Subject: Doc patch: New PL/Perl Features

From: David Fetter <[email-masked]>
Date: Tue, 12 Jul 2005 12:21:29 -0700

Folks,

Please find enclosed document patches for PL/Perl features recently
introduced in CVS TIP.  These include:

return_next
returning PostgreSQL arrays
spi_query/spi_fetchrow
use strict

Cheers,
D
-- 
David Fetter [email-masked] 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/doc/src/sgml/plperl.sgml,v
retrieving revision 2.41
diff -c -r2.41 plperl.sgml
*** doc/src/sgml/plperl.sgml	5 Jun 2005 03:16:29 -0000	2.41
--- doc/src/sgml/plperl.sgml	12 Jul 2005 19:18:05 -0000
***************
*** 54,59 ****
--- 54,86 ----
  </programlisting>
     The body of the function is ordinary Perl code.
    </para>
+     <para>
+     As with ordinary Perl code, you should use the strict pragma,
+     which you can do one of two ways:
+ 
+     <itemizedlist>
+     <listitem>
+         <para>
+         Globally, by turning on plperl (one of the <xref
+         linkend="guc-custom-variable-classes"
+         endterm="custom_variable_classes"> you can use) and setting
+         plperl.strict to true in your postgresql.conf, or
+         </para>
+     </listitem>
+     <listitem>
+         <para>
+         One function at a time, by using PL/PerlU (you must be
+         database superuser to do this) and issuing a
+ 
+ <programlisting>
+ use strict;
+ </programlisting>
+ 
+         in the code.
+         </para>
+     </listitem>
+     </itemizedlist>
+     </para>
  
     <para>
      The syntax of the <command>CREATE FUNCTION</command> command requires
***************
*** 118,123 ****
--- 145,165 ----
    </para>
  
    <para>
+    Perl can return PostgreSQL arrays as references to Perl arrays.
+     Here is an example:
+ <programlisting>
+ CREATE OR REPLACE function returns_array()
+ RETURNS text[][]
+ LANGUAGE plperl
+ AS $$
+     return [['a"b','c,d'],['e\\f','g']];
+ $$;
+ 
+ select returns_array();
+ </programlisting>
+   </para>
+ 
+   <para>
     Composite-type arguments are passed to the function as references
     to hashes.  The keys of the hash are the attribute names of the
     composite type.  Here is an example:
***************
*** 158,171 ****
    </para>
  
    <para>
!    PL/Perl functions can also return sets of either scalar or composite
!    types.  To do this, return a reference to an array that contains
!    either scalars or references to hashes, respectively.  Here are
!    some simple examples:
  
  <programlisting>
  CREATE OR REPLACE FUNCTION perl_set_int(int) RETURNS SETOF INTEGER AS $$
! return [0..$_[0]];
  $$ LANGUAGE plperl;
  
  SELECT * FROM perl_set_int(5);
--- 200,240 ----
    </para>
  
    <para>
!     PL/Perl functions can also return sets of either scalar or
!     composite types.  In general, you'll want to return rows one at a
!     time both to speed up startup time and to keep from queueing up
!     the entire result set in memory.  You can do this with
!     <function>return_next</function> as illustrated below:
! <programlisting>
! CREATE OR REPLACE FUNCTION perl_set_int(int)
! RETURNS SETOF INTEGER
! LANGUAGE plperl AS $$
!     foreach (0..$_[0]) {
!         return_next($_);
!     }
!     return;
! $$;
! 
! SELECT * FROM perl_set_int(5);
! 
! CREATE OR REPLACE FUNCTION perl_set()
! RETURNS SETOF testrowperl
! LANGUAGE plperl AS $$
!     return_next({f1 => 1, f2 => 'Hello', f3 => 'World' });
!     return_next({ f1 => 2, f2 => 'Hello', f3 => 'PostgreSQL' });
!     return_next({ f1 => 3, f2 => 'Hello', f3 => 'PL/Perl' });
!     return;
! $$;
! </programlisting>
! 
!     For small result sets, you can return a reference to
!     an array that contains either scalars or references to hashes,
!     respectively.  Here are some simple examples of returning the
!     entire result set as a reference:
  
  <programlisting>
  CREATE OR REPLACE FUNCTION perl_set_int(int) RETURNS SETOF INTEGER AS $$
!     return [0..$_[0]];
  $$ LANGUAGE plperl;
  
  SELECT * FROM perl_set_int(5);
***************
*** 182,192 ****
  SELECT * FROM perl_set();
  </programlisting>
  
-    When you do this, Perl will have to build the entire array in memory;
-    therefore the technique does not scale to very large result sets. You
-    can instead call <function>return_next</function> for each element of
-    the result set, passing it either a scalar or a reference to a hash,
-    as appropriate to your function's return type.
    </para>
  
      <para>
--- 251,256 ----
***************
*** 217,223 ****
    </para>
  
    <para>
!    PL/Perl itself presently provides two additional Perl commands:
  
     <variablelist>
      <varlistentry>
--- 281,287 ----
    </para>
  
    <para>
!    PL/Perl itself presently provides four additional Perl commands:
  
     <variablelist>
      <varlistentry>
***************
*** 228,233 ****
--- 292,301 ----
  
       <term><literal><function>spi_exec_query</>(<replaceable>query</replaceable> [, <replaceable>max-rows</replaceable>])</literal></term>
       <term><literal><function>spi_exec_query</>(<replaceable>command</replaceable>)</literal></term>
+      <term><literal><function>spi_query</>(<replaceable>query</replaceable>)</literal></term>
+      <term><literal><function>spi_fetchrow</>(<replaceable>result of spi_query</replaceable>)</literal></term>
+      <term><literal>
+      </literal></term>
       <listitem>
        <para>
         Executes an SQL command.  Here is an example of a query
***************
*** 280,287 ****
  INSERT INTO test (i, v) VALUES (3, 'third line');
  INSERT INTO test (i, v) VALUES (4, 'immortal');
  
! CREATE FUNCTION test_munge() RETURNS SETOF test AS $$
!     my $res = [];
      my $rv = spi_exec_query('select i, v from test;');
      my $status = $rv-&gt;{status};
      my $nrows = $rv-&gt;{processed};
--- 348,356 ----
  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 $rv = spi_exec_query('select i, v from test;');
      my $status = $rv-&gt;{status};
      my $nrows = $rv-&gt;{processed};
***************
*** 289,298 ****
          my $row = $rv-&gt;{rows}[$rn];
          $row-&gt;{i} += 200 if defined($row-&gt;{i});
          $row-&gt;{v} =~ tr/A-Za-z/a-zA-Z/ if (defined($row-&gt;{v}));
!         push @$res, $row;
      }
!     return $res;
! $$ LANGUAGE plperl;
  
  SELECT * FROM test_munge();
  </programlisting>
--- 358,367 ----
          my $row = $rv-&gt;{rows}[$rn];
          $row-&gt;{i} += 200 if defined($row-&gt;{i});
          $row-&gt;{v} =~ tr/A-Za-z/a-zA-Z/ if (defined($row-&gt;{v}));
!         return_next($row);
      }
!     return;
! $$;
  
  SELECT * FROM test_munge();
  </programlisting>
Index: doc/src/sgml/runtime.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/runtime.sgml,v
retrieving revision 1.337
diff -c -r1.337 runtime.sgml
*** doc/src/sgml/runtime.sgml	6 Jul 2005 14:45:12 -0000	1.337
--- doc/src/sgml/runtime.sgml	12 Jul 2005 19:18:11 -0000
***************
*** 4103,4111 ****
       when using custom variables:
  
  <programlisting>
! custom_variable_classes = 'plr,pljava'
  plr.path = '/usr/lib/R'
  pljava.foo = 1
  plruby.bar = true        # generates error, unknown class name
  </programlisting>
      </para>
--- 4103,4112 ----
       when using custom variables:
  
  <programlisting>
! custom_variable_classes = 'plperl,plr,pljava'
  plr.path = '/usr/lib/R'
  pljava.foo = 1
+ plperl.use_strict = true # now without having to use pl/perlU! :)
  plruby.bar = true        # generates error, unknown class name
  </programlisting>
      </para>

---

From: David Fetter <[email-masked]>
Date: Tue, 12 Jul 2005 13:01:56 -0700

> returning PostgreSQL arrays

Oops.  Persuant to corrections and clarifications by Andrew Dunstan,
please find enclosed a better patch.

Cheers,
D

Remember to vote!
--- doc/src/sgml/plperl.sgml	12 Jul 2005 19:59:40 -0000
+         plperl.use_strict to true in your postgresql.conf, or
  
     <para>
  
    <para>
  
    <para>
  
  <programlisting>
  
--- 200,245 ----
    </para>
  
    <para>
!     PL/Perl functions can also return sets of either scalar or
!     composite types.  In general, you'll want to return rows one at a
!     time both to speed up startup time and to keep from queueing up
!     the entire result set in memory.  You can do this with
!     <function>return_next</function> as illustrated below.  Note that
!     after the last <function>return_next</function>, you must put
!     either <literal>return;</literal> or (better) <literal>return
!     undef;</literal>
! 
! <programlisting>
! CREATE OR REPLACE FUNCTION perl_set_int(int)
! RETURNS SETOF INTEGER
! LANGUAGE plperl AS $$
!     foreach (0..$_[0]) {
!         return_next($_);
!     }
!     return undef;
! $$;
! 
! SELECT * FROM perl_set_int(5);
! 
! CREATE OR REPLACE FUNCTION perl_set()
! RETURNS SETOF testrowperl
! LANGUAGE plperl AS $$
!     return_next({f1 => 1, f2 => 'Hello', f3 => 'World' });
!     return_next({ f1 => 2, f2 => 'Hello', f3 => 'PostgreSQL' });
!     return_next({ f1 => 3, f2 => 'Hello', f3 => 'PL/Perl' });
!     return undef;
! $$;
! </programlisting>
! 
!     For small result sets, you can return a reference to an array that
!     contains either scalars, references to arrays, or references to
!     hashes for simple types, array types, and composite types,
!     respectively.  Here are some simple examples of returning the entire
!     result set as a reference:
  
  <programlisting>

  
      <para>
--- 256,261 ----
***************
*** 217,223 ****
    </para>
  
    <para>
  
     <variablelist>
      <varlistentry>
--- 286,292 ----
    </para>
  
    <para>
  
     <variablelist>
      <varlistentry>
***************
*** 228,233 ****
--- 297,306 ----

--- 353,361 ----

--- 363,372 ----
!     return undef;
! $$;
  
--- doc/src/sgml/runtime.sgml	12 Jul 2005 19:59:45 -0000
  
  <programlisting>
  
  <programlisting>

---

From: Neil Conway <[email-masked]>
Date: Wed, 13 Jul 2005 12:17:05 +1000

> please find enclosed a better patch.

Applied with editorialization; see comments below. Thanks for the patch.

> +         plperl.use_strict to true in your postgresql.conf, or

Needs a <filename/> and a <literal/>. Also the <xref/> doesn't compile 
using openjade 1.3.1

> +    Perl can return PostgreSQL arrays as references to Perl arrays.

Needs <productname/> around "PostgreSQL" for consistency with the rest 
of the SGML docs.

> + select returns_array();

CREATE FUNCTION ... AS $$ ... $$ LANGUAGE plperl; would be more 
consistent with the other PL/Perl examples.

> !     return_next({ f1 => 3, f2 => 'Hello', f3 => 'PL/Perl' });

Should probably use "&gt;" not ">".

>          Executes an SQL command.  Here is an example of a query

This needs more work -- the difference in behavior between spi_query() 
and spi_exec_query() is not described, so I didn't apply this hunk.

I didn't see why this was relevant, so I didn't apply it.

-Neil

---

From: Bruce Momjian <[email-masked]>
Date: Wed, 13 Jul 2005 00:22:42 -0400 (EDT)

Your patch has been added to the PostgreSQL unapplied patches list at:

	http://momjian.postgresql.org/cgi-bin/pgpatches

It will be applied as soon as one of the PostgreSQL committers reviews
and approves it.

---------------------------------------------------------------------------

> returning PostgreSQL arrays

[ Attachment, skipping... ]

> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  [email-masked]               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

---

From: Bruce Momjian <[email-masked]>
Date: Wed, 13 Jul 2005 00:23:27 -0400 (EDT)

Oops, already applied.  (Seems my threading email is busted.)