plr
Overview
| Package | Version | Category | License | Language |
|---|---|---|---|---|
plr | 8.4.8 | LANG | GPL-2.0 | C |
| ID | Extension | Bin | Lib | Load | Create | Trust | Reloc | Schema |
|---|---|---|---|---|---|---|---|---|
| 3100 | plr | No | Yes | No | Yes | No | No | - |
| Related | plpgsql pgml plpython3u pg_tle plv8 pljava plperl pllua |
|---|
missing el10.x86_64
Version
| Type | Repo | Version | PG Ver | Package | Deps |
|---|---|---|---|---|---|
| EXT | PGDG | 8.4.8 | 1817161514 | plr | - |
| RPM | PGDG | 8.4.8 | 1817161514 | plr_$v | - |
| DEB | PGDG | 8.4.8 | 1817161514 | postgresql-$v-plr | - |
Install
You can install plr directly. First, make sure the PGDG repository is added and enabled:
pig repo add pgdg -u # Add PGDG repo and update cache
Install the extension using pig or apt/yum/dnf:
pig install plr; # Install for current active PG version
pig ext install -y plr -v 18 # PG 18
pig ext install -y plr -v 17 # PG 17
pig ext install -y plr -v 16 # PG 16
pig ext install -y plr -v 15 # PG 15
pig ext install -y plr -v 14 # PG 14
dnf install -y plr_18 # PG 18
dnf install -y plr_17 # PG 17
dnf install -y plr_16 # PG 16
dnf install -y plr_15 # PG 15
dnf install -y plr_14 # PG 14
apt install -y postgresql-18-plr # PG 18
apt install -y postgresql-17-plr # PG 17
apt install -y postgresql-16-plr # PG 16
apt install -y postgresql-15-plr # PG 15
apt install -y postgresql-14-plr # PG 14
Create Extension:
CREATE EXTENSION plr;
Usage
plr: load R interpreter and execute R script from within a database
plr enables writing PostgreSQL functions in the R programming language, providing full access to R’s statistical and data analysis capabilities.
CREATE EXTENSION plr;
Create Functions
CREATE OR REPLACE FUNCTION r_max(integer, integer) RETURNS integer AS '
if (arg1 > arg2)
return(arg1)
else
return(arg2)
' LANGUAGE plr STRICT;
SELECT r_max(10, 20); -- 20
With named arguments:
CREATE OR REPLACE FUNCTION sd(vals float8[]) RETURNS float AS '
sd(vals)
' LANGUAGE plr STRICT;
SELECT sd(ARRAY[1.0, 2.0, 3.0, 4.0, 5.0]);
Argument Handling
- Arguments are available as
arg1,arg2, … or by named parameter - NULL arguments become R
NAvalues (unless function isSTRICT) - Composite types (rows) are passed as R data.frames
- Arrays are passed as R vectors
CREATE OR REPLACE FUNCTION r_max(integer, integer) RETURNS integer AS '
if (is.null(arg1) && is.null(arg2))
return(NULL)
if (is.null(arg1))
return(arg2)
if (is.null(arg2))
return(arg1)
if (arg1 > arg2)
return(arg1)
return(arg2)
' LANGUAGE plr;
Database Access via SPI
CREATE OR REPLACE FUNCTION test_spi(text) RETURNS SETOF record AS '
pg.spi.exec(arg1)
' LANGUAGE plr;
SELECT * FROM test_spi('SELECT oid, typname FROM pg_type LIMIT 5')
AS t(oid oid, typname name);
Prepared statements:
-- Prepare
sp <<- pg.spi.prepare('SELECT * FROM pg_type WHERE typname = $1', c(NAMEOID))
-- Execute
pg.spi.execp(sp, list('text'))
Set-Returning Functions
Return a data.frame for set-returning functions:
CREATE OR REPLACE FUNCTION get_numbers(n int) RETURNS SETOF integer AS '
1:arg1
' LANGUAGE plr;
SELECT * FROM get_numbers(5);
Window Functions
CREATE OR REPLACE FUNCTION r_regr_slope(float8, float8, int)
RETURNS float8 AS '
slope <- NA
y <- farg1
x <- farg2
if (fnumrows == arg3 + 1L)
try(slope <- lm(y ~ x)$coefficients[2])
return(slope)
' LANGUAGE plr WINDOW;
Window functions receive farg1..fargN (vectors of values in the window frame), fnumrows (frame size), and prownum (current row position in partition).
Global Variables
Persist data across function calls using R’s global environment:
CREATE OR REPLACE FUNCTION set_state(key text, val text) RETURNS void AS '
assign(arg1, arg2, env=.GlobalEnv)
' LANGUAGE plr;
Useful Support Functions
SELECT load_r_typenames(); -- Load type OID variables
SELECT * FROM r_typenames(); -- List available type OIDs
SELECT plr_version(); -- PL/R version
Trigger Functions
PL/R supports trigger functions with access to pg.tg.name, pg.tg.relname, pg.tg.when, pg.tg.level, pg.tg.op, pg.tg.new, and pg.tg.old.
Feedback
Was this page helpful?
Thanks for the feedback! Please let us know how we can improve.
Sorry to hear that. Please let us know how we can improve.