From ac9f2546841984d966ab31388678db318e6ad29f Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Fri, 5 Feb 2010 18:11:46 +0000 Subject: [PATCH] Improve PL/Perl documentation of database access functions. Alexey Klyukin --- doc/src/sgml/plperl.sgml | 127 +++++++++++++++++++++++++++++++++++++++-------- 1 file changed, 105 insertions(+), 22 deletions(-) diff --git a/doc/src/sgml/plperl.sgml b/doc/src/sgml/plperl.sgml index 3f466bce1e..c5c0056d99 100644 --- a/doc/src/sgml/plperl.sgml +++ b/doc/src/sgml/plperl.sgml @@ -1,4 +1,4 @@ - + PL/Perl - Perl Procedural Language @@ -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) + + + -- 2.11.0