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 ****
! PL/Perl provides three additional Perl commands:
--- 296,302 ----
! PL/Perl provides additional Perl commands:
***************
*** 306,314 ****
spi_exec_query(query [, max-rows])
- spi_exec_query(command)
spi_query(command)
! spi_fetchrow(command)
--- 306,318 ----
spi_exec_query(query [, max-rows])
spi_query(command)
! spi_fetchrow(cursor)
! spi_prepare(command, argument types)
! spi_exec_prepared(plan)
! spi_query_prepared(plan [, attributes], arguments)
! spi_cursor_close(cursor)
! spi_freeplan(plan)
***************
*** 419,424 ****
--- 423,488 ----
SELECT * from lotsa_md5(500);
+
+
+ spi_prepare, spi_query_prepared, spi_exec_prepared,
+ and spi_freeplan implement the same functionality but for prepared queries. Once
+ a query plan is prepared by a call to spi_prepare, the plan can be used instead
+ of the string query, either in spi_exec_prepared, where the result is the same as returned
+ by spi_exec_query, or in spi_query_prepared which returns a cursor
+ exactly as spi_query does, which can be later passed to spi_fetchrow.
+
+
+
+ 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
+ spi_freeplan:
+
+
+
+
+ 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
+
+
+
+
+ Note that the parameter subscript in spi_prepare is defined via
+ $1, $2, $3, etc, so avoid declaring query strings in double quotes that might easily
+ lead to hard-to-catch bugs.
+
+
+
+ spi_cursor_close can be used to abort sequence of
+ spi_fetchrow calls. Normally, the call to
+ spi_fetchrow that returns undef 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, spi_cursor_close must be
+ called to avoid memory leaks.
+
+