If you are running R version 2.12 or above on Windows, the R bin folder has changed. Instead of
bin it's
bin\i386 or
bin\x64. Also if you install the newer version, you'll need to use the binaries and manually register the paths and R_HOME yourself since the installer will not install. You can still use the plr.dll etc. See our other
Quick Intro to PL/R for more details and examples.
Edit Path system variable and add the R bin folder to the end of it. Do not remove existing ones, just add this to the end 
Restart your PostgreSQL service from control panel -> Services. On rare circumstances, you may need to restart the computer for changes to take effect.
Loading PL/R functionality into a database
In order to start using PL/R in a database, you need to load the help functions in the database. To do so do the following.
- Using PgAdmin III - select the database you want to enable with PL/R and then click the SQL icon to get to the query window.
For users running PostgreSQL 9.1+, install by typing in SQL window:
CREATE EXTENSION plr;
If you are running on PostgreSLQ 9.0 or lower you have to install using the plr.sql file. Choose -> File -> Open ->
path/to/PostgreSQL/8.4/contrib/plr.sql (NOTE: on Windows the default
location is C:\Program Files\PostgreSQL\8.4\contrib\plr.sql
- Click the Green arrow to execute
Testing out PL/R
Next run the following commands from PgAdminIII or psql to test out R
SELECT * FROM plr_environ();
SELECT load_r_typenames();
SELECT * FROM r_typenames();
SELECT plr_array_accum('{23,35}', 42);
Next try to create a helper function (this was copied from (http://www.joeconway.com/plr/doc/plr-pgsql-support-funcs.html) - and test with the following
CREATE OR REPLACE FUNCTION plr_array (text, text)
RETURNS text[]
AS '$libdir/plr','plr_array'
LANGUAGE 'C' WITH (isstrict);
select plr_array('hello','world');
Using R In PostgreSQL
Creating Median Function in PostgreSQL using R
Below is a link creating a median aggregate function. This basically
creates a stub aggregate function that calls the median function in R.
http://www.joeconway.com/plr/doc/plr-aggregate-funcs.html
NOTE: I ran into a problem here installing median from the plr-aggregate-funcs via PgAdmin. Gave R-Parse error when trying to use the function. I had to install median function by removing
all the carriage returns (\r\n) so put the whole median function body in single line like below to be safe. Evidentally when copying from IE - IE puts in carriage returns instead of unix line breaks. When creating PL/R functions make sure to use Unix line breaks instead of windows carriage returns by using an editor such as Notepad++ that will allow you to specify unix line breaks.
create or replace function r_median(_float8)
returns float as 'median(arg1)' language 'plr';
CREATE AGGREGATE median (
sfunc = plr_array_accum,
basetype = float8,
stype = _float8,
finalfunc = r_median
);
create table foo(f0 int, f1 text, f2 float8);
insert into foo values(1,'cat1',1.21);
insert into foo values(2,'cat1',1.24);
insert into foo values(3,'cat1',1.18);
insert into foo values(4,'cat1',1.26);
insert into foo values(5,'cat1',1.15);
insert into foo values(6,'cat2',1.15);
insert into foo values(7,'cat2',1.26);
insert into foo values(8,'cat2',1.32);
insert into foo values(9,'cat2',1.30);
select f1, median(f2) from foo group by f1 order by f1;
In the next part of this series, we will cover using PL/R in conjunction with PostGIS.
Post Comments About PLR Part 1: Up and Running with PL/R (PLR) in PostgreSQL: An almost Idiot's Guide
