On Sat, Jul 30, 2005 at 09:47:58AM -0400, Andrew Dunstan wrote:
>
>
> David Fetter wrote:
>
> You have rolled 2 problems into one - spi_query+spi_fetchrow does not
> address the issue of returning large data sets.
>
> Suggest instead:
[suggestion]
Revised patch attached. Thanks for catching this :)
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.sgml 13 Jul 2005 02:10:42 -0000 2.42
--- doc/src/sgml/plperl.sgml 31 Jul 2005 00:33:00 -0000
***************
*** 46,52 ****
<para>
To create a function in the PL/Perl language, use the standard
<xref linkend="sql-createfunction" endterm="sql-createfunction-title">
! syntax:
<programlisting>
CREATE FUNCTION <replaceable>funcname</replaceable>
(<replaceable>argument-types</replaceable>) RETURNS
<replaceable>return-type</replaceable> AS $$
# PL/Perl function body
--- 46,57 ----
<para>
To create a function in the PL/Perl language, use the standard
<xref linkend="sql-createfunction" endterm="sql-createfunction-title">
! 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.
!
<programlisting>
CREATE FUNCTION <replaceable>funcname</replaceable>
(<replaceable>argument-types</replaceable>) RETURNS
<replaceable>return-type</replaceable> AS $$
# PL/Perl function body
***************
*** 282,288 ****
</para>
<para>
! PL/Perl provides two additional Perl commands:
<variablelist>
<varlistentry>
--- 287,293 ----
</para>
<para>
! PL/Perl provides three additional Perl commands:
<variablelist>
<varlistentry>
***************
*** 293,303 ****
<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>
<listitem>
<para>
! Executes an SQL command. Here is an example of a query
! (<command>SELECT</command> command) with the optional maximum
! number of rows:
<programlisting>
$rv = spi_exec_query('SELECT * FROM my_table', 5);
</programlisting>
--- 298,315 ----
<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>command</replaceable>)</literal></term>
+
<term><literal><function>spi_fetchrow</>(<replaceable>command</replaceable>)</literal></term>
+
<listitem>
<para>
! <literal>spi_exec_query</literal> executes an SQL command and
! returns the entire rowset as a reference to an array of hash
! references. <emphasis>You should only use this command when you know
! that the result set will be relatively small.</emphasis> Here is an
! example of a query (<command>SELECT</command> command) with the
! optional maximum number of rows:
!
<programlisting>
$rv = spi_exec_query('SELECT * FROM my_table', 5);
</programlisting>
***************
*** 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();
</programlisting>
! </para>
</listitem>
</varlistentry>
--- 372,416 ----
SELECT * FROM test_munge();
</programlisting>
! </para>
! <para>
! <literal>spi_query</literal> and <literal>spi_fetchrow</literal>
! work together as a pair for rowsets which may be large, or for cases
! where you wish to return rows as they arrive.
! <literal>spi_fetchrow</literal> works <emphasis>only</emphasis> with
! <literal>spi_query</literal>. The following example illustrates how
! you use them together:
!
! <programlisting>
! 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);
! </programlisting>
! </para>
!
</listitem>
</varlistentry>
***************
*** 716,725 ****
<listitem>
<para>
! 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.
</para>
</listitem>
</itemizedlist>
</para>
--- 766,785 ----
<listitem>
<para>
! If you are fetching very large data sets using
! <literal>spi_exec_query</literal>, you should be aware that
! these will all go into memory. You can avoid this by using
! <literal>spi_query</literal>/<literal>spi_fetchrow</literal> as
! illustrated earlier.
</para>
+ <para>
+ A similar problem occurs if a set-returning function passes a
+ large set of rows back to postgres via <literal>return</literal>. You
+ can avoid this problem too by instead using
+ <literal>return_next</literal> for each row returned, as shown
+ previously.
+ </para>
+
</listitem>
</itemizedlist>
</para>
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
