diff --git a/doc/src/sgml/plperl.sgml b/doc/src/sgml/plperl.sgml
index 5fa7e3a..bb0345f 100644
--- a/doc/src/sgml/plperl.sgml
+++ b/doc/src/sgml/plperl.sgml
@@ -338,16 +338,36 @@ use strict;
spi_exec_query
in PL/Perl
+
+ spi_query
+ in PL/Perl
+
+
+ spi_fetchrow
+ in PL/Perl
+
+
+ spi_prepare
+ in PL/Perl
+
+
+ spi_exec_prepared
+ in PL/Perl
+
+
+ spi_query_prepared
+ in PL/Perl
+
+
+ spi_cursor_close
+ in PL/Perl
+
+
+ spi_freeplan
+ in PL/Perl
+
spi_exec_query(query [, max-rows])
- spi_query(command)
- spi_fetchrow(cursor)
- spi_prepare(command, argument types)
- spi_exec_prepared(plan, arguments)
- spi_query_prepared(plan [, attributes], arguments)
- spi_cursor_close(cursor)
- spi_freeplan(plan)
-
spi_exec_query executes an SQL command and
@@ -420,7 +440,15 @@ $$ LANGUAGE plperl;
SELECT * FROM test_munge();
+
+
+
+ spi_query(command)
+ spi_fetchrow(cursor)
+ spi_cursor_close(cursor)
+
+
spi_query and spi_fetchrow
work together as a pair for row sets which might be large, or for cases
@@ -460,12 +488,40 @@ SELECT * from lotsa_md5(500);
+ Normally, spi_fetchrow should be repeated until it
+ returns undef, indicating that there are no more
+ rows to read. The cursor returned by spi_query
+ is automatically freed when
+ spi_fetchrow returns undef.
+ If you do not wish to read all the rows, instead call
+ spi_cursor_close to free the cursor.
+ Failure to do so will result in memory leaks.
+
+
+
+
+
+
+ spi_prepare(command, argument types)
+ spi_query_prepared(plan, arguments)
+ spi_exec_prepared(plan [, attributes], arguments)
+ spi_freeplan(plan)
+
+
+
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
+ and spi_freeplan implement the same functionality but for prepared queries.
+ spi_prepare accepts a query string with numbered argument placeholders ($1, $2, etc)
+ and a string list of argument types:
+
+$plan = spi_prepare('SELECT * FROM test WHERE id > $1 AND name = $2', 'INTEGER', 'TEXT');
+
+ 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 optional second parameter to spi_exec_prepared is a hash reference of attributes;
+ the only attribute currently supported is limit, which sets the maximum number of rows returned by a query.
@@ -476,18 +532,18 @@ SELECT * from lotsa_md5(500);
-CREATE OR REPLACE FUNCTION init() RETURNS INTEGER AS $$
+CREATE OR REPLACE FUNCTION init() RETURNS VOID 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],
+ $_[0]
)->{rows}->[0]->{now};
$$ LANGUAGE plperl;
-CREATE OR REPLACE FUNCTION done() RETURNS INTEGER AS $$
+CREATE OR REPLACE FUNCTION done() RETURNS VOID AS $$
spi_freeplan( $_SHARED{my_plan});
undef $_SHARED{my_plan};
$$ LANGUAGE plperl;
@@ -509,15 +565,42 @@ SELECT done();
- Normally, spi_fetchrow should be repeated until it
- returns undef, indicating that there are no more
- rows to read. The cursor is automatically freed when
- spi_fetchrow returns undef.
- If you do not wish to read all the rows, instead call
- spi_cursor_close to free the cursor.
- Failure to do so will result in memory leaks.
+ Another example illustrates usage of an optional parameter in spi_exec_prepared:
-
+
+
+
+CREATE TABLE hosts AS SELECT id, ('192.168.1.'||id)::inet AS address FROM generate_series(1,3) AS id;
+
+CREATE OR REPLACE FUNCTION init_hosts_query() RETURNS VOID AS $$
+ $_SHARED{plan} = spi_prepare('SELECT * FROM hosts WHERE address << $1', 'inet');
+$$ LANGUAGE plperl;
+
+CREATE OR REPLACE FUNCTION query_hosts(inet) RETURNS SETOF hosts AS $$
+ return spi_exec_prepared(
+ $_SHARED{plan},
+ {limit => 2},
+ $_[0]
+ )->{rows};
+$$ LANGUAGE plperl;
+
+CREATE OR REPLACE FUNCTION release_hosts_query() RETURNS VOID AS $$
+ spi_freeplan($_SHARED{plan});
+ undef $_SHARED{plan};
+$$ LANGUAGE plperl;
+
+SELECT init_hosts_query();
+SELECT query_hosts('192.168.1.0/30');
+SELECT release_hosts_query();
+
+ query_hosts
+-----------------
+ (1,192.168.1.1)
+ (2,192.168.1.2)
+(2 rows)
+
+
+