Chapter 7. PostgreSQL Support Functions

The following commands are available to use in PostgreSQL queries to aid in the use of PL/R functions:

plr_version()

Displays PL/R version as a text string.

install_rcmd (text R_code)

Install R code, given as a string, into the interpreter. See Chapter 5 for an example.

reload_plr_modules ()

Force re-loading of R code from the plr_modules table. It is useful after modifying the contents of plr_modules, so that the change will have an immediate effect.

plr_singleton_array (float8 first_element)

Creates a new PostgreSQL array, using element first_element. This function is predefined to accept one float8 value and return a float8 array. The C function that implements this PostgreSQL function is capable of accepting and returning other data types, although the return type must be an array of the input parameter type. It can also accept multiple input parameters. For example, to define a plr_array function to create a text array from two input text values:

CREATE OR REPLACE FUNCTION plr_array (text, text)
RETURNS text[]
AS '$libdir/plr','plr_array'
LANGUAGE 'C' WITH (isstrict);

select plr_array('hello','world');
   plr_array
---------------
 {hello,world}
(1 row)
     

plr_array_push (float8[] array, float8 next_element)

Pushes a new element onto the end of an existing PostgreSQL array. This function is predefined to accept one float8 array and a float8 value, and return a float8 array. The C function that implements this PostgreSQL function is capable of accepting and returning other data types. For example, to define a plr_array_push function to add a text value to an existing text array:

CREATE OR REPLACE FUNCTION plr_array_push (_text, text)
RETURNS text[]
AS '$libdir/plr','plr_array_push'
LANGUAGE 'C' WITH (isstrict);

select plr_array_push(plr_array('hello','world'), 'how are you');
       plr_array_push
-----------------------------
 {hello,world,"how are you"}
(1 row)
     

plr_array_accum (float8[] state_value, float8 next_element)

Creates a new array using next_element if state_value is NULL. Otherwise, pushes next_element onto the end of state_value. This function is predefined to accept one float8 array and a float8 value, and return a float8 array. The C function that implements this PostgreSQL function is capable of accepting and returning other data types. For example, to define a plr_array_accum function to add an int4 value to an existing int4 array:

CREATE OR REPLACE FUNCTION plr_array_accum (_int4, int4)
RETURNS int4[]
AS '$libdir/plr','plr_array_accum'
LANGUAGE 'C';

select plr_array_accum(NULL, 42);
 plr_array_accum
-------------
 {42}
(1 row)
select plr_array_accum('{23,35}', 42);
 plr_array_accum
-----------------
 {23,35,42}
(1 row)
        

This function may be useful for creating custom aggregates. See Chapter 8 for an example.

load_r_typenames()

Installs datatype Oid variables into the R interpreter as globals. See also r_typenames below.

r_typenames()

Displays the datatype Oid variables installed into the R interpreter as globals. See Chapter 6 for an example.

plr_environ()

Displays the environment under which the Postmaster is currently running. This may be useful to debug issues related to R specific environment variables. This function is installed with EXECUTE permission revoked from PUBLIC.

plr_set_display(text display)

Sets the DISPLAY environment vaiable under which the Postmaster is currently running. This may be useful if using R to plot to a virtual frame buffer. This function is installed with EXECUTE permission revoked from PUBLIC.

plr_get_raw(bytea serialized_object)

By default, when R objects are returned as type bytea, the R object is serialized using an internal R function prior to sending to PostgreSQL. This function unserializes the R object using another internal R function, and returns the pure raw bytes to PostgreSQL. This is useful, for example, if the R object being returned is a JPEG or PNG graphic for use outside of R.