Hello
this patch contains ansi sql scrollable cursors's support for plpgsql. Add
three function to SPI and plpgsql scrollable cursor sup. is first test app
of this functionality.
Regards
Pavel Stehule
_________________________________________________________________
Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com.
http://www.msn.cz/
*** ./doc/src/sgml/plpgsql.sgml.orig 2007-01-26 20:30:17.000000000 +0100
--- ./doc/src/sgml/plpgsql.sgml 2007-01-26 21:33:38.000000000 +0100
***************
*** 2354,2360 ****
internally to avoid memory problems.) A more interesting usage is to
return a reference to a cursor that a function has created, allowing the
caller to read the rows. This provides an efficient way to return
! large row sets from functions.
</para>
<sect2 id="plpgsql-cursor-declarations">
--- 2354,2361 ----
internally to avoid memory problems.) A more interesting usage is to
return a reference to a cursor that a function has created, allowing the
caller to read the rows. This provides an efficient way to return
! large row sets from functions. PL/pgSQL allows to use scrollable
! cursors.
</para>
<sect2 id="plpgsql-cursor-declarations">
***************
*** 2368,2374 ****
Another way is to use the cursor declaration syntax,
which in general is:
<synopsis>
! <replaceable>name</replaceable> CURSOR <optional> ( <replaceable>arguments</replaceable> ) </optional> FOR <replaceable>query</replaceable>;
</synopsis>
(<literal>FOR</> may be replaced by <literal>IS</> for
<productname>Oracle</productname> compatibility.)
--- 2369,2375 ----
Another way is to use the cursor declaration syntax,
which in general is:
<synopsis>
! <replaceable>name</replaceable> <optional> <optional> NO </optional> SCROLL </optional> CURSOR <optional> ( <replaceable>arguments</replaceable> ) </optional> FOR <replaceable>query</replaceable>;
</synopsis>
(<literal>FOR</> may be replaced by <literal>IS</> for
<productname>Oracle</productname> compatibility.)
***************
*** 2517,2523 ****
<title><literal>FETCH</></title>
<synopsis>
! FETCH <replaceable>cursor</replaceable> INTO <replaceable>target</replaceable>;
</synopsis>
<para>
--- 2518,2524 ----
<title><literal>FETCH</></title>
<synopsis>
! FETCH <optional> <replaceable>direction</replaceable> FROM </optional> <replaceable>cursor</replaceable> INTO <replaceable>target</replaceable>;
</synopsis>
<para>
***************
*** 2526,2539 ****
variable, or a comma-separated list of simple variables, just like
<command>SELECT INTO</command>. As with <command>SELECT
INTO</command>, the special variable <literal>FOUND</literal> may
! be checked to see whether a row was obtained or not.
</para>
-
<para>
An example:
<programlisting>
FETCH curs1 INTO rowvar;
FETCH curs2 INTO foo, bar, baz;
</programlisting>
</para>
</sect3>
--- 2527,2545 ----
variable, or a comma-separated list of simple variables, just like
<command>SELECT INTO</command>. As with <command>SELECT
INTO</command>, the special variable <literal>FOUND</literal> may
! be checked to see whether a row was obtained or not. More details
! about <replaceable>direction</replaceable> you can find in
! <xref linkend="sql-fetch"> without <literal>BACKWARD</> and <literal>FORWARD</> keywords.
! Statement <command>FETCH</command> in <application>PL/pgSQL</> returns only one
! or zero row every time.
</para>
<para>
An example:
<programlisting>
FETCH curs1 INTO rowvar;
FETCH curs2 INTO foo, bar, baz;
+ FETCH LAST INTO x, y;
+ FETCH RELATIVE -2 INTO x;
</programlisting>
</para>
</sect3>
*** ./doc/src/sgml/spi.sgml.orig 2007-01-14 12:37:19.000000000 +0100
--- ./doc/src/sgml/spi.sgml 2007-01-26 11:46:18.000000000 +0100
***************
*** 800,805 ****
--- 800,937 ----
<!-- *********************************************** -->
+ <refentry id="spi-spi-prepare-cursor">
+ <refmeta>
+ <refentrytitle>SPI_prepare_cursor</refentrytitle>
+ </refmeta>
+
+ <refnamediv>
+ <refname>SPI_prepare_cursor</refname>
+ <refpurpose>prepare a plan for a cursor, without executing it yet</refpurpose>
+ </refnamediv>
+
+ <indexterm><primary>SPI_prepare_cursor</primary></indexterm>
+
+ <refsynopsisdiv>
+ <synopsis>
+ void * SPI_prepare_cursor(const char * <parameter>command</parameter>, int <parameter>nargs</parameter>, Oid * <parameter>argtypes</parameter>, int <parameter>options</parameter>)
+ </synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <function>SPI_prepare_cursor</function> creates and returns an execution
+ plan for the specified select but doesn't execute the command.
+ This function should only be called from a connected procedure. This
+ function allows set cursor's options.
+ </para>
+
+ <para>
+ When the same or a similar command is to be executed repeatedly, it
+ may be advantageous to perform the planning only once.
+ <function>SPI_prepare_cursor</function> converts a command string into an
+ execution plan that can be executed repeatedly using
+ <function>SPI_execute_plan</function>.
+ </para>
+
+ <para>
+ A prepared command can be generalized by writing parameters
+ (<literal>$1</>, <literal>$2</>, etc.) in place of what would be
+ constants in a normal command. The actual values of the parameters
+ are then specified when <function>SPI_execute_plan</function> is called.
+ This allows the prepared command to be used over a wider range of
+ situations than would be possible without parameters.
+ </para>
+
+ <para>
+ The plan returned by <function>SPI_prepare_cursor</function> can be used
+ only in the current invocation of the procedure, since
+ <function>SPI_finish</function> frees memory allocated for a plan.
+ But a plan can be saved for longer using the function
+ <function>SPI_saveplan</function>.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Arguments</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>const char * <parameter>command</parameter></literal></term>
+ <listitem>
+ <para>
+ command string
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>int <parameter>nargs</parameter></literal></term>
+ <listitem>
+ <para>
+ number of input parameters (<literal>$1</>, <literal>$2</>, etc.)
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>Oid * <parameter>argtypes</parameter></literal></term>
+ <listitem>
+ <para>
+ pointer to an array containing the <acronym>OID</acronym>s of
+ the data types of the parameters
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>int <parameter>options</parameter></literal></term>
+ <listitem>
+ <para>This option allow to set cursor's options. It's one or combination of
+ <symbol>CURSOR_OPT_BINARY</symbol>,
+ <symbol>CURSOR_OPT_SCROLL</symbol>,
+ <symbol>CURSOR_OPT_NO_SCROLL</symbol>,
+ <symbol>CURSOR_OPT_INSENSITIVE</symbol>, or
+ <symbol>CURSOR_OPT_HOLD</symbol>. Currently only <symbol>CURSOR_OPT_SCROLL</symbol>
+ is supported and allowed.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Return Value</title>
+
+ <para>
+ <function>SPI_prepare_cursor</function> returns a non-null pointer to an
+ execution plan. On error, <symbol>NULL</symbol> will be returned,
+ and <varname>SPI_result</varname> will be set to one of the same
+ error codes used by <function>SPI_execute</function>, except that
+ it is set to <symbol>SPI_ERROR_ARGUMENT</symbol> if
+ <parameter>command</parameter> is <symbol>NULL</symbol>, or if
+ <parameter>nargs</> is less than 0, or if <parameter>nargs</> is
+ greater than 0 and <parameter>argtypes</> is <symbol>NULL</symbol>,
+ or if are used any unsupported <parameter>options</parameter>.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Notes</title>
+
+ <para>
+ There is a disadvantage to using parameters: since the planner does
+ not know the values that will be supplied for the parameters, it
+ may make worse planning choices than it would make for a normal
+ command with all constants visible.
+ </para>
+ </refsect1>
+ </refentry>
+
+ <!-- *********************************************** -->
+
<refentry id="spi-spi-getargcount">
<refmeta>
<refentrytitle>SPI_getargcount</refentrytitle>
***************
*** 1472,1477 ****
--- 1604,1685 ----
<!-- *********************************************** -->
+ <refentry id="spi-spi-scroll-cursor-fetch">
+ <refmeta>
+ <refentrytitle>SPI_scroll_cursor_fetch</refentrytitle>
+ </refmeta>
+
+ <refnamediv>
+ <refname>SPI_scroll_cursor_fetch</refname>
+ <refpurpose>fetch some rows from a scrollable cursor</refpurpose>
+ </refnamediv>
+
+ <indexterm><primary>SPI_scroll_cursor_fetch</primary></indexterm>
+
+ <refsynopsisdiv>
+ <synopsis>
+ void SPI_scroll_cursor_fetch(Portal <parameter>portal</parameter>, FetchDirection <parameter>direction</parameter>, long <parameter>count</parameter>)
+ </synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <function>SPI_scroll_cursor_fetch</function> fetches some rows from a
+ cursor. This is equivalent to the SQL command <command>FETCH</>.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Arguments</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>Portal <parameter>portal</parameter></literal></term>
+ <listitem>
+ <para>
+ portal containing the cursor
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>FetchDirection <parameter>forward</parameter></literal></term>
+ <listitem>
+ <para>
+ It's one from <symbol>FETCH_FORWARD</symbol>,
+ <symbol>FETCH_BACKWARD</symbol>,
+ <symbol>FETCH_ABSOLUTE</symbol> or
+ <symbol>FETCH_RELATIVE</symbol> values. Please, look to SQL command <command>FETCH</>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>long <parameter>count</parameter></literal></term>
+ <listitem>
+ <para>
+ maximum number of rows to fetch.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Return Value</title>
+
+ <para>
+ <varname>SPI_processed</varname> and
+ <varname>SPI_tuptable</varname> are set as in
+ <function>SPI_execute</function> if successful.
+ </para>
+ </refsect1>
+ </refentry>
+
+ <!-- *********************************************** -->
+
<refentry id="spi-spi-cursor-move">
<refmeta>
<refentrytitle>SPI_cursor_move</refentrytitle>
***************
*** 1517,1523 ****
<term><literal>bool <parameter>forward</parameter></literal></term>
<listitem>
<para>
! true for move forward, false for move backward
</para>
</listitem>
</varlistentry>
--- 1725,1798 ----
<term><literal>bool <parameter>forward</parameter></literal></term>
<listitem>
<para>
! true for fetch forward, false for fetch backward
! </para>
! </listitem>
! </varlistentry>
!
! <varlistentry>
! <term><literal>long <parameter>count</parameter></literal></term>
! <listitem>
! <para>
! maximum number of rows to move
! </para>
! </listitem>
! </varlistentry>
! </variablelist>
! </refsect1>
! </refentry>
!
! <!-- *********************************************** -->
!
! <refentry id="spi-spi-scroll-cursor-move">
! <refmeta>
! <refentrytitle>SPI_scroll_cursor_move</refentrytitle>
! </refmeta>
!
! <refnamediv>
! <refname>SPI_scroll_cursor_move</refname>
! <refpurpose>move a scrollable cursor</refpurpose>
! </refnamediv>
!
! <indexterm><primary>SPI_scroll_cursor_move</primary></indexterm>
!
! <refsynopsisdiv>
! <synopsis>
! void SPI_scroll_cursor_move(Portal <parameter>portal</parameter>, FetchDirection <parameter>direction</parameter>, long <parameter>count</parameter>)
! </synopsis>
! </refsynopsisdiv>
!
! <refsect1>
! <title>Description</title>
!
! <para>
! <function>SPI_scroll_cursor_move</function> skips over some number of rows
! in a cursor. This is equivalent to the SQL command
! <command>MOVE</>.
! </para>
! </refsect1>
!
! <refsect1>
! <title>Arguments</title>
!
! <variablelist>
! <varlistentry>
! <term><literal>Portal <parameter>portal</parameter></literal></term>
! <listitem>
! <para>
! portal containing the cursor
! </para>
! </listitem>
! </varlistentry>
!
! <varlistentry>
! <term><literal>FetchDirection <parameter>forward</parameter></literal></term>
! <listitem>
! <para>
! It's one from <symbol>FETCH_FORWARD</symbol>,
! <symbol>FETCH_BACKWARD</symbol>,
! <symbol>FETCH_ABSOLUTE</symbol> or
! <symbol>FETCH_RELATIVE</symbol> values. Please, look to SQL command <command>FETCH</>.
</para>
</listitem>
</varlistentry>
*** ./src/backend/commands/prepare.c.orig 2007-01-23 22:35:33.000000000 +0100
--- ./src/backend/commands/prepare.c 2007-01-26 19:07:21.000000000 +0100
***************
*** 105,111 ****
query_list = QueryRewrite(query);
/* Generate plans for queries. Snapshot is already set. */
! plan_list = pg_plan_queries(query_list, NULL, false);
/*
* Save the results. We don't have the query string for this PREPARE, but
--- 105,111 ----
query_list = QueryRewrite(query);
/* Generate plans for queries. Snapshot is already set. */
! plan_list = pg_plan_queries(query_list, NULL, false, false, 0);
/*
* Save the results. We don't have the query string for this PREPARE, but
*** ./src/backend/executor/functions.c.orig 2007-01-23 22:36:52.000000000 +0100
--- ./src/backend/executor/functions.c 2007-01-26 19:02:43.000000000 +0100
***************
*** 124,130 ****
errmsg("%s is not allowed in a non-volatile function",
CreateQueryTag(queryTree))));
! planTree = pg_plan_query(queryTree, NULL);
newes = (execution_state *) palloc(sizeof(execution_state));
if (preves)
--- 124,130 ----
errmsg("%s is not allowed in a non-volatile function",
CreateQueryTag(queryTree))));
! planTree = pg_plan_query(queryTree, NULL, false, 0);
newes = (execution_state *) palloc(sizeof(execution_state));
if (preves)
*** ./src/backend/executor/spi.c.orig 2007-01-14 09:54:37.000000000 +0100
--- ./src/backend/executor/spi.c 2007-01-26 19:21:44.000000000 +0100
***************
*** 34,40 ****
static int _SPI_connected = -1;
static int _SPI_curid = -1;
! static void _SPI_prepare_plan(const char *src, _SPI_plan *plan);
static int _SPI_execute_plan(_SPI_plan *plan,
Datum *Values, const char *Nulls,
--- 34,40 ----
static int _SPI_connected = -1;
static int _SPI_curid = -1;
! static void _SPI_prepare_plan(const char *src, _SPI_plan *plan, bool isCursor, int options);
static int _SPI_execute_plan(_SPI_plan *plan,
Datum *Values, const char *Nulls,
***************
*** 45,51 ****
static void _SPI_error_callback(void *arg);
! static void _SPI_cursor_operation(Portal portal, bool forward, long count,
DestReceiver *dest);
static _SPI_plan *_SPI_copy_plan(_SPI_plan *plan, int location);
--- 45,51 ----
static void _SPI_error_callback(void *arg);
! static void _SPI_cursor_operation(Portal portal, FetchDirection direction, long count,
DestReceiver *dest);
static _SPI_plan *_SPI_copy_plan(_SPI_plan *plan, int location);
***************
*** 311,317 ****
plan.nargs = 0;
plan.argtypes = NULL;
! _SPI_prepare_plan(src, &plan);
res = _SPI_execute_plan(&plan, NULL, NULL,
InvalidSnapshot, InvalidSnapshot,
--- 311,317 ----
plan.nargs = 0;
plan.argtypes = NULL;
! _SPI_prepare_plan(src, &plan, false, 0);
res = _SPI_execute_plan(&plan, NULL, NULL,
InvalidSnapshot, InvalidSnapshot,
***************
*** 418,424 ****
plan.nargs = nargs;
plan.argtypes = argtypes;
! _SPI_prepare_plan(src, &plan);
/* copy plan to procedure context */
result = _SPI_copy_plan(&plan, _SPI_CPLAN_PROCXT);
--- 418,424 ----
plan.nargs = nargs;
plan.argtypes = argtypes;
! _SPI_prepare_plan(src, &plan, false, 0);
/* copy plan to procedure context */
result = _SPI_copy_plan(&plan, _SPI_CPLAN_PROCXT);
***************
*** 428,433 ****
--- 428,473 ----
return (void *) result;
}
+
+ void *
+ SPI_prepare_cursor(const char *src, int nargs, Oid *argtypes, int options)
+ {
+ _SPI_plan plan;
+ _SPI_plan *result;
+
+ if (src == NULL || nargs < 0 || (nargs > 0 && argtypes == NULL))
+ {
+ SPI_result = SPI_ERROR_ARGUMENT;
+ return NULL;
+ }
+
+ /* only scrollable cursors are supported currently */
+ if (options != CURSOR_OPT_SCROLL && options != CURSOR_OPT_NO_SCROLL)
+ {
+ SPI_result = SPI_ERROR_ARGUMENT;
+ return NULL;
+ }
+
+ SPI_result = _SPI_begin_call(true);
+ if (SPI_result < 0)
+ return NULL;
+
+ plan.plancxt = NULL; /* doesn't have own context */
+ plan.query = src;
+ plan.nargs = nargs;
+ plan.argtypes = argtypes;
+
+ _SPI_prepare_plan(src, &plan, true, options);
+
+ /* copy plan to procedure context */
+ result = _SPI_copy_plan(&plan, _SPI_CPLAN_PROCXT);
+
+ _SPI_end_call(true);
+
+ return (void *) result;
+ }
+
+
void *
SPI_saveplan(void *plan)
{
***************
*** 824,830 ****
* SPI_cursor_open()
*
* Open a prepared SPI plan as a portal
! */
Portal
SPI_cursor_open(const char *name, void *plan,
Datum *Values, const char *Nulls,
--- 864,870 ----
* SPI_cursor_open()
*
* Open a prepared SPI plan as a portal
! */
Portal
SPI_cursor_open(const char *name, void *plan,
Datum *Values, const char *Nulls,
***************
*** 988,993 ****
--- 1028,1060 ----
/*
+ * SPI_scroll_cursor_fetch()
+ *
+ * Fetch rows in a scrollable cursor
+ */
+ void
+ SPI_scroll_cursor_fetch(Portal portal, FetchDirection direction, long count)
+ {
+ _SPI_cursor_operation(portal, direction, count,
+ CreateDestReceiver(DestSPI, NULL));
+ /* we know that the DestSPI receiver doesn't need a destroy call */
+ }
+
+
+ /*
+ * SPI_scroll_cursor_move()
+ *
+ * Move in a scarollable cursor
+ */
+ void
+ SPI_scroll_cursor_move(Portal portal, FetchDirection direction, long count)
+ {
+ _SPI_cursor_operation(portal, direction, count, None_Receiver);
+ }
+
+
+
+ /*
* SPI_cursor_fetch()
*
* Fetch rows in a cursor
***************
*** 995,1001 ****
void
SPI_cursor_fetch(Portal portal, bool forward, long count)
{
! _SPI_cursor_operation(portal, forward, count,
CreateDestReceiver(DestSPI, NULL));
/* we know that the DestSPI receiver doesn't need a destroy call */
}
--- 1062,1068 ----
void
SPI_cursor_fetch(Portal portal, bool forward, long count)
{
! _SPI_cursor_operation(portal, forward ? FETCH_FORWARD : FETCH_BACKWARD, count,
CreateDestReceiver(DestSPI, NULL));
/* we know that the DestSPI receiver doesn't need a destroy call */
}
***************
*** 1009,1015 ****
void
SPI_cursor_move(Portal portal, bool forward, long count)
{
! _SPI_cursor_operation(portal, forward, count, None_Receiver);
}
--- 1076,1082 ----
void
SPI_cursor_move(Portal portal, bool forward, long count)
{
! _SPI_cursor_operation(portal, forward ? FETCH_FORWARD : FETCH_BACKWARD, count, None_Receiver);
}
***************
*** 1260,1266 ****
* Query and plan lists are stored into *plan.
*/
static void
! _SPI_prepare_plan(const char *src, _SPI_plan *plan)
{
List *raw_parsetree_list;
List *query_list_list;
--- 1327,1333 ----
* Query and plan lists are stored into *plan.
*/
static void
! _SPI_prepare_plan(const char *src, _SPI_plan *plan, bool isCursor, int options)
{
List *raw_parsetree_list;
List *query_list_list;
***************
*** 1311,1317 ****
query_list_list = lappend(query_list_list, query_list);
plan_list = list_concat(plan_list,
! pg_plan_queries(query_list, NULL, false));
}
plan->qtlist = query_list_list;
--- 1378,1384 ----
query_list_list = lappend(query_list_list, query_list);
plan_list = list_concat(plan_list,
! pg_plan_queries(query_list, NULL, false, isCursor, options));
}
plan->qtlist = query_list_list;
***************
*** 1663,1669 ****
* Do a FETCH or MOVE in a cursor
*/
static void
! _SPI_cursor_operation(Portal portal, bool forward, long count,
DestReceiver *dest)
{
long nfetched;
--- 1730,1736 ----
* Do a FETCH or MOVE in a cursor
*/
static void
! _SPI_cursor_operation(Portal portal, FetchDirection direction, long count,
DestReceiver *dest)
{
long nfetched;
***************
*** 1684,1690 ****
/* Run the cursor */
nfetched = PortalRunFetch(portal,
! forward ? FETCH_FORWARD : FETCH_BACKWARD,
count,
dest);
--- 1751,1757 ----
/* Run the cursor */
nfetched = PortalRunFetch(portal,
! direction,
count,
dest);
*** ./src/backend/tcop/postgres.c.orig 2007-01-23 22:31:50.000000000 +0100
--- ./src/backend/tcop/postgres.c 2007-01-26 19:08:15.000000000 +0100
***************
*** 647,653 ****
/* Generate a plan for a single already-rewritten query. */
Plan *
! pg_plan_query(Query *querytree, ParamListInfo boundParams)
{
Plan *plan;
--- 647,653 ----
/* Generate a plan for a single already-rewritten query. */
Plan *
! pg_plan_query(Query *querytree, ParamListInfo boundParams, bool isCursor, int options)
{
Plan *plan;
***************
*** 659,665 ****
ResetUsage();
/* call the optimizer */
! plan = planner(querytree, false, 0, boundParams);
if (log_planner_stats)
ShowUsage("PLANNER STATISTICS");
--- 659,665 ----
ResetUsage();
/* call the optimizer */
! plan = planner(querytree, isCursor, options, boundParams);
if (log_planner_stats)
ShowUsage("PLANNER STATISTICS");
***************
*** 705,711 ****
*/
List *
pg_plan_queries(List *querytrees, ParamListInfo boundParams,
! bool needSnapshot)
{
List *plan_list = NIL;
ListCell *query_list;
--- 705,711 ----
*/
List *
pg_plan_queries(List *querytrees, ParamListInfo boundParams,
! bool needSnapshot, bool isCursor, int options)
{
List *plan_list = NIL;
ListCell *query_list;
***************
*** 727,733 ****
ActiveSnapshot = CopySnapshot(GetTransactionSnapshot());
needSnapshot = false;
}
! plan = pg_plan_query(query, boundParams);
}
plan_list = lappend(plan_list, plan);
--- 727,733 ----
ActiveSnapshot = CopySnapshot(GetTransactionSnapshot());
needSnapshot = false;
}
! plan = pg_plan_query(query, boundParams, isCursor, options);
}
plan_list = lappend(plan_list, plan);
***************
*** 875,881 ****
querytree_list = pg_analyze_and_rewrite(parsetree, query_string,
NULL, 0);
! plantree_list = pg_plan_queries(querytree_list, NULL, true);
/* If we got a cancel signal in analysis or planning, quit */
CHECK_FOR_INTERRUPTS();
--- 875,881 ----
querytree_list = pg_analyze_and_rewrite(parsetree, query_string,
NULL, 0);
! plantree_list = pg_plan_queries(querytree_list, NULL, true, false, 0);
/* If we got a cancel signal in analysis or planning, quit */
CHECK_FOR_INTERRUPTS();
***************
*** 1191,1197 ****
if (!is_named && numParams > 0)
plantree_list = NIL;
else
! plantree_list = pg_plan_queries(querytree_list, NULL, true);
}
else
{
--- 1191,1197 ----
if (!is_named && numParams > 0)
plantree_list = NIL;
else
! plantree_list = pg_plan_queries(querytree_list, NULL, true, false, 0);
}
else
{
***************
*** 1575,1581 ****
qContext = PortalGetHeapMemory(portal);
oldContext = MemoryContextSwitchTo(qContext);
query_list = copyObject(pstmt->query_list);
! plan_list = pg_plan_queries(query_list, params, true);
MemoryContextSwitchTo(oldContext);
}
else
--- 1575,1581 ----
qContext = PortalGetHeapMemory(portal);
oldContext = MemoryContextSwitchTo(qContext);
query_list = copyObject(pstmt->query_list);
! plan_list = pg_plan_queries(query_list, params, true, false, 0);
MemoryContextSwitchTo(oldContext);
}
else
*** ./src/include/executor/spi.h.orig 2007-01-14 09:24:00.000000000 +0100
--- ./src/include/executor/spi.h 2007-01-23 22:44:30.000000000 +0100
***************
*** 25,30 ****
--- 25,31 ----
#include "catalog/pg_proc.h"
#include "catalog/pg_type.h"
#include "executor/executor.h"
+ #include "nodes/parsenodes.h"
#include "nodes/execnodes.h"
#include "nodes/params.h"
#include "nodes/plannodes.h"
***************
*** 97,102 ****
--- 98,104 ----
Snapshot crosscheck_snapshot,
bool read_only, long tcount);
extern void *SPI_prepare(const char *src, int nargs, Oid *argtypes);
+ extern void * SPI_prepare_cursor(const char *src, int nargs, Oid *argtypes, int options);
extern void *SPI_saveplan(void *plan);
extern int SPI_freeplan(void *plan);
***************
*** 128,133 ****
--- 130,137 ----
extern Portal SPI_cursor_find(const char *name);
extern void SPI_cursor_fetch(Portal portal, bool forward, long count);
extern void SPI_cursor_move(Portal portal, bool forward, long count);
+ extern void SPI_scroll_cursor_fetch(Portal, FetchDirection direction, long count);
+ extern void SPI_scroll_cursor_move(Portal, FetchDirection direction, long count);
extern void SPI_cursor_close(Portal portal);
extern void AtEOXact_SPI(bool isCommit);
*** ./src/include/tcop/tcopprot.h.orig 2007-01-23 22:30:42.000000000 +0100
--- ./src/include/tcop/tcopprot.h 2007-01-26 19:01:53.000000000 +0100
***************
*** 50,58 ****
extern List *pg_parse_query(const char *query_string);
extern List *pg_analyze_and_rewrite(Node *parsetree, const char *query_string,
Oid *paramTypes, int numParams);
! extern Plan *pg_plan_query(Query *querytree, ParamListInfo boundParams);
extern List *pg_plan_queries(List *querytrees, ParamListInfo boundParams,
! bool needSnapshot);
extern bool assign_max_stack_depth(int newval, bool doit, GucSource source);
#endif /* BOOTSTRAP_INCLUDE */
--- 50,58 ----
extern List *pg_parse_query(const char *query_string);
extern List *pg_analyze_and_rewrite(Node *parsetree, const char *query_string,
Oid *paramTypes, int numParams);
! extern Plan *pg_plan_query(Query *querytree, ParamListInfo boundParams, bool isCursor, int options);
extern List *pg_plan_queries(List *querytrees, ParamListInfo boundParams,
! bool needSnapshot, bool isCursor, int options);
extern bool assign_max_stack_depth(int newval, bool doit, GucSource source);
#endif /* BOOTSTRAP_INCLUDE */
*** ./src/pl/plpgsql/src/gram.y.orig 2007-01-26 12:03:05.000000000 +0100
--- ./src/pl/plpgsql/src/gram.y 2007-01-26 21:40:57.000000000 +0100
***************
*** 29,35 ****
static PLpgSQL_expr *read_sql_stmt(const char *sqlstart);
static PLpgSQL_type *read_datatype(int tok);
static PLpgSQL_stmt *make_execsql_stmt(const char *sqlstart, int lineno);
! static PLpgSQL_stmt *make_fetch_stmt(int lineno, int curvar);
static PLpgSQL_stmt *make_return_stmt(int lineno);
static PLpgSQL_stmt *make_return_next_stmt(int lineno);
static void check_assignable(PLpgSQL_datum *datum);
--- 29,35 ----
static PLpgSQL_expr *read_sql_stmt(const char *sqlstart);
static PLpgSQL_type *read_datatype(int tok);
static PLpgSQL_stmt *make_execsql_stmt(const char *sqlstart, int lineno);
! static PLpgSQL_stmt_fetch *make_fetch_stmt(void);
static PLpgSQL_stmt *make_return_stmt(int lineno);
static PLpgSQL_stmt *make_return_next_stmt(int lineno);
static void check_assignable(PLpgSQL_datum *datum);
***************
*** 77,82 ****
--- 77,87 ----
char *end_label;
List *stmts;
} loop_body;
+ struct
+ {
+ int dno;
+ bool scrollable;
+ } cursor_var;
List *list;
PLpgSQL_type *dtype;
PLpgSQL_datum *scalar; /* a VAR, RECFIELD, or TRIGARG */
***************
*** 92,97 ****
--- 97,103 ----
PLpgSQL_exception_block *exception_block;
PLpgSQL_nsitem *nsitem;
PLpgSQL_diag_item *diagitem;
+ PLpgSQL_stmt_fetch *fetch;
}
%type <declhdr> decl_sect
***************
*** 109,115 ****
%type <expr> expr_until_then expr_until_loop
%type <expr> opt_exitcond
! %type <ival> assign_var cursor_variable
%type <var> cursor_varptr
%type <variable> decl_cursor_arg
%type <forvariable> for_variable
--- 115,122 ----
%type <expr> expr_until_then expr_until_loop
%type <expr> opt_exitcond
! %type <ival> assign_var
! %type <cursor_var> cursor_variable
%type <var> cursor_varptr
%type <variable> decl_cursor_arg
%type <forvariable> for_variable
***************
*** 139,144 ****
--- 146,154 ----
%type <diagitem> getdiag_list_item
%type <ival> getdiag_kind getdiag_target
+ %type <boolean> opt_scrollable
+ %type <fetch> opt_fetch_direction
+
%type <ival> lno
/*
***************
*** 176,181 ****
--- 186,192 ----
%token K_LOG
%token K_LOOP
%token K_NEXT
+ %token K_NOSCROLL
%token K_NOT
%token K_NOTICE
%token K_NULL
***************
*** 188,193 ****
--- 199,205 ----
%token K_RESULT_OID
%token K_RETURN
%token K_REVERSE
+ %token K_SCROLL
%token K_STRICT
%token K_THEN
%token K_TO
***************
*** 356,362 ****
{
plpgsql_ns_rename($2, $4);
}
! | decl_varname K_CURSOR
{ plpgsql_ns_push(NULL); }
decl_cursor_args decl_is_from decl_cursor_query
{
--- 368,374 ----
{
plpgsql_ns_rename($2, $4);
}
! | decl_varname opt_scrollable K_CURSOR
{ plpgsql_ns_push(NULL); }
decl_cursor_args decl_is_from decl_cursor_query
{
***************
*** 377,382 ****
--- 389,396 ----
curname_def = palloc0(sizeof(PLpgSQL_expr));
+ new->options = $2 ? CURSOR_OPT_SCROLL : 0;
+
curname_def->dtype = PLPGSQL_DTYPE_EXPR;
strcpy(buf, "SELECT ");
cp1 = new->refname;
***************
*** 398,411 ****
curname_def->query = pstrdup(buf);
new->default_val = curname_def;
! new->cursor_explicit_expr = $6;
! if ($4 == NULL)
new->cursor_explicit_argrow = -1;
else
! new->cursor_explicit_argrow = $4->rowno;
}
;
decl_cursor_query :
{
PLpgSQL_expr *query;
--- 412,440 ----
curname_def->query = pstrdup(buf);
new->default_val = curname_def;
! new->cursor_explicit_expr = $7;
! if ($5 == NULL)
new->cursor_explicit_argrow = -1;
else
! new->cursor_explicit_argrow = $5->rowno;
}
;
+ opt_scrollable :
+ {
+ $$ = false;
+ }
+ | K_NOSCROLL
+ {
+ $$ = false;
+ }
+ | K_SCROLL
+ {
+ $$ = true;
+ }
+ ;
+
+
decl_cursor_query :
{
PLpgSQL_expr *query;
***************
*** 1429,1437 ****
}
;
! stmt_fetch : K_FETCH lno cursor_variable K_INTO
{
! $$ = make_fetch_stmt($2, $3);
}
;
--- 1458,1498 ----
}
;
! stmt_fetch : K_FETCH lno opt_fetch_direction cursor_variable K_INTO
{
! PLpgSQL_rec *rec;
! PLpgSQL_row *row;
! PLpgSQL_stmt_fetch *fetch;
!
! fetch = $3;
!
! /* We have already parsed everything through the INTO keyword */
! read_into_target(&rec, &row, NULL);
!
!
! if (yylex() != ';')
! yyerror("syntax error");
!
! fetch->lineno = $2;
! fetch->rec = rec;
! fetch->row = row;
! fetch->curvar = $4.dno;
!
! if (!fetch->expr)
! if ((fetch->direction != FETCH_FORWARD || fetch->how_many < 0) && !$4.scrollable)
! ereport(ERROR,
! (errcode(ERRCODE_INVALID_CURSOR_STATE),
! errmsg("cursor can scan only forward"),
! errhint("Declare cursor with SCROLL option to enable scrollable scan.")));
!
! $$ = (PLpgSQL_stmt *)fetch;
! }
! ;
!
!
! opt_fetch_direction :
! {
! $$ = make_fetch_stmt();
}
;
***************
*** 1442,1448 ****
new = palloc(sizeof(PLpgSQL_stmt_close));
new->cmd_type = PLPGSQL_STMT_CLOSE;
new->lineno = $2;
! new->curvar = $3;
$$ = (PLpgSQL_stmt *)new;
}
--- 1503,1509 ----
new = palloc(sizeof(PLpgSQL_stmt_close));
new->cmd_type = PLPGSQL_STMT_CLOSE;
new->lineno = $2;
! new->curvar = $3.dno;
$$ = (PLpgSQL_stmt *)new;
}
***************
*** 1485,1491 ****
errmsg("\"%s\" must be of type refcursor",
((PLpgSQL_var *) yylval.scalar)->refname)));
}
! $$ = yylval.scalar->dno;
}
;
--- 1546,1554 ----
errmsg("\"%s\" must be of type refcursor",
((PLpgSQL_var *) yylval.scalar)->refname)));
}
!
! $$.dno = yylval.scalar->dno;
! $$.scrollable = ((PLpgSQL_var *) yylval.scalar)->options & CURSOR_OPT_SCROLL;
}
;
***************
*** 1972,1999 ****
}
! static PLpgSQL_stmt *
! make_fetch_stmt(int lineno, int curvar)
{
int tok;
! PLpgSQL_rec *rec;
! PLpgSQL_row *row;
PLpgSQL_stmt_fetch *fetch;
- /* We have already parsed everything through the INTO keyword */
- read_into_target(&rec, &row, NULL);
- tok = yylex();
- if (tok != ';')
- yyerror("syntax error");
-
fetch = palloc0(sizeof(PLpgSQL_stmt_fetch));
fetch->cmd_type = PLPGSQL_STMT_FETCH;
- fetch->lineno = lineno;
- fetch->rec = rec;
- fetch->row = row;
- fetch->curvar = curvar;
! return (PLpgSQL_stmt *) fetch;
}
--- 2035,2109 ----
}
! static PLpgSQL_stmt_fetch *
! make_fetch_stmt()
{
int tok;
! char *token = NULL;
! bool check_FROM = true;
PLpgSQL_stmt_fetch *fetch;
fetch = palloc0(sizeof(PLpgSQL_stmt_fetch));
fetch->cmd_type = PLPGSQL_STMT_FETCH;
! tok = yylex();
!
! if (tok != K_NEXT && tok != K_FROM)
! plpgsql_convert_ident(yytext, &token, 1);
!
! fetch->direction = FETCH_FORWARD;
! fetch->how_many = 1;
!
! /*
! * Tokenized words have to be tested first,
! * because token is NULL
! */
! if (tok == K_FROM)
! {
! /* read optional FROM */
! check_FROM = false;
! }
! else if (tok == K_NEXT)
! {
! /* use default */
! }
! else if (strcmp(token, "prior") == 0)
! {
! fetch->direction = FETCH_BACKWARD;
! }
! else if (strcmp(token, "first") == 0)
! {
! fetch->direction = FETCH_ABSOLUTE;
! }
! else if (strcmp(token, "last") == 0)
! {
! fetch->direction = FETCH_ABSOLUTE;
! fetch->how_many = -1;
! }
! else if (strcmp(token, "absolute") == 0)
! {
! fetch->direction = FETCH_ABSOLUTE;
! fetch->expr = plpgsql_read_expression(K_FROM, "FROM");
! check_FROM = false;
! }
! else if (strcmp(token, "relative") == 0)
! {
! fetch->direction = FETCH_RELATIVE;
! fetch->expr = plpgsql_read_expression(K_FROM, "FROM");
! check_FROM = false;
! }
! else
! {
! /* propably cursor_variable */
! check_FROM = false;
! plpgsql_push_back_token(tok);
! }
!
! /* check FROM keyword after direction's specification */
! if (check_FROM && yylex() != K_FROM)
! yyerror("expected \"FROM\"");
!
! return fetch;
}
*** ./src/pl/plpgsql/src/pl_exec.c.orig 2007-01-26 14:21:25.000000000 +0100
--- ./src/pl/plpgsql/src/pl_exec.c 2007-01-26 20:19:51.000000000 +0100
***************
*** 102,108 ****
static void exec_eval_cleanup(PLpgSQL_execstate *estate);
static void exec_prepare_plan(PLpgSQL_execstate *estate,
! PLpgSQL_expr *expr);
static bool exec_simple_check_node(Node *node);
static void exec_simple_check_plan(PLpgSQL_expr *expr);
static Datum exec_eval_simple_expr(PLpgSQL_execstate *estate,
--- 102,108 ----
static void exec_eval_cleanup(PLpgSQL_execstate *estate);
static void exec_prepare_plan(PLpgSQL_execstate *estate,
! PLpgSQL_expr *expr, int options);
static bool exec_simple_check_node(Node *node);
static void exec_simple_check_plan(PLpgSQL_expr *expr);
static Datum exec_eval_simple_expr(PLpgSQL_execstate *estate,
***************
*** 2201,2207 ****
*/
static void
exec_prepare_plan(PLpgSQL_execstate *estate,
! PLpgSQL_expr *expr)
{
int i;
_SPI_plan *spi_plan;
--- 2201,2207 ----
*/
static void
exec_prepare_plan(PLpgSQL_execstate *estate,
! PLpgSQL_expr *expr, int options)
{
int i;
_SPI_plan *spi_plan;
***************
*** 2225,2233 ****
}
/*
! * Generate and save the plan
*/
! plan = SPI_prepare(expr->query, expr->nparams, argtypes);
if (plan == NULL)
{
/* Some SPI errors deserve specific error messages */
--- 2225,2237 ----
}
/*
! * Generate and save the plan, if any option is know, use it
*/
! if (options)
! plan = SPI_prepare_cursor(expr->query, expr->nparams, argtypes, options);
! else
! plan = SPI_prepare(expr->query, expr->nparams, argtypes);
!
if (plan == NULL)
{
/* Some SPI errors deserve specific error messages */
***************
*** 2287,2293 ****
_SPI_plan *spi_plan;
ListCell *l;
! exec_prepare_plan(estate, expr);
stmt->mod_stmt = false;
spi_plan = (_SPI_plan *) expr->plan;
foreach(l, spi_plan->qtlist)
--- 2291,2297 ----
_SPI_plan *spi_plan;
ListCell *l;
! exec_prepare_plan(estate, expr, 0);
stmt->mod_stmt = false;
spi_plan = (_SPI_plan *) expr->plan;
foreach(l, spi_plan->qtlist)
***************
*** 2858,2864 ****
*/
query = stmt->query;
if (query->plan == NULL)
! exec_prepare_plan(estate, query);
}
else if (stmt->dynquery != NULL)
{
--- 2862,2868 ----
*/
query = stmt->query;
if (query->plan == NULL)
! exec_prepare_plan(estate, query, 0);
}
else if (stmt->dynquery != NULL)
{
***************
*** 2960,2967 ****
}
query = curvar->cursor_explicit_expr;
if (query->plan == NULL)
! exec_prepare_plan(estate, query);
}
/* ----------
--- 2964,2972 ----
}
query = curvar->cursor_explicit_expr;
+
if (query->plan == NULL)
! exec_prepare_plan(estate, query, curvar->options);
}
/* ----------
***************
*** 3029,3034 ****
--- 3034,3040 ----
Portal portal;
char *curname;
int n;
+ int how_many = stmt->how_many;
/* ----------
* Get the portal of the cursor by name
***************
*** 3048,3053 ****
--- 3054,3082 ----
errmsg("cursor \"%s\" does not exist", curname)));
pfree(curname);
+ /* calculate position for FETCH_RELATIVE or FETCH_ABSOLUTE */
+ if (stmt->expr)
+ {
+ bool isnull;
+
+ how_many = exec_eval_integer(estate, stmt->expr, &isnull);
+
+ if (isnull)
+ ereport(ERROR,
+ (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+ errmsg("relative or absolute cursor position is NULL")));
+
+ exec_eval_cleanup(estate);
+ }
+
+ /* check if we neeed scrollable corsor */
+ if (!(curvar->options & CURSOR_OPT_SCROLL))
+ if (stmt->direction != FETCH_FORWARD && how_many < 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_CURSOR_STATE),
+ errmsg("cursor can scan only forward"),
+ errhint("Declare cursor with SCROLL option to enable scrollable scan.")));
+
/* ----------
* Determine if we fetch into a record or a row
* ----------
***************
*** 3063,3069 ****
* Fetch 1 tuple from the cursor
* ----------
*/
! SPI_cursor_fetch(portal, true, 1);
tuptab = SPI_tuptable;
n = SPI_processed;
--- 3092,3098 ----
* Fetch 1 tuple from the cursor
* ----------
*/
! SPI_scroll_cursor_fetch(portal, stmt->direction, how_many);
tuptab = SPI_tuptable;
n = SPI_processed;
***************
*** 3776,3782 ****
* If not already done create a plan for this expression
*/
if (expr->plan == NULL)
! exec_prepare_plan(estate, expr);
/*
* If this is a simple expression, bypass SPI and use the executor
--- 3805,3811 ----
* If not already done create a plan for this expression
*/
if (expr->plan == NULL)
! exec_prepare_plan(estate, expr, 0);
/*
* If this is a simple expression, bypass SPI and use the executor
***************
*** 3840,3846 ****
* On the first call for this expression generate the plan
*/
if (expr->plan == NULL)
! exec_prepare_plan(estate, expr);
/*
* Now build up the values and nulls arguments for SPI_execute_plan()
--- 3869,3875 ----
* On the first call for this expression generate the plan
*/
if (expr->plan == NULL)
! exec_prepare_plan(estate, expr, 0);
/*
* Now build up the values and nulls arguments for SPI_execute_plan()
*** ./src/pl/plpgsql/src/plpgsql.h.orig 2007-01-26 12:14:07.000000000 +0100
--- ./src/pl/plpgsql/src/plpgsql.h 2007-01-26 18:48:18.000000000 +0100
***************
*** 204,209 ****
--- 204,210 ----
PLpgSQL_expr *default_val;
PLpgSQL_expr *cursor_explicit_expr;
int cursor_explicit_argrow;
+ int options;
Datum value;
bool isnull;
***************
*** 447,452 ****
--- 448,456 ----
PLpgSQL_rec *rec;
PLpgSQL_row *row;
int curvar;
+ int direction;
+ int how_many;
+ PLpgSQL_expr *expr;
} PLpgSQL_stmt_fetch;
*** ./src/pl/plpgsql/src/scan.l.orig 2007-01-26 12:02:48.000000000 +0100
--- ./src/pl/plpgsql/src/scan.l 2007-01-26 14:14:54.000000000 +0100
***************
*** 155,160 ****
--- 155,161 ----
return { return K_RETURN; }
reverse { return K_REVERSE; }
row_count { return K_ROW_COUNT; }
+ scroll { return K_SCROLL; }
strict { return K_STRICT; }
then { return K_THEN; }
to { return K_TO; }
***************
*** 174,179 ****
--- 175,182 ----
* in the pl_comp.c subroutines will point to the right place.
* ----------
*/
+ NO{space}*SCROLL {
+ return K_NOSCROLL; }
{identifier} {
plpgsql_error_lineno = plpgsql_scanner_lineno();
return plpgsql_parse_word(yytext); }
*** ./src/test/regress/expected/plpgsql.out.orig 2007-01-26 20:25:10.000000000 +0100
--- ./src/test/regress/expected/plpgsql.out 2007-01-26 20:23:16.000000000 +0100
***************
*** 2934,2936 ****
--- 2934,2995 ----
ERROR: query returned more than one row
CONTEXT: PL/pgSQL function "footest" line 4 at execute statement
drop function footest();
+ -- scrollable cursor support
+ --shoud fail, early detect scrollable cursor
+ create or replace function sc_test(int) returns void as $$
+ declare
+ x integer;
+ y integer;
+ c no scroll cursor(w int) for
+ select a.a, b.b
+ from generate_series(1,2) a(a), generate_series(1,10) b(b)
+ where a = w;
+ begin
+ open c($1);
+ fetch last from c into x, y;
+ while found loop
+ raise notice '% %', x, y;
+ fetch prior from c into x, y;
+ end loop;
+ close c;
+ end;
+ $$ language plpgsql;
+ ERROR: cursor can scan only forward
+ HINT: Declare cursor with SCROLL option to enable scrollable scan.
+ CONTEXT: compile of PL/pgSQL function "sc_test" near line 10
+ --shoud work, early detect used scrollable cursor
+ create or replace function sc_test(int) returns void as $$
+ declare
+ x integer;
+ y integer;
+ c scroll cursor(w int) for
+ select a.a, b.b
+ from generate_series(1,2) a(a), generate_series(1,10) b(b)
+ where a = w;
+ begin
+ open c($1);
+ fetch last from c into x, y;
+ while found loop
+ raise notice '% %', x, y;
+ fetch prior from c into x, y;
+ end loop;
+ close c;
+ end;
+ $$ language plpgsql;
+ select sc_test(1);
+ NOTICE: 1 10
+ NOTICE: 1 9
+ NOTICE: 1 8
+ NOTICE: 1 7
+ NOTICE: 1 6
+ NOTICE: 1 5
+ NOTICE: 1 4
+ NOTICE: 1 3
+ NOTICE: 1 2
+ NOTICE: 1 1
+ sc_test
+ ---------
+
+ (1 row)
+
+ drop function sc_test(int);
*** ./src/test/regress/sql/plpgsql.sql.orig 2007-01-26 16:00:24.000000000 +0100
--- ./src/test/regress/sql/plpgsql.sql 2007-01-26 20:19:55.000000000 +0100
***************
*** 2440,2442 ****
--- 2440,2488 ----
select footest();
drop function footest();
+
+ -- scrollable cursor support
+
+ --shoud fail, early detect scrollable cursor
+ create or replace function sc_test(int) returns void as $$
+ declare
+ x integer;
+ y integer;
+ c no scroll cursor(w int) for
+ select a.a, b.b
+ from generate_series(1,2) a(a), generate_series(1,10) b(b)
+ where a = w;
+ begin
+ open c($1);
+ fetch last from c into x, y;
+ while found loop
+ raise notice '% %', x, y;
+ fetch prior from c into x, y;
+ end loop;
+ close c;
+ end;
+ $$ language plpgsql;
+
+ --shoud work, early detect used scrollable cursor
+ create or replace function sc_test(int) returns void as $$
+ declare
+ x integer;
+ y integer;
+ c scroll cursor(w int) for
+ select a.a, b.b
+ from generate_series(1,2) a(a), generate_series(1,10) b(b)
+ where a = w;
+ begin
+ open c($1);
+ fetch last from c into x, y;
+ while found loop
+ raise notice '% %', x, y;
+ fetch prior from c into x, y;
+ end loop;
+ close c;
+ end;
+ $$ language plpgsql;
+
+ select sc_test(1);
+
+ drop function sc_test(int);
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
