6.1. Normal Support

Execute an SQL query given as a string. An error in the query causes an error to be raised. Otherwise, the command's return value is the number of rows processed for INSERT, UPDATE, or DELETE statements, or zero if the query is a utility statement. If the query is a SELECT statement, the values of the selected columns are placed in an R data.frame with the target column names used as the frame column names. However, non-numeric columns are not converted to factors. If you want all non-numeric columns converted to factors, a convenience function pg.spi.factor (described below) is provided.

If a field of a SELECT result is NULL, the target variable for it is set to "NA". For example:

create or replace function test_spi_tup(text) returns setof record as '
  pg.spi.exec(arg1)
' language 'plr';

select * from test_spi_tup('select oid, NULL::text as nullcol,
  typname from pg_type where typname = ''oid'' or typname = ''text''')
  as t(typeid oid, nullcol text, typename name);
 typeid | nullcol | typename
--------+---------+----------
     25 |         | text
     26 |         | oid
(2 rows)
        

The NULL values were passed to R as "NA", and on return to PostgreSQL they were converted back to NULL.

Prepares and saves a query plan for later execution. The saved plan will be retained for the life of the current backend.

The query may use arguments, which are placeholders for values to be supplied whenever the plan is actually executed. In the query string, refer to arguments by the symbols $1 ... $n. If the query uses arguments, the values of the argument types must be given as a vector. Pass NA for type_vector if the query has no arguments. The argument types must be identified by the type Oids, shown in pg_type. Global variables are provided for this use. They are named according to the convention TYPENAMEOID, where the actual name of the type, in all capitals, is substituted for TYPENAME. A support function, load_r_typenames() must be used to make the predefined global variables available for use:

select load_r_typenames();
 load_r_typenames
------------------
 OK
(1 row)
        

Another support function, r_typenames() may be used to list the predefined Global variables:

select * from r_typenames();
    typename     | typeoid
-----------------+---------
 ABSTIMEOID      |     702
 ACLITEMOID      |    1033
 ANYARRAYOID     |    2277
 ANYOID          |    2276
 BITOID          |    1560
 BOOLOID         |      16
  [...]
 TRIGGEROID      |    2279
 UNKNOWNOID      |     705
 VARBITOID       |    1562
 VARCHAROID      |    1043
 VOIDOID         |    2278
 XIDOID          |      28
(59 rows)
        

The return value from pg.spi.prepare is a query ID to be used in subsequent calls to pg.spi.execp. See spi_execp for an example.

Execute a query previously prepared with pg.spi.prepare . saved_plan is the external pointer returned by pg.spi.prepare. If the query references arguments, a value_list must be supplied: this is an R list of actual values for the plan arguments. It must be the same length as the argument type_vector previously given to pg.spi.prepare. Pass NA for value_list if the query has no arguments. The following illustrates the use of pg.spi.prepare and pg.spi.execp with and without query arguments:

create or replace function test_spi_prep(text) returns text as '
  sp <<- pg.spi.prepare(arg1, c(NAMEOID, NAMEOID));
  print("OK")
' language 'plr';

select test_spi_prep('select oid, typname from pg_type 
  where typname = $1 or typname = $2');
 test_spi_prep 
---------------
 OK
(1 row)

create or replace function test_spi_execp(text, text, text) returns setof record as '
  pg.spi.execp(pg.reval(arg1), list(arg2,arg3))
' language 'plr';

select * from test_spi_execp('sp','oid','text') as t(typeid oid, typename name);
 typeid | typename 
--------+----------
     25 | text
     26 | oid
(2 rows)

create or replace function test_spi_prep(text) returns text as '
  sp <<- pg.spi.prepare(arg1, NA);
  print("OK")
' language 'plr';

select test_spi_prep('select oid, typname from pg_type
  where typname = ''bytea'' or typname = ''text''');
 test_spi_prep
---------------
 OK
(1 row)

create or replace function test_spi_execp(text) returns setof record as '
  pg.spi.execp(pg.reval(arg1), NA)
' language 'plr';

select * from test_spi_execp('sp') as t(typeid oid, typename name);
 typeid | typename
--------+----------
     17 | bytea
     25 | text
(2 rows)

create or replace function test_spi_prep(text) returns text as '
  sp <<- pg.spi.prepare(arg1);
  print("OK")
' language 'plr';

select test_spi_prep('select oid, typname from pg_type
  where typname = ''bytea'' or typname = ''text''');
 test_spi_prep
---------------
 OK
(1 row)

create or replace function test_spi_execp(text) returns setof record as '
  pg.spi.execp(pg.reval(arg1))
' language 'plr';

select * from test_spi_execp('sp') as t(typeid oid, typename name);
 typeid | typename
--------+----------
     17 | bytea
     25 | text
(2 rows)
        

NULL arguments should be passed as individual NA values in value_list.

Except for the way in which the query and its arguments are specified, pg.spi.execp works just like pg.spi.exec.

Opens a cursor identified by cursor_name. The cursor can then be used to scroll through the results of a query plan previously prepared by pg.spi.prepare. Any arguments to the plan should be specified in argvalues similar to pg.spi.execp. Only read-only cursors are supported at the moment.

plan <- pg.spi.prepare('SELECT * FROM pg_class');
cursor_obj <- pg.spi.cursor_open('my_cursor',plan);
        

Returns a cursor object that be be passed to pg.spi.cursor_fetch

Fetches rows from the cursor object previosuly returned by pg.spi.cursor_open . If forward is TRUE then the cursor is moved forward to fetch at most the number of rows required by the rows parameter. If forward is FALSE then the cursor is moved backrwards at most the number of rows specified.

rows indicates the maximum number of rows that should be returned.

plan <- pg.spi.prepare('SELECT * FROM pg_class');
cursor_obj <- pg.spi.cursor_open('my_cursor',plan);
data <- pg.spi.cursor_fetch(cursor_obj,TRUE,as.integer(10));
        

Returns a data frame containing the results.

Closes a cursor previously opened by pg.spi.cursor_open

plan <- pg.spi.prepare('SELECT * FROM pg_class');
cursor_obj <- pg.spi.cursor_open('my_cursor',plan);
pg.spi.cursor_close(cursor_obj);
        

Returns the OID of the row inserted by the last query executed via pg.spi.exec or pg.spi.execp, if that query was a single-row INSERT. (If not, you get zero.)

Duplicates all occurrences of single quote and backslash characters in the given string. This may be used to safely quote strings that are to be inserted into SQL queries given to pg.spi.exec or pg.spi.prepare.

Return the given string suitably quoted to be used as an identifier in an SQL query string. Quotes are added only if necessary (i.e., if the string contains non-identifier characters or would be case-folded). Embedded quotes are properly doubled. This may be used to safely quote strings that are to be inserted into SQL queries given to pg.spi.exec or pg.spi.prepare.

Emit a PostgreSQL NOTICE or ERROR message. ERROR also raises an error condition: further execution of the function is abandoned, and the current transaction is aborted.

Accepts an R data.frame as input, and converts all non-numeric columns to factors. This may be useful for data.frames produced by pg.spi.exec or pg.spi.prepare, because the PL/R conversion mechanism does not do that for you.