public inbox for [email protected]
help / color / mirror / Atom feedDoc patch: New PL/Perl Features
5+ messages / 3 participants
[nested] [flat]
* Doc patch: New PL/Perl Features
@ 2005-07-12 19:21 David Fetter <[email protected]>
0 siblings, 3 replies; 5+ messages in thread
From: David Fetter @ 2005-07-12 19:21 UTC (permalink / raw)
To: pgsql-docs; PostgreSQL Patches <[email protected]>
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 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/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->{status};
my $nrows = $rv->{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->{status};
my $nrows = $rv->{processed};
***************
*** 289,298 ****
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;
! $$ LANGUAGE plperl;
SELECT * FROM test_munge();
</programlisting>
--- 358,367 ----
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}));
! 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>
Attachments:
[text/plain] plperl_new_features.diff (7.3K, 2-plperl_new_features.diff)
download | inline diff:
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->{status};
my $nrows = $rv->{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->{status};
my $nrows = $rv->{processed};
***************
*** 289,298 ****
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;
! $$ LANGUAGE plperl;
SELECT * FROM test_munge();
</programlisting>
--- 358,367 ----
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}));
! 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>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [PATCHES] Doc patch: New PL/Perl Features
@ 2005-07-12 20:01 David Fetter <[email protected]>
parent: David Fetter <[email protected]>
2 siblings, 1 reply; 5+ messages in thread
From: David Fetter @ 2005-07-12 20:01 UTC (permalink / raw)
To: pgsql-docs; PostgreSQL Patches <[email protected]>
On Tue, Jul 12, 2005 at 12:21:29PM -0700, David Fetter wrote:
> 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
Oops. Persuant to corrections and clarifications by Andrew Dunstan,
please find enclosed a better patch.
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/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:59:40 -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.use_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,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>
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>
--- 256,261 ----
***************
*** 217,223 ****
</para>
<para>
! PL/Perl itself presently provides two additional Perl commands:
<variablelist>
<varlistentry>
--- 286,292 ----
</para>
<para>
! PL/Perl itself presently provides four additional Perl commands:
<variablelist>
<varlistentry>
***************
*** 228,233 ****
--- 297,306 ----
<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->{status};
my $nrows = $rv->{processed};
--- 353,361 ----
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->{status};
my $nrows = $rv->{processed};
***************
*** 289,298 ****
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;
! $$ LANGUAGE plperl;
SELECT * FROM test_munge();
</programlisting>
--- 363,372 ----
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}));
! return_next($row);
}
! return undef;
! $$;
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:59:45 -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>
Attachments:
[text/plain] plperl_new_features.diff (7.5K, 2-plperl_new_features.diff)
download | inline diff:
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:59:40 -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.use_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,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>
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>
--- 256,261 ----
***************
*** 217,223 ****
</para>
<para>
! PL/Perl itself presently provides two additional Perl commands:
<variablelist>
<varlistentry>
--- 286,292 ----
</para>
<para>
! PL/Perl itself presently provides four additional Perl commands:
<variablelist>
<varlistentry>
***************
*** 228,233 ****
--- 297,306 ----
<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->{status};
my $nrows = $rv->{processed};
--- 353,361 ----
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->{status};
my $nrows = $rv->{processed};
***************
*** 289,298 ****
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;
! $$ LANGUAGE plperl;
SELECT * FROM test_munge();
</programlisting>
--- 363,372 ----
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}));
! return_next($row);
}
! return undef;
! $$;
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:59:45 -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>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [PATCHES] Doc patch: New PL/Perl Features
@ 2005-07-13 02:17 Neil Conway <[email protected]>
parent: David Fetter <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: Neil Conway @ 2005-07-13 02:17 UTC (permalink / raw)
To: David Fetter <[email protected]>; +Cc: pgsql-docs; PostgreSQL Patches <[email protected]>
David Fetter wrote:
> On Tue, Jul 12, 2005 at 12:21:29PM -0700, David Fetter wrote:
>
>>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
>
>
> Oops. Persuant to corrections and clarifications by Andrew Dunstan,
> please find enclosed a better patch.
Applied with editorialization; see comments below. Thanks for the patch.
> + <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.use_strict to true in your postgresql.conf, or
> + </para>
Needs a <filename/> and a <literal/>. Also the <xref/> doesn't compile
using openjade 1.3.1
> <para>
> + Perl can return PostgreSQL arrays as references to Perl arrays.
> + Here is an example:
Needs <productname/> around "PostgreSQL" for consistency with the rest
of the SGML docs.
> + <programlisting>
> + CREATE OR REPLACE function returns_array()
> + RETURNS text[][]
> + LANGUAGE plperl
> + AS $$
> + return [['a"b','c,d'],['e\\f','g']];
> + $$;
> +
> + select returns_array();
CREATE FUNCTION ... AS $$ ... $$ LANGUAGE plperl; would be more
consistent with the other PL/Perl examples.
> ! 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>
Should probably use ">" not ">".
> <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
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.
> *** 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>
I didn't see why this was relevant, so I didn't apply it.
-Neil
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Doc patch: New PL/Perl Features
@ 2005-07-13 04:22 Bruce Momjian <[email protected]>
parent: David Fetter <[email protected]>
2 siblings, 0 replies; 5+ messages in thread
From: Bruce Momjian @ 2005-07-13 04:22 UTC (permalink / raw)
To: David Fetter <[email protected]>; +Cc: pgsql-docs; PostgreSQL Patches <[email protected]>
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.
---------------------------------------------------------------------------
David Fetter wrote:
> 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 protected] http://fetter.org/
> phone: +1 510 893 6100 mobile: +1 415 235 3778
>
> Remember to vote!
[ 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 protected] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Doc patch: New PL/Perl Features
@ 2005-07-13 04:23 Bruce Momjian <[email protected]>
parent: David Fetter <[email protected]>
2 siblings, 0 replies; 5+ messages in thread
From: Bruce Momjian @ 2005-07-13 04:23 UTC (permalink / raw)
To: David Fetter <[email protected]>; +Cc: pgsql-docs; PostgreSQL Patches <[email protected]>
Oops, already applied. (Seems my threading email is busted.)
---------------------------------------------------------------------------
David Fetter wrote:
> 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 protected] http://fetter.org/
> phone: +1 510 893 6100 mobile: +1 415 235 3778
>
> Remember to vote!
[ 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 protected] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-07-13 04:23 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-07-12 19:21 Doc patch: New PL/Perl Features David Fetter <[email protected]>
2005-07-12 20:01 ` David Fetter <[email protected]>
2005-07-13 02:17 ` Neil Conway <[email protected]>
2005-07-13 04:22 ` Bruce Momjian <[email protected]>
2005-07-13 04:23 ` Bruce Momjian <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox