/usr/share/doc/postgresql-9.5-plr/plr-global-data.html

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN""http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>Using Global Data</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PL/R User's Guide - R Procedural Language"
HREF="index.html"><LINK
REL="PREVIOUS"
TITLE="Passing Data Values"
HREF="plr-data.html"><LINK
REL="NEXT"
TITLE="Database Access and Support Functions"
HREF="plr-spi-rsupport-funcs.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2015-02-13T14:44:26"></HEAD
><BODY
CLASS="CHAPTER"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="4"
ALIGN="center"
VALIGN="bottom"
><A
HREF="index.html"
>PL/R User's Guide - R Procedural Language</A
></TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
TITLE="Passing Data Values"
HREF="plr-data.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
></TD
><TD
WIDTH="20%"
ALIGN="right"
VALIGN="top"
><A
TITLE="Database Access and Support Functions"
HREF="plr-spi-rsupport-funcs.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="CHAPTER"
><H1
><A
NAME="PLR-GLOBAL-DATA"
></A
>Chapter 5. Using Global Data</H1
><P
>    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
    <CODE
CLASS="FUNCTION"
>pg.spi.execp</CODE
> example, in
    <A
HREF="plr-spi-rsupport-funcs.html"
>Chapter 6</A
>.
   </P
><P
>    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: 
    <A
HREF="plr-func-naming.html"
>Chapter 11</A
>) can be created dynamically
    using the provided PostgreSQL function 
    <CODE
CLASS="FUNCTION"
>install_rcmd(text)</CODE
>. Here is an example:
    </P><PRE
CLASS="PROGRAMLISTING"
>select install_rcmd('pg.test.install &#60;-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)
    </PRE
><P>
   </P
><P
>    A globally available, user named, R function can also be automatically
    created and installed in the R interpreter. See: 
    <A
HREF="plr-module-funcs.html"
>Chapter 10</A
>
   </P
><P
>    PL/R also provides a global variable called pg.state.firstpass. This
    variable is reset to TRUE the first time <SPAN
CLASS="emphasis"
><I
CLASS="EMPHASIS"
>each</I
></SPAN
> 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:
    </P><PRE
CLASS="PROGRAMLISTING"
>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 &#60;- paste("enter f1, pg.state.firstpass is", pg.state.firstpass)
  pg.thrownotice(msg)
  if (pg.state.firstpass == TRUE)
    pg.state.firstpass &lt;&lt;- FALSE
  msg &lt;- 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 &lt;- paste("enter f2, pg.state.firstpass is", pg.state.firstpass)
  pg.thrownotice(msg)
  if (pg.state.firstpass == TRUE)
    pg.state.firstpass &lt;&lt;- FALSE
  msg &lt;- 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 &lt;- 1
  }
  else
    lclcntr &lt;- 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)
    </PRE
><P>
   </P
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="plr-data.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="plr-spi-rsupport-funcs.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>Passing Data Values</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
>&nbsp;</TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>Database Access and Support Functions</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>
postgresql-9.5-plr 1:8.3.0.16-1 / usr / share / doc / postgresql-9.5-plr / plr-global-data.html
