PL/R User's Guide - R Procedural Language
Prev | Fast Backward | Chapter 6. Database Access and Support Functions | Fast Forward | Next

# 6.1. Normal Support

`pg.spi.exec` (`character` `query`)

  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.

`pg.spi.prepare` (`character` `query`, `integer vector` `type_vector`)

  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.

`pg.spi.execp` (`external pointer` `saved_plan`, `variable list` `value_list`)

  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`.

`pg.spi.cursor_open`( `character` `cursor_name`, `external pointer` `saved_plan`, `variable list` `value_list`)

  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`

`pg.spi.cursor_fetch`( `external pointer` `cursor`, `boolean` `forward`, `integer` `rows`)

  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.

`pg.spi.cursor_close`( `external pointer` `cursor`)

  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);

`pg.spi.lastoid`()

  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.)

`pg.quoteliteral` (`character` `SQL_string`)

  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`.

`pg.quoteident` (`character` `SQL_string`)

  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`.

`pg.thrownotice` (`character` `message`)
`pg.throwerror` (`character` `message`)

  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.

`pg.spi.factor` (`data.frame` `data`)

  Accepts an R `data.frame` as input, and converts all non-numeric columns to `factor`s. 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.

Prev | Home | Next
Database Access and Support Functions | Up | RPostgreSQL Compatibility Support