> > 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
