plperl db access documentation enhancement

Started by Alexey Klyukinalmost 16 years ago5 messages

alexk@commandprompt.com

almost 16 years ago

1 attachment(s)

Hello,

We were asked by Enova Financial to improve the documentation of PL/Perl database access functions.
Alvaro and me worked on that and we produced the patch that is attached. It splits initial block of functions
into the groups with the description directly following each of the group, corrects couple of mistakes and
adds an example.

One of the existing mistakes was confusion in definitions of spi_exec_prepared and spi_query_prepared.
Another one is usage of INTEGER type to return the result of spi_prepare in the example for prepared queries.
When trying to execute that function I've got the following error:

postgres=# CREATE OR REPLACE FUNCTION init() RETURNS INTEGER AS $#
$_SHARED{my_plan} = spi_prepare( 'SELECT (now() + $1)::date AS now', 'INTERVAL');
$$ LANGUAGE plperl;

CREATE FUNCTION

postgres=# select init();
ERROR: invalid input syntax for integer: "0x1007d6f40"
CONTEXT: PL/Perl function "init"

Since the return value is not used anyway, I've changed the return type of the function declaration in the example to VOID.

I think this is a good reason to suggest backpatching these changes down to 8.2.

Attachments:

plperl_db.diffapplication/octet-stream; name=plperl_db.diffDownload

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;
       <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
@@ -420,7 +440,15 @@ $$ LANGUAGE plperl;
 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
@@ -460,12 +488,40 @@ SELECT * from lotsa_md5(500);
     </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>
@@ -476,18 +532,18 @@ SELECT * from lotsa_md5(500);
 
     <para>
     <programlisting>
-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();
     </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>

Bruce Momjian

bruce@momjian.us

almost 16 years ago

In reply to: Alexey Klyukin (#1)

Re: plperl db access documentation enhancement

Patch applied for 9.0. We don't normally backpatch such documentation
improvements unless we receive multiple reports of confusion.

---------------------------------------------------------------------------

Alexey Klyukin wrote:

Hello,

We were asked by Enova Financial to improve the documentation of PL/Perl database access functions.
Alvaro and me worked on that and we produced the patch that is attached. It splits initial block of functions
into the groups with the description directly following each of the group, corrects couple of mistakes and
adds an example.

One of the existing mistakes was confusion in definitions of spi_exec_prepared and spi_query_prepared.
Another one is usage of INTEGER type to return the result of spi_prepare in the example for prepared queries.
When trying to execute that function I've got the following error:

postgres=# CREATE OR REPLACE FUNCTION init() RETURNS INTEGER AS $#
$_SHARED{my_plan} = spi_prepare( 'SELECT (now() + $1)::date AS now', 'INTERVAL');
$$ LANGUAGE plperl;

CREATE FUNCTION

postgres=# select init();
ERROR: invalid input syntax for integer: "0x1007d6f40"
CONTEXT: PL/Perl function "init"

Since the return value is not used anyway, I've changed the return type of the function declaration in the example to VOID.

I think this is a good reason to suggest backpatching these changes down to 8.2.

[ Attachment, skipping... ]

--
Alexey Klyukin http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Alvaro Herrera

alvherre@commandprompt.com

almost 16 years ago

In reply to: Bruce Momjian (#2)

Re: plperl db access documentation enhancement

Bruce Momjian wrote:

Patch applied for 9.0. We don't normally backpatch such documentation
improvements unless we receive multiple reports of confusion.

I think that's a mistake in this case. The documentation wasn't
confusing -- it was bogus. (Actually, the bug fixing is a smaller
change than the whole of this patch, so we could provide the smaller
patch if desired to apply to 8.4. To be honest I think it is better to
just apply the larger patch verbatim.)

I could do the backpatch if you want. I just wanted some more peer
review on the changes.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Bruce Momjian

bruce@momjian.us

almost 16 years ago

In reply to: Alvaro Herrera (#3)

Re: plperl db access documentation enhancement

Alvaro Herrera wrote:

Bruce Momjian wrote:

Patch applied for 9.0. We don't normally backpatch such documentation
improvements unless we receive multiple reports of confusion.

I think that's a mistake in this case. The documentation wasn't
confusing -- it was bogus. (Actually, the bug fixing is a smaller
change than the whole of this patch, so we could provide the smaller
patch if desired to apply to 8.4. To be honest I think it is better to
just apply the larger patch verbatim.)

I could do the backpatch if you want. I just wanted some more peer
review on the changes.

Sure, go ahead if you are sure. I wasn't clear enough to risk it, and
documentation churn in back branches has its own downsides, which is why
I avoid it, especially for larger patches.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Alvaro Herrera

alvherre@commandprompt.com

almost 16 years ago

In reply to: Bruce Momjian (#4)

Re: plperl db access documentation enhancement

Bruce Momjian wrote:

Alvaro Herrera wrote:

Bruce Momjian wrote:

Patch applied for 9.0. We don't normally backpatch such documentation
improvements unless we receive multiple reports of confusion.

I think that's a mistake in this case. The documentation wasn't
confusing -- it was bogus. (Actually, the bug fixing is a smaller
change than the whole of this patch, so we could provide the smaller
patch if desired to apply to 8.4. To be honest I think it is better to
just apply the larger patch verbatim.)

I could do the backpatch if you want. I just wanted some more peer
review on the changes.

Sure, go ahead if you are sure. I wasn't clear enough to risk it, and
documentation churn in back branches has its own downsides, which is why
I avoid it, especially for larger patches.

Just applied it.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support