Re: [PATCHES] Doc patch: New PL/Perl Features
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.
+
+
+
+ Globally, by turning on plperl (one of the you can use) and setting
+ plperl.use_strict to true in your postgresql.conf, or
+
Needs a and a . Also the doesn't compile
using openjade 1.3.1
+Perl can return PostgreSQL arrays as references to Perl arrays.
+ Here is an example:
Needs around "PostgreSQL" for consistency with the rest
of the SGML docs.
+
+ 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;
! $$;
!
Should probably use ">" not ">".
spi_exec_query(query [,
max-rows])
spi_exec_query(command)
+
spi_query(query)
+ spi_fetchrow(result of
spi_query)
+
+
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:
! custom_variable_classes = 'plr,pljava'
plr.path = '/usr/lib/R'
pljava.foo = 1
plruby.bar = true# generates error, unknown class name
--- 4103,4112
when using custom variables:
! 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
I didn't see why this was relevant, so I didn't apply it.
-Neil
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [PATCHES] Doc patch: New PL/Perl Features
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.sgml5 Jun 2005 03:16:29 - 2.41
--- doc/src/sgml/plperl.sgml12 Jul 2005 19:59:40 -
***
*** 54,59
--- 54,86
The body of the function is ordinary Perl code.
+
+ As with ordinary Perl code, you should use the strict pragma,
+ which you can do one of two ways:
+
+
+
+
+ Globally, by turning on plperl (one of the you can use) and setting
+ plperl.use_strict to true in your postgresql.conf, or
+
+
+
+
+ One function at a time, by using PL/PerlU (you must be
+ database superuser to do this) and issuing a
+
+
+ use strict;
+
+
+ in the code.
+
+
+
+
The syntax of the CREATE FUNCTION command requires
***
*** 118,123
--- 145,165
+Perl can return PostgreSQL arrays as references to Perl arrays.
+ Here is an example:
+
+ CREATE OR REPLACE function returns_array()
+ RETURNS text[][]
+ LANGUAGE plperl
+ AS $$
+ return [['a"b','c,d'],['e\\f','g']];
+ $$;
+
+ select returns_array();
+
+
+
+
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
!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:
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
! 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
! return_next as illustrated below. Note that
! after the last return_next, you must put
! either return; or (better) return
! undef;
!
!
! 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;
! $$;
!
!
! 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:
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();
-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 return_next 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.
--- 256,261
***
*** 217,223
!PL/Perl itself presently provides two additional Perl commands:
--- 286,292
!PL/Perl itself presently provides four additional Perl commands:
***
*** 228,233
--- 297,306
spi_exec_query(query [,
max-rows])
spi_exec_query(command)
+
spi_query(query)
+ spi_fetchrow(result of
spi_query)
+
+
Executes an SQL command. Here is an example of a query
***
***
[PATCHES] Doc patch: New PL/Perl Features
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.sgml5 Jun 2005 03:16:29 - 2.41
--- doc/src/sgml/plperl.sgml12 Jul 2005 19:18:05 -
***
*** 54,59
--- 54,86
The body of the function is ordinary Perl code.
+
+ As with ordinary Perl code, you should use the strict pragma,
+ which you can do one of two ways:
+
+
+
+
+ Globally, by turning on plperl (one of the you can use) and setting
+ plperl.strict to true in your postgresql.conf, or
+
+
+
+
+ One function at a time, by using PL/PerlU (you must be
+ database superuser to do this) and issuing a
+
+
+ use strict;
+
+
+ in the code.
+
+
+
+
The syntax of the CREATE FUNCTION command requires
***
*** 118,123
--- 145,165
+Perl can return PostgreSQL arrays as references to Perl arrays.
+ Here is an example:
+
+ CREATE OR REPLACE function returns_array()
+ RETURNS text[][]
+ LANGUAGE plperl
+ AS $$
+ return [['a"b','c,d'],['e\\f','g']];
+ $$;
+
+ select returns_array();
+
+
+
+
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
!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:
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
! 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
! return_next as illustrated below:
!
! 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;
! $$;
!
!
! 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:
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();
-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 return_next 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.
--- 251,256
***
*** 217,223
!PL/Perl itself presently provides two additional Perl commands:
--- 281,287
!PL/Perl itself presently provides four additional Perl commands:
***
*** 228,233
--- 292,301
spi_exec_query(query [,
max-rows])
spi_exec_query(command)
+
spi_query(query)
+ spi_fetchrow(result of
spi_query)
+
+
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,
