PL/R User's Guide - R Procedural Language
Prev | Fast Backward |  | Fast Forward | Next

# Chapter 5. Using Global Data

Sometimes it is useful to have some global status data that is held between two calls to a procedure or is shared between different procedures. Equally useful is the ability to create functions that your PL/R functions can share. This is easily done since all PL/R procedures executed in one backend share the same R interpreter. So, any global R variable is accessible to all PL/R procedure calls, and will persist for the duration of the SQL client connection. An example of using a global object appears in the `pg.spi.execp` example, in Chapter 6.

A globally available, user named, R function (the R function name of PL/R functions is not the same as its PostgreSQL function name; see: Chapter 11) can be created dynamically using the provided PostgreSQL function `install_rcmd(text)`. Here is an example:

    select install_rcmd('pg.test.install <-function(msg) {print(msg)}');
     install_rcmd
    --------------
     OK
    (1 row)

    create or replace function pg_test_install(text) returns text as '
      pg.test.install(arg1)
    ' language 'plr';

    select pg_test_install('hello world');
     pg_test_install
    -----------------
     hello world
    (1 row)

A globally available, user named, R function can also be automatically created and installed in the R interpreter. See: Chapter 10

PL/R also provides a global variable called pg.state.firstpass. This variable is reset to TRUE the first time each PL/R function is called, for a particular query. On subsequent calls the value is left unchanged. This allows one or more PL/R functions to perform a possibly expensive initialization on the first call, and reuse the results for the remaining rows in the query. For example:

    create table t (f1 int);
    insert into t values (1);
    insert into t values (2);
    insert into t values (3);

    create or replace function f1() returns int as '
      msg <- paste("enter f1, pg.state.firstpass is", pg.state.firstpass)
      pg.thrownotice(msg)
      if (pg.state.firstpass == TRUE)
        pg.state.firstpass <<- FALSE
      msg <- paste("exit f1, pg.state.firstpass is", pg.state.firstpass)
      pg.thrownotice(msg)
      return(0)
    ' language plr;

    create or replace function f2() returns int as '
      msg <- paste("enter f2, pg.state.firstpass is", pg.state.firstpass)
      pg.thrownotice(msg)
      if (pg.state.firstpass == TRUE)
        pg.state.firstpass <<- FALSE
      msg <- paste("exit f2, pg.state.firstpass is", pg.state.firstpass)
      pg.thrownotice(msg)
      return(0)
    ' language plr;

    select f1(), f2(), f1 from t;
    NOTICE:  enter f1, pg.state.firstpass is TRUE
    NOTICE:  exit f1, pg.state.firstpass is FALSE
    NOTICE:  enter f2, pg.state.firstpass is TRUE
    NOTICE:  exit f2, pg.state.firstpass is FALSE
    NOTICE:  enter f1, pg.state.firstpass is FALSE
    NOTICE:  exit f1, pg.state.firstpass is FALSE
    NOTICE:  enter f2, pg.state.firstpass is FALSE
    NOTICE:  exit f2, pg.state.firstpass is FALSE
    NOTICE:  enter f1, pg.state.firstpass is FALSE
    NOTICE:  exit f1, pg.state.firstpass is FALSE
    NOTICE:  enter f2, pg.state.firstpass is FALSE
    NOTICE:  exit f2, pg.state.firstpass is FALSE
     f1 | f2 | f1
    ----+----+----
      0 |  0 |  1
      0 |  0 |  2
      0 |  0 |  3
    (3 rows)

    create or replace function row_number() returns int as '
      if (pg.state.firstpass)
      {
        assign("pg.state.firstpass", FALSE, env=.GlobalEnv)
        lclcntr <- 1
      }
      else
        lclcntr <- plrcounter + 1
      assign("plrcounter", lclcntr, env=.GlobalEnv)
      return(lclcntr)
    ' language 'plr';

    SELECT row_number(), f1 from t;
     row_number | f1
    ------------+----
              1 |  1
              2 |  2
              3 |  3
    (3 rows)

Prev | Home | Next
Passing Data Values |  | Database Access and Support Functions