This is the most recent email I have on this. Was the scrollable patch
applied? If not, would you resubmit?
Advertising
I resubmit scrollable cursor patch
Regards
Pavel Stehule
_________________________________________________________________
Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci.
http://messenger.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
