On Fri, Jul 29, 2005 at 11:24:37PM -0400, Bruce Momjian wrote:
>
> Would someone who knows perl update plperl.sgml and send me a patch?
>
> Also, is this still true in 8.1:
>
> In the current implementation, if you are fetching or returning
> very large data sets, you should be aware that these will all go
> into memory.
That's no longer true. Please find enclosed a new 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.42
diff -c -r2.42 plperl.sgml
*** doc/src/sgml/plperl.sgml13 Jul 2005 02:10:42 - 2.42
--- doc/src/sgml/plperl.sgml30 Jul 2005 05:42:56 -
***
*** 46,52
To create a function in the PL/Perl language, use the standard
!syntax:
CREATE FUNCTION funcname
(argument-types) RETURNS
return-type AS $$
# PL/Perl function body
--- 46,57
To create a function in the PL/Perl language, use the standard
!syntax. A PL/Perl function must always return a scalar value. You
!can return more complex structures (arrays, records, and sets)
!in the appropriate context by returning a reference.
!Never return a list. Here follows an example of a PL/Perl
!function.
!
CREATE FUNCTION funcname
(argument-types) RETURNS
return-type AS $$
# PL/Perl function body
***
*** 282,288
!PL/Perl provides two additional Perl commands:
--- 287,293
!PL/Perl provides three additional Perl commands:
***
*** 293,303
spi_exec_query(query [,
max-rows])
spi_exec_query(command)
!Executes an SQL command. Here is an example of a query
!(SELECT command) with the optional maximum
!number of rows:
$rv = spi_exec_query('SELECT * FROM my_table', 5);
--- 298,315
spi_exec_query(query [,
max-rows])
spi_exec_query(command)
+
spi_query(command)
+
spi_fetchrow(command)
+
!spi_exec_query executes an SQL command and
! returns the entire rowset as a reference to an array of hash
! references. You should only use this command when you know
! that the result set will be relatively small. Here is an
! example of a query (SELECT command) with the
! optional maximum number of rows:
!
$rv = spi_exec_query('SELECT * FROM my_table', 5);
***
*** 345,351
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 $rv = spi_exec_query('select i, v from test;');
my $status = $rv->{status};
my $nrows = $rv->{processed};
--- 357,363
INSERT INTO test (i, v) VALUES (3, 'third line');
INSERT INTO test (i, v) VALUES (4, 'immortal');
! CREATE OR REPLACE FUNCTION test_munge() RETURNS SETOF test AS $$
my $rv = spi_exec_query('select i, v from test;');
my $status = $rv->{status};
my $nrows = $rv->{processed};
***
*** 360,366
SELECT * FROM test_munge();
!
--- 372,416
SELECT * FROM test_munge();
!
!
! spi_query and spi_fetchrow
! work together as a pair for rowsets which may be large, or for cases
! where you wish to return rows as they arrive.
! spi_fetchrow works only with
! spi_query. The following example illustrates how
! you use them together:
!
!
! CREATE TYPE foo_type AS (the_num INTEGER, the_text TEXT);
!
! CREATE OR REPLACE FUNCTION lotsa_md5 (INTEGER) RETURNS SETOF foo_type AS $$
! use Digest::MD5 qw(md5_hex);
! my $file = '/usr/share/dict/words';
! my $t = localtime;
! elog(NOTICE, "opening file $file at $t" );
! open my $fh, '<', $file # ooh, it's a file access!
! or elog(ERROR, "Can't open $file for reading: $!");
! my @words = <$fh>;
! close $fh;
! $t = localtime;
! elog(NOTICE, "closed file $file at $t");
! chomp(@words);
! my $row;
! my $sth = spi_query("SELECT * FROM generate_series(1,$_[0]) AS b(a)");
! while (defined ($row = spi_fetchrow($sth))) {
! return_next({
! the_num => $row->{a},
! the_text => md5_hex($words[rand @words])
! });
! }
! return;
! $$ LANGUAGE plperlu;
!
! SELECT * from lotsa_md5(500);
!
!
!
***
*** 716,724
! In the current implementation, if you are fetching or returning
! very large data sets, you should be aware that these will all go
! into memory.
--- 766,776
! If you are fetching
