<!-- $PostgreSQL: pgsql/doc/src/sgml/plperl.sgml,v 2.78 2010/02/01 15:48:35 momjian Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/plperl.sgml,v 2.79 2010/02/05 18:11:46 momjian Exp $ -->

<chapter id="plperl">

<title>PL/Perl - Perl Procedural Language</title>

<primary>spi_exec_query</primary>

<secondary>in PL/Perl</secondary>

</indexterm>

<indexterm>

<primary>spi_query</primary>

<secondary>in PL/Perl</secondary>

</indexterm>

<indexterm>

<primary>spi_fetchrow</primary>

<secondary>in PL/Perl</secondary>

</indexterm>

<indexterm>

<primary>spi_prepare</primary>

<secondary>in PL/Perl</secondary>

</indexterm>

<indexterm>

<primary>spi_exec_prepared</primary>

<secondary>in PL/Perl</secondary>

</indexterm>

<indexterm>

<primary>spi_query_prepared</primary>

<secondary>in PL/Perl</secondary>

</indexterm>

<indexterm>

<primary>spi_cursor_close</primary>

<secondary>in PL/Perl</secondary>

</indexterm>

<indexterm>

<primary>spi_freeplan</primary>

<secondary>in PL/Perl</secondary>

</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>, <replaceable>arguments</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>

<literal>spi_exec_query</literal> executes an SQL command and

SELECT * FROM test_munge();

</programlisting>

</para>

</listitem>

</varlistentry>

<varlistentry>

<term><literal><function>spi_query</>(<replaceable>command</replaceable>)</literal></term>

<term><literal><function>spi_fetchrow</>(<replaceable>cursor</replaceable>)</literal></term>

<term><literal><function>spi_cursor_close</>(<replaceable>cursor</replaceable>)</literal></term>

<listitem>

<para>

<literal>spi_query</literal> and <literal>spi_fetchrow</literal>

work together as a pair for row sets which might be large, or for cases

</programlisting>

</para>

<para>

Normally, <function>spi_fetchrow</> should be repeated until it

returns <literal>undef</literal>, indicating that there are no more

rows to read. The cursor returned by <literal>spi_query</literal>

is automatically freed when

<function>spi_fetchrow</> returns <literal>undef</literal>.

If you do not wish to read all the rows, instead call

<function>spi_cursor_close</> to free the cursor.

Failure to do so will result in memory leaks.

</para>

</listitem>

</varlistentry>

<varlistentry>

<term><literal><function>spi_prepare</>(<replaceable>command</replaceable>, <replaceable>argument types</replaceable>)</literal></term>

<term><literal><function>spi_query_prepared</>(<replaceable>plan</replaceable>, <replaceable>arguments</replaceable>)</literal></term>

<term><literal><function>spi_exec_prepared</>(<replaceable>plan</replaceable> [, <replaceable>attributes</replaceable>], <replaceable>arguments</replaceable>)</literal></term>

<term><literal><function>spi_freeplan</>(<replaceable>plan</replaceable>)</literal></term>

<listitem>

<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

and <literal>spi_freeplan</literal> implement the same functionality but for prepared queries.

<literal>spi_prepare</literal> accepts a query string with numbered argument placeholders ($1, $2, etc)

and a string list of argument types:

<programlisting>

$plan = spi_prepare('SELECT * FROM test WHERE id &gt; $1 AND name = $2', 'INTEGER', 'TEXT');

</programlisting>

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

The optional second parameter to <literal>spi_exec_prepared</literal> is a hash reference of attributes;

the only attribute currently supported is <literal>limit</literal>, which sets the maximum number of rows returned by a query.

</para>

<para>

<para>

<programlisting>

CREATE OR REPLACE FUNCTION init() RETURNSINTEGER AS $$

CREATE OR REPLACE FUNCTION init() RETURNSVOID 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() RETURNSINTEGER AS $$

CREATE OR REPLACE FUNCTION done() RETURNSVOID AS $$

spi_freeplan( $_SHARED{my_plan});

undef $_SHARED{my_plan};

$$ LANGUAGE plperl;

</para>

<para>

Normally, <function>spi_fetchrow</> should be repeated until it

returns <literal>undef</literal>, indicating that there are no more

rows to read. The cursor is automatically freed when

<function>spi_fetchrow</> returns <literal>undef</literal>.

If you do not wish to read all the rows, instead call

<function>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 <literal>spi_exec_prepared</literal>:

</para>

</listitem>

<para>

<programlisting>

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 &lt;&lt; $1', 'inet');

$$ LANGUAGE plperl;

CREATE OR REPLACE FUNCTION query_hosts(inet) RETURNS SETOF hosts AS $$

return spi_exec_prepared(

$_SHARED{plan},

{limit =&gt; 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)

</programlisting>

</para>

</listitem>

</varlistentry>

</variablelist>

</sect2>