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