Kind people,

Please find enclosed a patch that matches the PL/Perl documentation
(fairly closely, I hope) to the current PL/Perl implementation.

Cheers,
D
-- 
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!
Index: doc/src/sgml/plperl.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/plperl.sgml,v
retrieving revision 2.23
diff -u -r2.23 plperl.sgml
--- doc/src/sgml/plperl.sgml    16 May 2004 23:22:06 -0000      2.23
+++ doc/src/sgml/plperl.sgml    20 Jul 2004 03:04:05 -0000
@@ -34,9 +34,10 @@
   <note>
    <para>
     Users of source packages must specially enable the build of
-    PL/Perl during the installation process.  (Refer to the installation
-    instructions for more information.)  Users of binary packages
-    might find PL/Perl in a separate subpackage.
+    PL/Perl during the installation process.  (Refer to <xref
+    linkend="install-short"> for more information.)  Users of
+    binary packages might find PL/Perl in a separate subpackage.
+
    </para>
   </note>
 
@@ -54,7 +55,7 @@
    The body of the function is ordinary Perl code. Since the body of
    the function is treated as a string by
    <productname>PostgreSQL</productname>, it can be specified using
-   dollar quoting (as shown above), or via the usual single quote
+   dollar quoting (as shown above), or via the legacy single quote
    syntax (see <xref linkend="sql-syntax-strings"> for more
    information).
   </para>
@@ -79,19 +80,22 @@
   </para>
 
   <para>
-   If an SQL null value<indexterm><primary>null value</><secondary
-   sortas="PL/Perl">in PL/Perl</></indexterm> is passed to a function,
-   the argument value will appear as <quote>undefined</> in Perl.  The
-   above function definition will not behave very nicely with null
-   inputs (in fact, it will act as though they are zeroes).  We could
-   add <literal>STRICT</> to the function definition to make
-   <productname>PostgreSQL</productname> do something more reasonable:
-   if a null value is passed, the function will not be called at all,
-   but will just return a null result automatically.  Alternatively,
-   we could check for undefined inputs in the function body.  For
-   example, suppose that we wanted <function>perl_max</function> with
-   one null and one non-null argument to return the non-null argument,
-   rather than a null value:
+   If an SQL <literal>NULL</literal> value<indexterm><primary>null
+    value</><secondary sortas="PL/Perl">in PL/Perl</></indexterm> is
+    passed to a function, the argument value will appear as
+    <quote>undefined</> in Perl.  The above function definition will not
+    behave very nicely with <literal>NULL</literal> inputs (in fact, it
+    will act as though they are zeroes).  We could add <literal>STRICT</>
+    to the function definition to make
+    <productname>PostgreSQL</productname> do something more reasonable: if
+    a <literal>NULL</literal> value is passed, the function will not be
+    called at all, but will just return a <literal>NULL</literal> result
+    automatically.  Alternatively, we could check for undefined inputs in
+    the function body.  For example, suppose that we wanted
+    <function>perl_max</function> with one <literal>NULL</literal> and one
+    non-<literal>NULL</literal> argument to return the
+    non-<literal>NULL</literal> argument, rather than a
+    <literal>NULL</literal> value:
 
 <programlisting>
 CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS $$
@@ -108,9 +112,9 @@
   </para>
 
   <para>
-   As shown above, to return an SQL null value from a PL/Perl
-   function, return an undefined value.  This can be done whether the
-   function is strict or not.
+    As shown above, to return an SQL <literal>NULL</literal> value from
+    a PL/Perl function, return an undefined value.  This can be done
+    whether the function is strict or not.
   </para>
 
   <para>
@@ -127,7 +131,7 @@
 
 CREATE FUNCTION empcomp(employee) RETURNS integer AS $$
     my ($emp) = @_;
-    return $emp->{'basesalary'} + $emp->{'bonus'};
+    return $emp->{basesalary} + $emp->{bonus};
 $$ LANGUAGE plperl;
 
 SELECT name, empcomp(employee) FROM employee;
@@ -135,35 +139,9 @@
   </para>
 
   <para>
-   There is currently no support for returning a composite-type result
-   value.
+   There is now support for returning a composite-type result value.
   </para>
 
-  <tip>
-   <para>
-    Because the function body is passed as an SQL string literal to
-    <command>CREATE FUNCTION</command>, you have to use dollar quoting
-    or escape single quotes and backslashes within your Perl source,
-    typically by doubling them.  Another possible approach is to avoid
-    writing single quotes by using Perl's extended quoting operators
-    (<literal>q[]</literal>, <literal>qq[]</literal>,
-    <literal>qw[]</literal>).
-   </para>
-  </tip>
- </sect1>
-
- <sect1 id="plperl-data">
-  <title>Data Values in PL/Perl</title>
-
-  <para>
-   The argument values supplied to a PL/Perl function's code are
-   simply the input arguments converted to text form (just as if they
-   had been displayed by a <command>SELECT</command> statement).
-   Conversely, the <literal>return</> command will accept any string
-   that is acceptable input format for the function's declared return
-   type.  So, the PL/Perl programmer can manipulate data values as if
-   they were just text.
-  </para>
  </sect1>
 
  <sect1 id="plperl-database">
@@ -171,25 +149,77 @@
 
   <para>
    Access to the database itself from your Perl function can be done via
-   an experimental module <ulink
+   spi_exec_query, or via an experimental module <ulink
    
url="http://www.cpan.org/modules/by-module/DBD/APILOS/";><literal>DBD::PgSPI</literal></ulink>
    (also available at <ulink url="http://www.cpan.org/SITES.html";><acronym>CPAN</>
-   mirror sites</ulink>). This module makes available a
+   mirror sites</ulink>).  This module makes available a
    <acronym>DBI</>-compliant database-handle named
    <varname>$pg_dbh</varname> that can be used to perform queries
    with normal <acronym>DBI</> syntax.<indexterm><primary>DBI</></indexterm>
+
   </para>
 
   <para>
-   PL/Perl itself presently provides only one additional Perl command:
+   PL/Perl itself presently provides two additional Perl commands:
 
    <variablelist>
     <varlistentry>
      <indexterm>
+      <primary>spi_exec_query</primary>
+      <secondary>in PL/Perl</secondary>
+     </indexterm>
+     <indexterm>
       <primary>elog</primary>
       <secondary>in PL/Perl</secondary>
      </indexterm>
 
+     <term><function>spi_exec_query(</> [ <replaceable>SELECT query</replaceable> [, 
<replaceable>max_rows</replaceable>]] | [<replaceable>non-SELECT query</replaceable>] 
) </term>
+     <listitem>
+       <para>
+        Here is an example of a SELECT query with the optional maximum
+number of rows.
+<programlisting>
+$rv = spi_exec_query('SELECT * from my_table', 5);
+</programlisting>
+
+This returns up to 5 rows from my_table.
+       </para>
+       <para>
+If my_table has a column my_column, it would be accessed as
+<programlisting>
+$foo = $rv->{rows}[$i]->{my_column};
+</programlisting>
+       </para>
+       <para>
+The number of rows actually returned would be:
+<programlisting>
+$nrows = @{$rv->{rows}};
+</programlisting>
+       </para>
+       <para>
+Here is an example using a non-SELECT statement.
+<programlisting>
+$query = "INSERT INTO my_table VALUES (1, 'test')";
+$rv = spi_exec_query($query);
+</programlisting>
+
+You can then access status (SPI_OK_INSERT, e.g.) like this.
+<programlisting>
+$res = $rv->{status};
+</programlisting>
+
+       </para>
+       <para>
+To get the rows affected, do:
+<programlisting>
+$nrows = $rv->{rows};
+</programlisting>
+       </para>
+
+     </listitem>
+
+    </varlistentry>
+    <varlistentry>
      <term><function>elog</> <replaceable>level</replaceable>, 
<replaceable>msg</replaceable></term>
      <listitem>
       <para>
@@ -206,6 +236,111 @@
   </para>
  </sect1>
 
+ <sect1 id="plperl-data">
+  <title>Data Values in PL/Perl</title>
+
+  <para>
+   The argument values supplied to a PL/Perl function's code are
+   simply the input arguments converted to text form (just as if they
+   had been displayed by a <command>SELECT</command> statement).
+   Conversely, the <literal>return</> command will accept any string
+   that is acceptable input format for the function's declared return
+   type.  So, the PL/Perl programmer can manipulate data values as if
+   they were just text.
+  </para>
+
+  <para>
+   PL/Perl can now return rowsets and composite types, and rowsets of
+composite types.
+  </para>
+
+  <para>
+   Here is an example of a PL/Perl function returning a rowset of a row type:
+<programlisting>
+CREATE TABLE test (
+       i int,
+       v varchar
+);
+
+INSERT INTO test (i, v) VALUES (1,'first line');
+INSERT INTO test (i, v) VALUES (2,'second line');
+INSERT INTO test (i, v) VALUES (3,'third line');
+INSERT INTO test (i, v) VALUES (4,'immortal');
+
+create function test_munge() returns setof test language plperl as $$
+    my $res = [];
+    my $rv = spi_exec_query('select i,v from test;');
+    my $status = $rv->{status};
+    my $rows = @{$rv->{rows}};
+    my $processed = $rv->{processed};
+    foreach my $rn (0..$rows-1) {
+        my $row = $rv->{rows}[$rn];
+        $row->{i} += 200 if defined($row->{i});
+        $row->{v} =~ tr/A-Za-z/a-zA-Z/ if (defined($row->{v}));
+        push @$res,$row;
+    }
+    return $res;
+$$;
+
+select * from test_munge();
+</programlisting>
+  </para>
+
+  <para>
+   Here is an example of a PL/Perl function returning a composite type:
+  <programlisting>
+CREATE TYPE testrowperl AS (f1 integer, f2 text, f3 text);
+
+CREATE OR REPLACE FUNCTION perl_row() RETURNS testrowperl AS $$
+
+ return {f2 => 'hello', f1 => 1, f3 => 'world'};
+
+$$ LANGUAGE plperl;
+  </programlisting>
+  </para>
+
+  <para>
+  Here is an example of a PL/Perl function returning a rowset of a composite type.
+  <programlisting>
+CREATE TYPE testsetperl AS (f1 integer, f2 text, f3 text);
+
+CREATE OR REPLACE FUNCTION perl_set() RETURNS SETOF testsetperl AS $$
+ return[
+        {f1 => 1, f2 => 'hello', f3 =>  'world'},
+        {f1 => 2, f2 => 'hello', f3 =>  'postgres'},
+        {f1 => 3, f2 => 'hello', f3 =>  'plperl'}
+       ];
+$$  LANGUAGE plperl;
+  </programlisting>
+  </para>
+ </sect1>
+ <sect1 id="plperl-global">
+  <title>Global Values in PL/Perl</title>
+  <para>
+  You can use the %_SHARED to store data between function calls.  WHY
+IS THIS A HASH, AND NOT A HASH REF?
+  </para>
+  <para>
+For example:
+<programlisting>
+CREATE OR REPLACE FUNCTION set_var(TEXT) RETURNS TEXT AS $$
+    $_SHARED{first} = 'Hello, PL/Perl!';
+    return 'ok';
+$$ LANGUAGE plperl;
+
+CREATE OR REPLACE FUNCTION get_var() RETURNS text AS $$
+    return $_SHARED{first};
+$$ LANGUAGE plperl;
+
+SELECT set_var('hello plperl');
+SELECT get_var();
+</programlisting>
+
+  </para>
+   
+
+ </sect1>
+
  <sect1 id="plperl-trusted">
   <title>Trusted and Untrusted PL/Perl</title>
 
@@ -266,9 +401,69 @@
    <literal>plperlu</>, execution would succeed.
   </para>
  </sect1>
+ <sect1 id="plperl-triggers">
+  <title>PL/Perl Triggers</title>
+
+  <para>
+   PL/Perl can now be used to write trigger functions using the
+<varname>$_TD</varname> hash reference.
+  </para>
+
+  <para>
+   Some useful parts of the $_TD hash reference are:
+
+<programlisting>
+$_TD->{new}{foo} # NEW value of column foo
+$_TD->{old}{bar} # OLD value of column bar
+$_TD{name}       # Name of the trigger being called
+$_TD{event}      # INSERT, UPDATE, DELETE or UNKNOWN
+$_TD{when}       # BEFORE, AFTER or UNKNOWN
+$_TD{level}      # ROW, STATEMENT or UNKNOWN
+$_TD{relid}      # Relation ID of the table on which the trigger occurred.
+$_TD{relname}    # Name of the table on which the trigger occurred.
[EMAIL PROTECTED]    # Array of arguments to the trigger function.  May be empty.
+$_TD{argc}       # Number of arguments to the trigger.  Why is this here?
+</programlisting>
+
+  </para>
+
+  <para>
+   Triggers can return one of the following:
+<programlisting>
+return;   -- Executes the statement
+SKIP;     -- Doesn't execute the statement
+MODIFY; -- Says it modified a NEW row
+</programlisting>
+  </para>
+
+  <para>
+Here is an example of a trigger function, illustrating some of the
+above.
+<programlisting>
+CREATE TABLE test (
+        i int,
+        v varchar
+);
+
+CREATE OR REPLACE FUNCTION valid_id() RETURNS trigger AS $$
+    if (($_TD->{new}{i}>=100) || ($_TD->{new}{i}<=0)) {
+        return "SKIP";   # Skip INSERT/UPDATE command
+    } elsif ($_TD->{new}{v} ne "immortal") {
+        $_TD->{new}{v} .= "(modified by trigger)";
+        return "MODIFY"; # Modify tuple and proceed INSERT/UPDATE command
+    } else {
+        return;      # Proceed INSERT/UPDATE command
+    }
+$$ LANGUAGE plperl;
+
+CREATE TRIGGER "test_valid_id_trig" BEFORE INSERT OR UPDATE ON test
+FOR EACH ROW EXECUTE PROCEDURE "valid_id"();
+</programlisting>
+  </para>
+ </sect1>
 
  <sect1 id="plperl-missing">
-  <title>Missing Features</title>
+  <title>Limitations and Missing Features</title>
 
   <para>
    The following features are currently missing from PL/Perl, but they
@@ -278,26 +473,25 @@
     <listitem>
      <para>
       PL/Perl functions cannot call each other directly (because they
-      are anonymous subroutines inside Perl).  There's presently no
-      way for them to share global variables, either.
+      are anonymous subroutines inside Perl).
      </para>
     </listitem>
 
     <listitem>
      <para>
-      PL/Perl cannot be used to write trigger
-      functions.<indexterm><primary>trigger</><secondary>in
-      PL/Perl</></indexterm>
+      <application>Full SPI</application> is not yet implemented.
      </para>
     </listitem>
-
     <listitem>
-     <para>
-      <application>DBD::PgSPI</applicatioN> or similar capability
-      should be integrated into the standard
-      <productname>PostgreSQL</productname> distribution.
-     </para>
+      <para>
+        In the current implementation, if you are fetching or
+        returning very large datasets, you should be aware that these
+        will all go into memory.  Future features will help with this.
+        In the meantime, we suggest that you not use pl/perl if you
+        will fetch or return very large result sets.
+      </para>
     </listitem>
+
    </itemizedlist>
   </para>
  </sect1>
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Reply via email to