Working with R from postgres in Ubuntu

In order to install R and make it work from postgres 8.4 in ubuntu you should do as follows:

* install postgresql-server
sudo aptitude install postgresql postgresql-server-dev-8.4 postgis
sudo -i
su - postgres
psql
# alter user postgres with password 'some password, it will be asked later';
CTRL-D

* install R
sudo aptitude install r-base r-base-dev
sudo echo "/usr/lib/R/lib/" > /etc/ld.so.conf.d/libR.conf
sudo ldconfig

* install pl/R
(Follow http://www.joeconway.com/plr/doc/plr-install.html ) or:

wget http://www.joeconway.com/plr/plr-8.3.0.10.tar.gz

tar xvfz plr-8.3.0.10.tar.gz
cd plr
USE_PGXS=1 make
sudo USE_PGXS=1 make install

* restart postgres
sudo /etc/init.d/postgres-8.4 restart

* create a database

psql -U postgres -h localhost -W
# CREATE DATABASE plrtest
# CREATE TABLE tst (a float);
# insert into tst values (1.2342);
# insert into tst values (2.1231);
CTRL-D
* load plr into the database
psql -U postgres -h localhost -W plrtest < /usr/share/postgresql/8.4/contrib/plr.sql

psql -U postgres -h localhost -W plrtest
# \c tst
# create or replace function r_pnorm( x float ) returns float as '
return ( pnorm(x) );
' LANGUAGE 'plr';

* test the function
# select r_pnorm( a ) from tst;