> > please supply documentation (i.e. a patch to the SGML) to accompany this > > patch
the patch to doc/src/sgml/plperl.sgml is attached. -- Sincerely, Dmitry Karasik
Index: plperl.sgml =================================================================== RCS file: /projects/cvsroot/pgsql/doc/src/sgml/plperl.sgml,v retrieving revision 2.49 diff -c -r2.49 plperl.sgml *** plperl.sgml 4 Nov 2005 23:14:00 -0000 2.49 --- plperl.sgml 9 Dec 2005 12:47:54 -0000 *************** *** 296,302 **** </para> <para> ! PL/Perl provides three additional Perl commands: <variablelist> <varlistentry> --- 296,302 ---- </para> <para> ! PL/Perl provides additional Perl commands: <variablelist> <varlistentry> *************** *** 306,314 **** </indexterm> <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> --- 306,318 ---- </indexterm> <term><literal><function>spi_exec_query</>(<replaceable>query</replaceable> [, <replaceable>max-rows</replaceable>])</literal></term> <term><literal><function>spi_query</>(<replaceable>command</replaceable>)</literal></term> ! <term><literal><function>spi_fetchrow</>(<replaceable>cursor</replaceable>)</literal></term> ! <term><literal><function>spi_prepare</>(<replaceable>command</replaceable>, <replaceable>argument types</replaceable>)</literal></term> ! <term><literal><function>spi_exec_prepared</>(<replaceable>plan</replaceable>)</literal></term> ! <term><literal><function>spi_query_prepared</>(<replaceable>plan</replaceable> [, <replaceable>attributes</replaceable>], <replaceable>arguments</replaceable>)</literal></term> ! <term><literal><function>spi_cursor_close</>(<replaceable>cursor</replaceable>)</literal></term> ! <term><literal><function>spi_freeplan</>(<replaceable>plan</replaceable>)</literal></term> <listitem> <para> *************** *** 419,424 **** --- 423,488 ---- SELECT * from lotsa_md5(500); </programlisting> </para> + + <para> + <literal>spi_prepare</literal>, <literal>spi_query_prepared</literal>, <literal>spi_exec_prepared</literal>, + and <literal>spi_freeplan</literal> implement the same functionality but for prepared queries. Once + a query plan is prepared by a call to <literal>spi_prepare</literal>, the plan can be used instead + of the string query, either in <literal>spi_exec_prepared</literal>, where the result is the same as returned + by <literal>spi_exec_query</literal>, or in <literal>spi_query_prepared</literal> which returns a cursor + exactly as <literal>spi_query</literal> does, which can be later passed to <literal>spi_fetchrow</literal>. + </para> + + <para> + The advantage of prepared queries is that is it possible to use one prepared plan for more + than one query execution. After the plan is not needed anymore, it must be freed with + <literal>spi_freeplan</literal>: + </para> + + <para> + <programlisting> + CREATE OR REPLACE FUNCTION init() RETURNS INTEGER AS $$ + $_SHARED{my_plan} = spi_prepare( 'SELECT (now() + $1)::date AS now', 'INTERVAL'); + $$ LANGUAGE plperl; + + CREATE OR REPLACE FUNCTION add_time( INTERVAL ) RETURNS TEXT AS $$ + return spi_exec_prepared( + $_SHARED{my_plan}, + $_[0], + )->{rows}->[0]->{now}; + $$ LANGUAGE plperl; + + CREATE OR REPLACE FUNCTION done() RETURNS INTEGER AS $$ + spi_freeplan( $_SHARED{my_plan}); + undef $_SHARED{my_plan}; + $$ LANGUAGE plperl; + + SELECT init(); + SELECT add_time('1 day'), add_time('2 days'), add_time('3 days'); + SELECT done(); + + add_time | add_time | add_time + ------------+------------+------------ + 2005-12-10 | 2005-12-11 | 2005-12-12 + </programlisting> + </para> + + <para> + Note that the parameter subscript in <literal>spi_prepare</literal> is defined via + $1, $2, $3, etc, so avoid declaring query strings in double quotes that might easily + lead to hard-to-catch bugs. + </para> + + <para> + <literal>spi_cursor_close</literal> can be used to abort sequence of + <literal>spi_fetchrow</literal> calls. Normally, the call to + <literal>spi_fetchrow</literal> that returns <literal>undef</literal> is + the signal that there are no more rows to read. Also + that call automatically frees the cursor associated with the query. If it is desired not + to read all retuned rows, <literal>spi_cursor_close</literal> must be + called to avoid memory leaks. + </para> + </listitem> </varlistentry>
---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster