I’m pleased to introduce you to a new available package for postgreSQL database.
The R procedural language extension developed by Joseph E Conway.
R Procedural Language for PostgreSQL
Introduction
PL/R is a library which allow you to add the power of R statistical engine in your database.
This article will show you how to add it and basic usage on a 12.1 installation.
(The package is also available for 11.4)
Become familiar with the project and how it can help you.
The homepage project : www.joeconway.com/plr/
We admit here, that you are able to manage a posgreSQL server instance, and have already one running.
Installation
Actually the pre-packaged rpms are available at
Repository for openSUSE 12.1:
http://software.opensuse.org/repositories/server:database:postgresql/openSUSE_12.1
Repository for openSUSE 11.4:
http://software.opensuse.org/repositories/server:database:postgresql/openSUSE_11.4
Packages are composed of postgresql-plr (containing only the necessary lib) and postgresql-plr-doc (the whole documentation and samples)
Disclaimer/Warning
This repository is the devel (but stable) repository for postgresql, using it can ask you to upgrade your postgresql installation to new version.
Take care to be sure, you can safely upgrade it (backups before), other software you need that are linked to previous version etc…
Submission to factory/12.2 is pending …
Adding the repository
zypper ar -f -c -n "server-database-postgresql" http://software.opensuse.org/repositories/server:database:postgresql/openSUSE_12.1 "server-database-postgresql"
If postgresql is already installed
Check upgrade process
zypper -v dup -D -r server-database-postgresql
Do the upgrade
zypper -v dup -r server-database-postgresql
Add postgresql-plr to the installation
zypper in postgresql-plr postgresql-plr-doc
Create a PLR ready database
As postgresql superuser
createdb plrtest
Create the plr extension
psql -d plrtest
CREATE EXTENSION plr;
Quick test if it works
-- first, define the language and functions. Turn off echoing so that expected file -- does not depend on contents of plr.sql. -- \set ECHO none -- check version SELECT plr_version(); plr_version ------------- 08.03.00.13 (1 row)
Work with
If you install the package postresql-plr-doc you will have a file named plr.out which contain a lot of usefull examples.
Connect to your plr ready db
psql -d plrtest
And run some of the example like
--
-- test aggregates
--
create table foo(f0 int, f1 text, f2 float8) with oids;
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);
create or replace function r_median(_float8) returns float as 'median(arg1)' language 'plr';
select r_median('{1.23,1.31,1.42,1.27}'::_float8);
r_median
----------
1.29
(1 row)
CREATE AGGREGATE median (sfunc = plr_array_accum, basetype = float8, stype = _float8, finalfunc = r_median);
select f1, median(f2) from foo group by f1 order by f1;
f1 | median
------+--------
cat1 | 1.21
cat2 | 1.28
(2 rows)
create or replace function r_gamma(_float8) returns float as 'gamma(arg1)' language 'plr';
select round(r_gamma('{1.23,1.31,1.42,1.27}'::_float8)::numeric,8);
round
------------
0.91075486
(1 row)
CREATE AGGREGATE gamma (sfunc = plr_array_accum, basetype = float8, stype = _float8, finalfunc = r_gamma);
select f1, round(gamma(f2)::numeric,8) from foo group by f1 order by f1;
f1 | round
------+------------
cat1 | 0.91557649
cat2 | 0.93304093
(2 rows)
\q
Play more
Have a look and build your own benchmark
- http://www.postgresonline.com/journal/archives/188-plr_part1.html
- http://www.postgresonline.com/journal/archives/189-plr_part2.html
- http://www.postgresonline.com/journal/archives/190-plr_part3.html
- http://www.postgresonline.com/journal/archives/67-Build-Median-Aggregate-Function-in-SQL.html
Failures, bugs
If you find a defect in the package, please use traditional bug reporting at
bugzilla.novell.com
This link contain the already pre-filled form and should help you to get quick answer.
Both comments and pings are currently closed.
