On 2020-10-09 20:46, Andres Freund wrote:
Is there really a good reason for forcing the client to issue
NextResult, Describe, Execute for each of the dynamic result sets? It's
not like there's really a case for allowing the clients to skip them,
right? Why aren't we sending something more like
S: CommandPartiallyComplete
S: RowDescription
S: DataRow...
S: CommandPartiallyComplete
S: RowDescription
S: DataRow...
...
S: CommandComplete
C: Sync
I want to post my current patch, to keep this discussion moving. There
are still a number of pieces to pull together, but what I have is a
self-contained functioning prototype.
The interesting thing about the above message sequence is that the
"CommandPartiallyComplete" isn't actually necessary. Since an Execute
message normally does not issue a RowDescription response, the
appearance of one is already enough to mark the beginning of a new
result set. Moreover, libpq already handles this correctly, so we
wouldn't need to change it at all.
We might still want to add a new protocol message, for clarity perhaps,
and that would probably only be a few lines of code on either side, but
that would only serve for additional error checking and wouldn't
actually be needed to identify what's going on.
What else we need:
- Think about what should happen if the Execute message specifies a row
count, and what should happen during subsequent Execute messages on the
same portal. I suspect that there isn't a particularly elegant answer,
but we need to pick some behavior.
- Some way for psql to display multiple result sets. Proposals have been
made in [0] and [1]. (You need either patch or one like it for the
regression tests in this patch to pass.)
- Session-level default result formats setting, proposed in [2]. Not
strictly necessary, but would be most sensible to coordinate these two.
- We don't have a way to test the extended query protocol. I have
attached my test program, but we might want to think about something
more permanent. Proposals for this have already been made in [3].
- Right now, this only supports returning dynamic result sets from a
top-level CALL. Specifications for passing dynamic result sets from one
procedure to a calling procedure exist in the SQL standard and could be
added later.
(All the SQL additions in this patch are per SQL standard. DB2 appears
to be the closest existing implementation.)
[0]:
https://www.postgresql.org/message-id/flat/4580ff7b-d610-eaeb-e06f-4d686896b93b%402ndquadrant.com
[1]: https://commitfest.postgresql.org/29/2096/
[2]: https://commitfest.postgresql.org/31/2812/
[3]:
https://www.postgresql.org/message-id/4f733cca-5e07-e167-8b38-05b5c9066d04%402ndQuadrant.com
--
Peter Eisentraut
2ndQuadrant, an EDB company
https://www.2ndquadrant.com/
From 03e7b009ca54f686f0798c764ffc802e70f64076 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Wed, 30 Dec 2020 14:30:33 +0100
Subject: [PATCH v1] Dynamic result sets from procedures
Declaring a cursor WITH RETURN in a procedure makes the cursor's data be
returned as a result of the CALL invocation. The procedure needs to
be declared with the DYNAMIC RESULT SETS attribute.
---
doc/src/sgml/catalogs.sgml | 10 +++
doc/src/sgml/information_schema.sgml | 3 +-
doc/src/sgml/protocol.sgml | 19 +++++
doc/src/sgml/ref/alter_procedure.sgml | 12 +++
doc/src/sgml/ref/create_procedure.sgml | 14 ++++
doc/src/sgml/ref/declare.sgml | 34 ++++++++-
src/backend/catalog/information_schema.sql | 2 +-
src/backend/catalog/pg_aggregate.c | 3 +-
src/backend/catalog/pg_proc.c | 4 +-
src/backend/catalog/sql_features.txt | 2 +-
src/backend/commands/functioncmds.c | 75 +++++++++++++++++--
src/backend/commands/portalcmds.c | 23 ++++++
src/backend/commands/typecmds.c | 12 ++-
src/backend/parser/gram.y | 20 ++++-
src/backend/tcop/postgres.c | 62 ++++++++++++++-
src/backend/tcop/pquery.c | 6 ++
src/backend/utils/errcodes.txt | 1 +
src/backend/utils/mmgr/portalmem.c | 48 ++++++++++++
src/bin/pg_dump/pg_dump.c | 16 +++-
src/include/catalog/pg_proc.h | 6 +-
src/include/commands/defrem.h | 2 +
src/include/nodes/parsenodes.h | 9 ++-
src/include/parser/kwlist.h | 3 +
src/include/utils/portal.h | 14 ++++
src/interfaces/libpq/fe-protocol3.c | 6 +-
.../regress/expected/create_procedure.out | 41 +++++++++-
src/test/regress/sql/create_procedure.sql | 30 +++++++-
27 files changed, 443 insertions(+), 34 deletions(-)
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 3a2266526c..cde88b54e2 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -5840,6 +5840,16 @@ <title><structname>pg_proc</structname> Columns</title>
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>prodynres</structfield> <type>int4</type>
+ </para>
+ <para>
+ For procedures, this records the maximum number of dynamic result sets
+ the procedure may create. Otherwise zero.
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>pronargs</structfield> <type>int2</type>
diff --git a/doc/src/sgml/information_schema.sgml
b/doc/src/sgml/information_schema.sgml
index 36ec17a4c6..0eeae45da4 100644
--- a/doc/src/sgml/information_schema.sgml
+++ b/doc/src/sgml/information_schema.sgml
@@ -5441,7 +5441,8 @@ <title><structname>routines</structname> Columns</title>
<structfield>max_dynamic_result_sets</structfield>
<type>cardinal_number</type>
</para>
<para>
- Applies to a feature not available in
<productname>PostgreSQL</productname>
+ For a procedure, the maximum number of dynamic result sets. Otherwise
+ zero.
</para></entry>
</row>
diff --git a/doc/src/sgml/protocol.sgml b/doc/src/sgml/protocol.sgml
index 4899bacda7..62f17d4fb0 100644
--- a/doc/src/sgml/protocol.sgml
+++ b/doc/src/sgml/protocol.sgml
@@ -959,6 +959,25 @@ <title>Extended Query</title>
an empty query string), ErrorResponse, or PortalSuspended.
</para>
+ <para>
+ Executing a portal may give rise to a <firstterm>dynamic result set
+ sequence</firstterm>. That means the command contained in the portal
+ created additional result sets beyond what it normally returns. (The
+ typical example is calling a stored procedure that creates dynamic result
+ sets.) Dynamic result sets are issues after whatever response the main
+ command issued. Each dynamic result set begins with a RowDescription
+ message followed by zero or more DataRow messages. (Since as explained
+ above an Execute message normally does not respond with a RowDescription,
+ the appearance of the first RowDescription marks the end of the primary
+ result set of the portal and the beginning of the first dynamic result
+ set.) The CommandComplete message that concludes the Execute message
+ response follows <emphasis>after</emphasis> all dynamic result sets. Note
+ that dynamic result sets cannot, by their nature, be decribed prior to the
+ execution of the portal. Multiple executions of the same prepared
+ statement could result in dynamic result sets with different row
+ descriptions being returned.
+ </para>
+
<para>
At completion of each series of extended-query messages, the frontend
should issue a Sync message. This parameterless message causes the
diff --git a/doc/src/sgml/ref/alter_procedure.sgml
b/doc/src/sgml/ref/alter_procedure.sgml
index 5c176fb5d8..441585e665 100644
--- a/doc/src/sgml/ref/alter_procedure.sgml
+++ b/doc/src/sgml/ref/alter_procedure.sgml
@@ -34,6 +34,7 @@
<phrase>where <replaceable class="parameter">action</replaceable> is one
of:</phrase>
+ DYNAMIC RESULT SETS <replaceable
class="parameter">dynamic_result_sets</replaceable>
[ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
SET <replaceable class="parameter">configuration_parameter</replaceable> {
TO | = } { <replaceable class="parameter">value</replaceable> | DEFAULT }
SET <replaceable class="parameter">configuration_parameter</replaceable>
FROM CURRENT
@@ -150,6 +151,17 @@ <title>Parameters</title>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>DYNAMIC RESULT SETS <replaceable
class="parameter">dynamic_result_sets</replaceable></literal></term>
+
+ <listitem>
+ <para>
+ Changes the dynamic result sets setting of the procedure. See <xref
+ linkend="sql-createprocedure"/> for more information.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal><optional> EXTERNAL </optional> SECURITY
INVOKER</literal></term>
<term><literal><optional> EXTERNAL </optional> SECURITY
DEFINER</literal></term>
diff --git a/doc/src/sgml/ref/create_procedure.sgml
b/doc/src/sgml/ref/create_procedure.sgml
index e258eca5ce..9b5090f229 100644
--- a/doc/src/sgml/ref/create_procedure.sgml
+++ b/doc/src/sgml/ref/create_procedure.sgml
@@ -24,6 +24,7 @@
CREATE [ OR REPLACE ] PROCEDURE
<replaceable class="parameter">name</replaceable> ( [ [ <replaceable
class="parameter">argmode</replaceable> ] [ <replaceable
class="parameter">argname</replaceable> ] <replaceable
class="parameter">argtype</replaceable> [ { DEFAULT | = } <replaceable
class="parameter">default_expr</replaceable> ] [, ...] ] )
{ LANGUAGE <replaceable class="parameter">lang_name</replaceable>
+ | DYNAMIC RESULT SETS <replaceable
class="parameter">dynamic_result_sets</replaceable>
| TRANSFORM { FOR TYPE <replaceable
class="parameter">type_name</replaceable> } [, ... ]
| [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
| SET <replaceable class="parameter">configuration_parameter</replaceable>
{ TO <replaceable class="parameter">value</replaceable> | = <replaceable
class="parameter">value</replaceable> | FROM CURRENT }
@@ -168,6 +169,19 @@ <title>Parameters</title>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>DYNAMIC RESULT SETS <replaceable
class="parameter">dynamic_result_sets</replaceable></literal></term>
+
+ <listitem>
+ <para>
+ Specifies how many dynamic result sets the procedure returns (see
+ <literal><link linkend="sql-declare">DECLARE</link> WITH
+ RETURN</literal>). The default is 0. If a procedure returns more
+ result sets than declared, a warning is raised.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>TRANSFORM { FOR TYPE <replaceable
class="parameter">type_name</replaceable> } [, ... ] }</literal></term>
diff --git a/doc/src/sgml/ref/declare.sgml b/doc/src/sgml/ref/declare.sgml
index 2152134635..dded159d18 100644
--- a/doc/src/sgml/ref/declare.sgml
+++ b/doc/src/sgml/ref/declare.sgml
@@ -27,7 +27,8 @@
<refsynopsisdiv>
<synopsis>
DECLARE <replaceable class="parameter">name</replaceable> [ BINARY ] [
INSENSITIVE ] [ [ NO ] SCROLL ]
- CURSOR [ { WITH | WITHOUT } HOLD ] FOR <replaceable
class="parameter">query</replaceable>
+ CURSOR [ { WITH | WITHOUT } HOLD ] [ { WITH | WITHOUT } RETURN ]
+ FOR <replaceable class="parameter">query</replaceable>
</synopsis>
</refsynopsisdiv>
@@ -120,6 +121,22 @@ <title>Parameters</title>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>WITH RETURN</literal></term>
+ <term><literal>WITHOUT RETURN</literal></term>
+ <listitem>
+ <para>
+ This option is only valid for cursors defined inside a procedure.
+ <literal>WITH RETURN</literal> specifies that the cursor's result rows
+ will be provided as a result set of the procedure invocation. To
+ accomplish that, the cursor must be left open at the end of the
+ procedure. If multiple <literal>WITH RETURN</literal> cursors are
+ declared, then their results will be returned in the order they were
+ created. <literal>WITHOUT RETURN</literal> is the default.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><replaceable class="parameter">query</replaceable></term>
<listitem>
@@ -313,6 +330,21 @@ <title>Examples</title>
See <xref linkend="sql-fetch"/> for more
examples of cursor usage.
</para>
+
+ <para>
+ This example shows how to return multiple result sets from a procedure:
+<programlisting>
+CREATE PROCEDURE test()
+LANGUAGE SQL
+AS $$
+DECLARE a CURSOR WITH RETURN FOR SELECT * FROM tbl1;
+DECLARE b CURSOR WITH RETURN FOR SELECT * FROM tbl2;
+$$;
+
+CALL test();
+</programlisting>
+ The results of the two cursors will be returned in order from this call.
+ </para>
</refsect1>
<refsect1>
diff --git a/src/backend/catalog/information_schema.sql
b/src/backend/catalog/information_schema.sql
index 5ab47e7743..216c1f3b1e 100644
--- a/src/backend/catalog/information_schema.sql
+++ b/src/backend/catalog/information_schema.sql
@@ -1494,7 +1494,7 @@ CREATE VIEW routines AS
CASE WHEN p.proisstrict THEN 'YES' ELSE 'NO' END END AS
yes_or_no) AS is_null_call,
CAST(null AS character_data) AS sql_path,
CAST('YES' AS yes_or_no) AS schema_level_routine,
- CAST(0 AS cardinal_number) AS max_dynamic_result_sets,
+ CAST(p.prodynres AS cardinal_number) AS max_dynamic_result_sets,
CAST(null AS yes_or_no) AS is_user_defined_cast,
CAST(null AS yes_or_no) AS is_implicitly_invocable,
CAST(CASE WHEN p.prosecdef THEN 'DEFINER' ELSE 'INVOKER' END AS
character_data) AS security_type,
diff --git a/src/backend/catalog/pg_aggregate.c
b/src/backend/catalog/pg_aggregate.c
index 7664bb6285..11ed5ebb31 100644
--- a/src/backend/catalog/pg_aggregate.c
+++ b/src/backend/catalog/pg_aggregate.c
@@ -639,7 +639,8 @@ AggregateCreate(const char *aggName,
PointerGetDatum(NULL),
/* proconfig */
InvalidOid, /* no
prosupport */
1, /* procost */
- 0); /* prorows */
+ 0, /* prorows */
+ 0); /* prodynres */
procOid = myself.objectId;
/*
diff --git a/src/backend/catalog/pg_proc.c b/src/backend/catalog/pg_proc.c
index 1dd9ecc063..017a6acca6 100644
--- a/src/backend/catalog/pg_proc.c
+++ b/src/backend/catalog/pg_proc.c
@@ -91,7 +91,8 @@ ProcedureCreate(const char *procedureName,
Datum proconfig,
Oid prosupport,
float4 procost,
- float4 prorows)
+ float4 prorows,
+ int dynres)
{
Oid retval;
int parameterCount;
@@ -310,6 +311,7 @@ ProcedureCreate(const char *procedureName,
values[Anum_pg_proc_proretset - 1] = BoolGetDatum(returnsSet);
values[Anum_pg_proc_provolatile - 1] = CharGetDatum(volatility);
values[Anum_pg_proc_proparallel - 1] = CharGetDatum(parallel);
+ values[Anum_pg_proc_prodynres - 1] = Int32GetDatum(dynres);
values[Anum_pg_proc_pronargs - 1] = UInt16GetDatum(parameterCount);
values[Anum_pg_proc_pronargdefaults - 1] =
UInt16GetDatum(list_length(parameterDefaults));
values[Anum_pg_proc_prorettype - 1] = ObjectIdGetDatum(returnType);
diff --git a/src/backend/catalog/sql_features.txt
b/src/backend/catalog/sql_features.txt
index caa971c435..3bce031fa6 100644
--- a/src/backend/catalog/sql_features.txt
+++ b/src/backend/catalog/sql_features.txt
@@ -484,7 +484,7 @@ T433 Multiargument GROUPING function
YES
T434 GROUP BY DISTINCT NO
T441 ABS and MOD functions YES
T461 Symmetric BETWEEN predicate YES
-T471 Result sets return value NO
+T471 Result sets return value NO partially
supported
T472 DESCRIBE CURSOR NO
T491 LATERAL derived table YES
T495 Combined data change and retrieval NO
different syntax
diff --git a/src/backend/commands/functioncmds.c
b/src/backend/commands/functioncmds.c
index c3ce480c8f..3ea4c5c04a 100644
--- a/src/backend/commands/functioncmds.c
+++ b/src/backend/commands/functioncmds.c
@@ -68,6 +68,7 @@
#include "utils/guc.h"
#include "utils/lsyscache.h"
#include "utils/memutils.h"
+#include "utils/portal.h"
#include "utils/rel.h"
#include "utils/syscache.h"
#include "utils/typcache.h"
@@ -478,7 +479,8 @@ compute_common_attribute(ParseState *pstate,
DefElem **cost_item,
DefElem **rows_item,
DefElem **support_item,
- DefElem **parallel_item)
+ DefElem **parallel_item,
+ DefElem **dynres_item)
{
if (strcmp(defel->defname, "volatility") == 0)
{
@@ -554,6 +556,15 @@ compute_common_attribute(ParseState *pstate,
*parallel_item = defel;
}
+ else if (strcmp(defel->defname, "dynamic_result_sets") == 0)
+ {
+ if (!is_procedure)
+ goto function_error;
+ if (*dynres_item)
+ goto duplicate_error;
+
+ *dynres_item = defel;
+ }
else
return false;
@@ -567,6 +578,13 @@ compute_common_attribute(ParseState *pstate,
parser_errposition(pstate, defel->location)));
return false; /* keep compiler quiet */
+function_error:
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_FUNCTION_DEFINITION),
+ errmsg("invalid attribute in function definition"),
+ parser_errposition(pstate, defel->location)));
+ return false;
+
procedure_error:
ereport(ERROR,
(errcode(ERRCODE_INVALID_FUNCTION_DEFINITION),
@@ -703,7 +721,8 @@ compute_function_attributes(ParseState *pstate,
float4 *procost,
float4 *prorows,
Oid *prosupport,
- char *parallel_p)
+ char *parallel_p,
+ int *dynres_p)
{
ListCell *option;
DefElem *as_item = NULL;
@@ -719,6 +738,7 @@ compute_function_attributes(ParseState *pstate,
DefElem *rows_item = NULL;
DefElem *support_item = NULL;
DefElem *parallel_item = NULL;
+ DefElem *dynres_item = NULL;
foreach(option, options)
{
@@ -776,7 +796,8 @@ compute_function_attributes(ParseState *pstate,
&cost_item,
&rows_item,
&support_item,
-
¶llel_item))
+
¶llel_item,
+
&dynres_item))
{
/* recognized common option */
continue;
@@ -842,6 +863,11 @@ compute_function_attributes(ParseState *pstate,
*prosupport = interpret_func_support(support_item);
if (parallel_item)
*parallel_p = interpret_func_parallel(parallel_item);
+ if (dynres_item)
+ {
+ *dynres_p = intVal(dynres_item->arg);
+ Assert(*dynres_p >= 0); /* enforced by parser */
+ }
}
@@ -950,6 +976,7 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt *stmt)
Form_pg_language languageStruct;
List *as_clause;
char parallel;
+ int dynres;
/* Convert list of names to a name and namespace */
namespaceId = QualifiedNameGetCreationNamespace(stmt->funcname,
@@ -972,6 +999,7 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt *stmt)
prorows = -1; /* indicates not set */
prosupport = InvalidOid;
parallel = PROPARALLEL_UNSAFE;
+ dynres = 0;
/* Extract non-default attributes from stmt->options list */
compute_function_attributes(pstate,
@@ -981,7 +1009,7 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt
*stmt)
&isWindowFunc,
&volatility,
&isStrict,
&security, &isLeakProof,
&proconfig,
&procost, &prorows,
- &prosupport,
¶llel);
+ &prosupport,
¶llel, &dynres);
/* Look up the language and validate permissions */
languageTuple = SearchSysCache1(LANGNAME, PointerGetDatum(language));
@@ -1170,7 +1198,8 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt
*stmt)
PointerGetDatum(proconfig),
prosupport,
procost,
- prorows);
+ prorows,
+ dynres);
}
/*
@@ -1245,6 +1274,7 @@ AlterFunction(ParseState *pstate, AlterFunctionStmt *stmt)
DefElem *rows_item = NULL;
DefElem *support_item = NULL;
DefElem *parallel_item = NULL;
+ DefElem *dynres_item = NULL;
ObjectAddress address;
rel = table_open(ProcedureRelationId, RowExclusiveLock);
@@ -1288,7 +1318,8 @@ AlterFunction(ParseState *pstate, AlterFunctionStmt *stmt)
&cost_item,
&rows_item,
&support_item,
-
¶llel_item) == false)
+
¶llel_item,
+
&dynres_item) == false)
elog(ERROR, "option \"%s\" not recognized",
defel->defname);
}
@@ -1384,6 +1415,8 @@ AlterFunction(ParseState *pstate, AlterFunctionStmt *stmt)
}
if (parallel_item)
procForm->proparallel = interpret_func_parallel(parallel_item);
+ if (dynres_item)
+ procForm->prodynres = intVal(dynres_item->arg);
/* Do the update */
CatalogTupleUpdate(rel, &tup->t_self, tup);
@@ -2026,6 +2059,24 @@ ExecuteDoStmt(DoStmt *stmt, bool atomic)
OidFunctionCall1(laninline, PointerGetDatum(codeblock));
}
+static List *procedure_stack;
+
+Oid
+CurrentProcedure(void)
+{
+ if (!procedure_stack)
+ return InvalidOid;
+ else
+ return llast_oid(procedure_stack);
+}
+
+void
+ProcedureCallsCleanup(void)
+{
+ list_free(procedure_stack);
+ procedure_stack = NIL;
+}
+
/*
* Execute CALL statement
*
@@ -2068,6 +2119,7 @@ ExecuteCallStmt(CallStmt *stmt, ParamListInfo params,
bool atomic, DestReceiver
char *argmodes;
FmgrInfo flinfo;
CallContext *callcontext;
+ int prodynres;
EState *estate;
ExprContext *econtext;
HeapTuple tp;
@@ -2108,6 +2160,8 @@ ExecuteCallStmt(CallStmt *stmt, ParamListInfo params,
bool atomic, DestReceiver
if (((Form_pg_proc) GETSTRUCT(tp))->prosecdef)
callcontext->atomic = true;
+ prodynres = ((Form_pg_proc) GETSTRUCT(tp))->prodynres;
+
/*
* Expand named arguments, defaults, etc. We do not want to scribble on
* the passed-in CallStmt parse tree, so first flat-copy fexpr, allowing
@@ -2179,9 +2233,11 @@ ExecuteCallStmt(CallStmt *stmt, ParamListInfo params,
bool atomic, DestReceiver
i++;
}
+ procedure_stack = lappend_oid(procedure_stack, fexpr->funcid);
pgstat_init_function_usage(fcinfo, &fcusage);
retval = FunctionCallInvoke(fcinfo);
pgstat_end_function_usage(&fcusage, true);
+ procedure_stack = list_delete_last(procedure_stack);
if (fexpr->funcresulttype == VOIDOID)
{
@@ -2229,6 +2285,13 @@ ExecuteCallStmt(CallStmt *stmt, ParamListInfo params,
bool atomic, DestReceiver
fexpr->funcresulttype);
FreeExecutorState(estate);
+
+ CloseOtherReturnableCursors(fexpr->funcid);
+
+ if (list_length(GetReturnableCursors()) > prodynres)
+ ereport(WARNING,
+
errcode(ERRCODE_WARNING_ATTEMPT_TO_RETURN_TOO_MANY_RESULT_SETS),
+ errmsg("attempt to return too many result
sets"));
}
/*
diff --git a/src/backend/commands/portalcmds.c
b/src/backend/commands/portalcmds.c
index 0b64204975..b5562f5a24 100644
--- a/src/backend/commands/portalcmds.c
+++ b/src/backend/commands/portalcmds.c
@@ -24,6 +24,7 @@
#include <limits.h>
#include "access/xact.h"
+#include "commands/defrem.h"
#include "commands/portalcmds.h"
#include "executor/executor.h"
#include "executor/tstoreReceiver.h"
@@ -146,6 +147,28 @@ PerformCursorOpen(ParseState *pstate, DeclareCursorStmt
*cstmt, ParamListInfo pa
portal->cursorOptions |= CURSOR_OPT_NO_SCROLL;
}
+ /*
+ * For returnable cursors, remember the currently active procedure, as
+ * well as the command ID, so we can sort by creation order later. If
+ * there is no procedure active, the cursor is marked as WITHOUT RETURN.
+ * (This is not an error, per SQL standard, subclause "Effect of
opening a
+ * cursor".)
+ */
+ if (portal->cursorOptions & CURSOR_OPT_RETURN)
+ {
+ Oid procId = CurrentProcedure();
+
+ if (procId)
+ {
+ portal->procId = procId;
+ portal->createCid = GetCurrentCommandId(true);
+ }
+ else
+ {
+ portal->cursorOptions &= ~CURSOR_OPT_RETURN;
+ }
+ }
+
/*
* Start execution, inserting parameters if any.
*/
diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.c
index a0a8695b1b..b1c90429e2 100644
--- a/src/backend/commands/typecmds.c
+++ b/src/backend/commands/typecmds.c
@@ -1790,7 +1790,8 @@ makeRangeConstructors(const char *name, Oid namespace,
PointerGetDatum(NULL), /* proconfig */
InvalidOid,
/* prosupport */
1.0, /*
procost */
- 0.0); /*
prorows */
+ 0.0, /*
prorows */
+ 0); /*
prodynres */
/*
* Make the constructors internally-dependent on the range type
so
@@ -1854,7 +1855,8 @@ makeMultirangeConstructors(const char *name, Oid
namespace,
PointerGetDatum(NULL),
/* proconfig */
InvalidOid, /*
prosupport */
1.0, /* procost */
- 0.0); /* prorows */
+ 0.0, /* prorows */
+ 0); /* prodynres */
/*
* Make the constructor internally-dependent on the multirange type so
@@ -1897,7 +1899,8 @@ makeMultirangeConstructors(const char *name, Oid
namespace,
PointerGetDatum(NULL),
/* proconfig */
InvalidOid, /*
prosupport */
1.0, /* procost */
- 0.0); /* prorows */
+ 0.0, /* prorows */
+ 0); /* prodynres */
/* ditto */
recordDependencyOn(&myself, &referenced, DEPENDENCY_INTERNAL);
pfree(argtypes);
@@ -1937,7 +1940,8 @@ makeMultirangeConstructors(const char *name, Oid
namespace,
PointerGetDatum(NULL),
/* proconfig */
InvalidOid, /*
prosupport */
1.0, /* procost */
- 0.0); /* prorows */
+ 0.0, /* prorows */
+ 0); /* prodynres */
/* ditto */
recordDependencyOn(&myself, &referenced, DEPENDENCY_INTERNAL);
pfree(argtypes);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 8f341ac006..3c9deb397d 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -637,7 +637,7 @@ static Node *makeRecursiveViewSelect(char *relname, List
*aliases, Node *query);
DATA_P DATABASE DAY_P DEALLOCATE DEC DECIMAL_P DECLARE DEFAULT DEFAULTS
DEFERRABLE DEFERRED DEFINER DELETE_P DELIMITER DELIMITERS DEPENDS DESC
DETACH DICTIONARY DISABLE_P DISCARD DISTINCT DO DOCUMENT_P DOMAIN_P
- DOUBLE_P DROP
+ DOUBLE_P DROP DYNAMIC
EACH ELSE ENABLE_P ENCODING ENCRYPTED END_P ENUM_P ESCAPE EVENT EXCEPT
EXCLUDE EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN EXPRESSION
@@ -682,7 +682,7 @@ static Node *makeRecursiveViewSelect(char *relname, List
*aliases, Node *query);
RANGE READ REAL REASSIGN RECHECK RECURSIVE REF REFERENCES REFERENCING
REFRESH REINDEX RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA
- RESET RESTART RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK
ROLLUP
+ RESET RESTART RESTRICT RESULT RETURN RETURNING RETURNS REVOKE RIGHT
ROLE ROLLBACK ROLLUP
ROUTINE ROUTINES ROW ROWS RULE
SAVEPOINT SCHEMA SCHEMAS SCROLL SEARCH SECOND_P SECURITY SELECT
SEQUENCE SEQUENCES
@@ -7780,6 +7780,10 @@ common_func_opt_item:
{
$$ = makeDefElem("parallel", (Node
*)makeString($2), @1);
}
+ | DYNAMIC RESULT SETS Iconst
+ {
+ $$ = makeDefElem("dynamic_result_sets",
(Node *)makeInteger($4), @1);
+ }
;
createfunc_opt_item:
@@ -11071,6 +11075,12 @@ cursor_options: /*EMPTY*/
{ $$ = 0; }
opt_hold: /* EMPTY */ { $$ = 0; }
| WITH HOLD
{ $$ = CURSOR_OPT_HOLD; }
| WITHOUT HOLD { $$ =
0; }
+ | WITH HOLD WITH RETURN { $$ =
CURSOR_OPT_HOLD | CURSOR_OPT_RETURN; }
+ | WITHOUT HOLD WITH RETURN { $$ =
CURSOR_OPT_RETURN; }
+ | WITH HOLD WITHOUT RETURN { $$ =
CURSOR_OPT_HOLD; }
+ | WITHOUT HOLD WITHOUT RETURN { $$ = 0; }
+ | WITH RETURN { $$ =
CURSOR_OPT_RETURN; }
+ | WITHOUT RETURN { $$ =
0; }
;
/*****************************************************************************
@@ -15144,6 +15154,7 @@ unreserved_keyword:
| DOMAIN_P
| DOUBLE_P
| DROP
+ | DYNAMIC
| EACH
| ENABLE_P
| ENCODING
@@ -15285,6 +15296,8 @@ unreserved_keyword:
| RESET
| RESTART
| RESTRICT
+ | RESULT
+ | RETURN
| RETURNS
| REVOKE
| ROLE
@@ -15678,6 +15691,7 @@ bare_label_keyword:
| DOMAIN_P
| DOUBLE_P
| DROP
+ | DYNAMIC
| EACH
| ELSE
| ENABLE_P
@@ -15861,6 +15875,8 @@ bare_label_keyword:
| RESET
| RESTART
| RESTRICT
+ | RESULT
+ | RETURN
| RETURNS
| REVOKE
| RIGHT
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index d35c5020ea..961a289897 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -41,6 +41,7 @@
#include "access/xact.h"
#include "catalog/pg_type.h"
#include "commands/async.h"
+#include "commands/defrem.h"
#include "commands/prepare.h"
#include "executor/spi.h"
#include "jit/jit.h"
@@ -1076,6 +1077,7 @@ exec_simple_query(const char *query_string)
Portal portal;
DestReceiver *receiver;
int16 format;
+ ListCell *lc;
/*
* Get the command name for use in status display (it also
becomes the
@@ -1235,7 +1237,7 @@ exec_simple_query(const char *query_string)
MemoryContextSwitchTo(oldcontext);
/*
- * Run the portal to completion, and then drop it (and the
receiver).
+ * Run the portal to completion, and then drop it.
*/
(void) PortalRun(portal,
FETCH_ALL,
@@ -1245,10 +1247,34 @@ exec_simple_query(const char *query_string)
receiver,
&qc);
- receiver->rDestroy(receiver);
-
PortalDrop(portal, false);
+ /*
+ * Run portals for dynamic result sets.
+ */
+ foreach (lc, GetReturnableCursors())
+ {
+ Portal portal = lfirst(lc);
+
+ if (dest == DestRemote)
+ SetRemoteDestReceiverParams(receiver, portal);
+
+ PortalRun(portal,
+ FETCH_ALL,
+ true,
+ true,
+ receiver,
+ receiver,
+ NULL);
+
+ PortalDrop(portal, false);
+ }
+
+ /*
+ * Drop the receiver.
+ */
+ receiver->rDestroy(receiver);
+
if (lnext(parsetree_list, parsetree_item) == NULL)
{
/*
@@ -2049,6 +2075,7 @@ exec_execute_message(const char *portal_name, long
max_rows)
const char *sourceText;
const char *prepStmtName;
ParamListInfo portalParams;
+ ListCell *lc;
bool save_log_statement_stats = log_statement_stats;
bool is_xact_command;
bool execute_is_fetch;
@@ -2201,6 +2228,34 @@ exec_execute_message(const char *portal_name, long
max_rows)
receiver,
&qc);
+ /*
+ * Run portals for dynamic result sets.
+ */
+ foreach (lc, GetReturnableCursors())
+ {
+ Portal dyn_portal = lfirst(lc);
+
+ if (dest == DestRemote)
+ SetRemoteDestReceiverParams(receiver, dyn_portal);
+
+ PortalSetResultFormat(dyn_portal, 1, &portal->dynamic_format);
+
+ SendRowDescriptionMessage(&row_description_buf,
+
dyn_portal->tupDesc,
+
FetchPortalTargetList(dyn_portal),
+
dyn_portal->formats);
+
+ PortalRun(dyn_portal,
+ FETCH_ALL,
+ true,
+ true,
+ receiver,
+ receiver,
+ NULL);
+
+ PortalDrop(dyn_portal, false);
+ }
+
receiver->rDestroy(receiver);
/* Done executing; remove the params error callback */
@@ -4108,6 +4163,7 @@ PostgresMain(int argc, char *argv[],
PortalErrorCleanup();
SPICleanup();
+ ProcedureCallsCleanup();
/*
* We can't release replication slots inside AbortTransaction()
as we
diff --git a/src/backend/tcop/pquery.c b/src/backend/tcop/pquery.c
index 96ea74f118..6fb160f604 100644
--- a/src/backend/tcop/pquery.c
+++ b/src/backend/tcop/pquery.c
@@ -629,6 +629,8 @@ PortalSetResultFormat(Portal portal, int nFormats, int16
*formats)
errmsg("bind message has %d result
formats but query has %d columns",
nFormats, natts)));
memcpy(portal->formats, formats, natts * sizeof(int16));
+
+ portal->dynamic_format = 0;
}
else if (nFormats > 0)
{
@@ -637,12 +639,16 @@ PortalSetResultFormat(Portal portal, int nFormats, int16
*formats)
for (i = 0; i < natts; i++)
portal->formats[i] = format1;
+
+ portal->dynamic_format = format1;
}
else
{
/* use default format for all columns */
for (i = 0; i < natts; i++)
portal->formats[i] = 0;
+
+ portal->dynamic_format = 0;
}
}
diff --git a/src/backend/utils/errcodes.txt b/src/backend/utils/errcodes.txt
index c79312ed03..cc1d90f56b 100644
--- a/src/backend/utils/errcodes.txt
+++ b/src/backend/utils/errcodes.txt
@@ -83,6 +83,7 @@ Section: Class 01 - Warning
# do not use this class for failure conditions
01000 W ERRCODE_WARNING
warning
0100C W ERRCODE_WARNING_DYNAMIC_RESULT_SETS_RETURNED
dynamic_result_sets_returned
+0100E W ERRCODE_WARNING_ATTEMPT_TO_RETURN_TOO_MANY_RESULT_SETS
attempt_to_return_too_many_result_sets
01008 W ERRCODE_WARNING_IMPLICIT_ZERO_BIT_PADDING
implicit_zero_bit_padding
01003 W ERRCODE_WARNING_NULL_VALUE_ELIMINATED_IN_SET_FUNCTION
null_value_eliminated_in_set_function
01007 W ERRCODE_WARNING_PRIVILEGE_NOT_GRANTED
privilege_not_granted
diff --git a/src/backend/utils/mmgr/portalmem.c
b/src/backend/utils/mmgr/portalmem.c
index 283dfe2d9e..9a3f425146 100644
--- a/src/backend/utils/mmgr/portalmem.c
+++ b/src/backend/utils/mmgr/portalmem.c
@@ -1278,3 +1278,51 @@ HoldPinnedPortals(void)
}
}
}
+
+static int
+cmp_portals_by_creation(const ListCell *a, const ListCell *b)
+{
+ Portal pa = lfirst(a);
+ Portal pb = lfirst(b);
+
+ return pa->createCid - pb->createCid;
+}
+
+List *
+GetReturnableCursors(void)
+{
+ List *ret = NIL;
+ HASH_SEQ_STATUS status;
+ PortalHashEnt *hentry;
+
+ hash_seq_init(&status, PortalHashTable);
+
+ while ((hentry = (PortalHashEnt *) hash_seq_search(&status)) != NULL)
+ {
+ Portal portal = hentry->portal;
+
+ if (portal->cursorOptions & CURSOR_OPT_RETURN)
+ ret = lappend(ret, portal);
+ }
+
+ list_sort(ret, cmp_portals_by_creation);
+
+ return ret;
+}
+
+void
+CloseOtherReturnableCursors(Oid procid)
+{
+ HASH_SEQ_STATUS status;
+ PortalHashEnt *hentry;
+
+ hash_seq_init(&status, PortalHashTable);
+
+ while ((hentry = (PortalHashEnt *) hash_seq_search(&status)) != NULL)
+ {
+ Portal portal = hentry->portal;
+
+ if (portal->cursorOptions & CURSOR_OPT_RETURN && portal->procId
!= procid)
+ PortalDrop(portal, false);
+ }
+}
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 1ab98a2286..eaa569c819 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -11960,6 +11960,7 @@ dumpFunc(Archive *fout, FuncInfo *finfo)
char *prorows;
char *prosupport;
char *proparallel;
+ int prodynres;
char *lanname;
char *rettypename;
int nallargs;
@@ -12054,10 +12055,17 @@ dumpFunc(Archive *fout, FuncInfo *finfo)
if (fout->remoteVersion >= 120000)
appendPQExpBufferStr(query,
- "prosupport\n");
+ "prosupport,\n");
else
appendPQExpBufferStr(query,
- "'-' AS prosupport\n");
+ "'-' AS
prosupport,\n");
+
+ if (fout->remoteVersion >= 140000)
+ appendPQExpBufferStr(query,
+ "prodynres\n");
+ else
+ appendPQExpBufferStr(query,
+ "0 AS prodynres\n");
appendPQExpBuffer(query,
"FROM pg_catalog.pg_proc "
@@ -12097,6 +12105,7 @@ dumpFunc(Archive *fout, FuncInfo *finfo)
prorows = PQgetvalue(res, 0, PQfnumber(res, "prorows"));
prosupport = PQgetvalue(res, 0, PQfnumber(res, "prosupport"));
proparallel = PQgetvalue(res, 0, PQfnumber(res, "proparallel"));
+ prodynres = atoi(PQgetvalue(res, 0, PQfnumber(res, "prodynres")));
lanname = PQgetvalue(res, 0, PQfnumber(res, "lanname"));
/*
@@ -12273,6 +12282,9 @@ dumpFunc(Archive *fout, FuncInfo *finfo)
if (proisstrict[0] == 't')
appendPQExpBufferStr(q, " STRICT");
+ if (prodynres > 0)
+ appendPQExpBuffer(q, " DYNAMIC RESULT SETS %d", prodynres);
+
if (prosecdef[0] == 't')
appendPQExpBufferStr(q, " SECURITY DEFINER");
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index f8e6dea22d..a25a5101be 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -76,6 +76,9 @@ CATALOG(pg_proc,1255,ProcedureRelationId) BKI_BOOTSTRAP
BKI_ROWTYPE_OID(81,Proce
/* see PROPARALLEL_ categories below */
char proparallel BKI_DEFAULT(s);
+ /* maximum number of dynamic result sets */
+ int32 prodynres BKI_DEFAULT(0);
+
/* number of arguments */
/* Note: need not be given in pg_proc.dat; genbki.pl will compute it */
int16 pronargs;
@@ -209,7 +212,8 @@ extern ObjectAddress ProcedureCreate(const char
*procedureName,
Datum
proconfig,
Oid
prosupport,
float4
procost,
- float4
prorows);
+ float4
prorows,
+ int
dynres);
extern bool function_parse_error_transpose(const char *prosrc);
diff --git a/src/include/commands/defrem.h b/src/include/commands/defrem.h
index 1133ae1143..e3f9fa000a 100644
--- a/src/include/commands/defrem.h
+++ b/src/include/commands/defrem.h
@@ -61,6 +61,8 @@ extern ObjectAddress CreateTransform(CreateTransformStmt
*stmt);
extern void IsThereFunctionInNamespace(const char *proname, int pronargs,
oidvector *proargtypes, Oid nspOid);
extern void ExecuteDoStmt(DoStmt *stmt, bool atomic);
+extern Oid CurrentProcedure(void);
+extern void ProcedureCallsCleanup(void);
extern void ExecuteCallStmt(CallStmt *stmt, ParamListInfo params, bool atomic,
DestReceiver *dest);
extern TupleDesc CallStmtResultDesc(CallStmt *stmt);
extern Oid get_transform_oid(Oid type_id, Oid lang_id, bool missing_ok);
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 48a79a7657..e880a0c053 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2706,11 +2706,12 @@ typedef struct SecLabelStmt
#define CURSOR_OPT_NO_SCROLL 0x0004 /* NO SCROLL explicitly given */
#define CURSOR_OPT_INSENSITIVE 0x0008 /* INSENSITIVE */
#define CURSOR_OPT_HOLD 0x0010 /* WITH HOLD */
+#define CURSOR_OPT_RETURN 0x0020 /* WITH RETURN */
/* these planner-control flags do not correspond to any SQL grammar: */
-#define CURSOR_OPT_FAST_PLAN 0x0020 /* prefer fast-start plan */
-#define CURSOR_OPT_GENERIC_PLAN 0x0040 /* force use of generic plan */
-#define CURSOR_OPT_CUSTOM_PLAN 0x0080 /* force use of custom plan */
-#define CURSOR_OPT_PARALLEL_OK 0x0100 /* parallel mode OK */
+#define CURSOR_OPT_FAST_PLAN 0x0100 /* prefer fast-start plan */
+#define CURSOR_OPT_GENERIC_PLAN 0x0200 /* force use of generic plan */
+#define CURSOR_OPT_CUSTOM_PLAN 0x0400 /* force use of custom plan */
+#define CURSOR_OPT_PARALLEL_OK 0x0800 /* parallel mode OK */
typedef struct DeclareCursorStmt
{
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 71dcdf2889..a2577ce69d 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -139,6 +139,7 @@ PG_KEYWORD("document", DOCUMENT_P, UNRESERVED_KEYWORD,
BARE_LABEL)
PG_KEYWORD("domain", DOMAIN_P, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("double", DOUBLE_P, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("drop", DROP, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("dynamic", DYNAMIC, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("each", EACH, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("else", ELSE, RESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("enable", ENABLE_P, UNRESERVED_KEYWORD, BARE_LABEL)
@@ -344,6 +345,8 @@ PG_KEYWORD("replica", REPLICA, UNRESERVED_KEYWORD,
BARE_LABEL)
PG_KEYWORD("reset", RESET, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("restart", RESTART, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("restrict", RESTRICT, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("result", RESULT, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("return", RETURN, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("returning", RETURNING, RESERVED_KEYWORD, AS_LABEL)
PG_KEYWORD("returns", RETURNS, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("revoke", REVOKE, UNRESERVED_KEYWORD, BARE_LABEL)
diff --git a/src/include/utils/portal.h b/src/include/utils/portal.h
index d41ff2efda..fb3564d873 100644
--- a/src/include/utils/portal.h
+++ b/src/include/utils/portal.h
@@ -131,6 +131,16 @@ typedef struct PortalData
SubTransactionId createSubid; /* the creating subxact */
SubTransactionId activeSubid; /* the last subxact with activity */
+ /*
+ * Procedure that created this portal. Used for returnable cursors.
+ */
+ Oid procId;
+ /*
+ * Command ID where the portal was created. Used for sorting returnable
+ * cursors into creation order.
+ */
+ CommandId createCid;
+
/* The query or queries the portal will execute */
const char *sourceText; /* text of query (as of 8.4, never
NULL) */
CommandTag commandTag; /* command tag for original
query */
@@ -159,6 +169,8 @@ typedef struct PortalData
TupleDesc tupDesc; /* descriptor for result tuples
*/
/* and these are the format codes to use for the columns: */
int16 *formats; /* a format code for each column */
+ /* Format code for dynamic result sets */
+ int16 dynamic_format;
/*
* Where we store tuples for a held cursor or a PORTAL_ONE_RETURNING or
@@ -237,5 +249,7 @@ extern void PortalCreateHoldStore(Portal portal);
extern void PortalHashTableDeleteAll(void);
extern bool ThereAreNoReadyPortals(void);
extern void HoldPinnedPortals(void);
+extern List *GetReturnableCursors(void);
+extern void CloseOtherReturnableCursors(Oid procid);
#endif /* PORTAL_H */
diff --git a/src/interfaces/libpq/fe-protocol3.c
b/src/interfaces/libpq/fe-protocol3.c
index 1696525475..56290f93c1 100644
--- a/src/interfaces/libpq/fe-protocol3.c
+++ b/src/interfaces/libpq/fe-protocol3.c
@@ -297,10 +297,8 @@ pqParseInput3(PGconn *conn)
{
/*
* A new 'T' message is treated
as the start of
- * another PGresult. (It is
not clear that this is
- * really possible with the
current backend.) We stop
- * parsing until the
application accepts the current
- * result.
+ * another PGresult. We stop
parsing until the
+ * application accepts the
current result.
*/
conn->asyncStatus =
PGASYNC_READY;
return;
diff --git a/src/test/regress/expected/create_procedure.out
b/src/test/regress/expected/create_procedure.out
index 3838fa2324..cc86eea731 100644
--- a/src/test/regress/expected/create_procedure.out
+++ b/src/test/regress/expected/create_procedure.out
@@ -212,8 +212,47 @@ ALTER ROUTINE cp_testfunc1a RENAME TO cp_testfunc1;
ALTER ROUTINE ptest1(text) RENAME TO ptest1a;
ALTER ROUTINE ptest1a RENAME TO ptest1;
DROP ROUTINE cp_testfunc1(int);
+-- dynamic result sets
+CREATE TABLE cp_test2 (a int);
+INSERT INTO cp_test2 VALUES (1), (2), (3);
+CREATE TABLE cp_test3 (x text, y text);
+INSERT INTO cp_test3 VALUES ('abc', 'def'), ('foo', 'bar');
+CREATE PROCEDURE pdrstest1()
+LANGUAGE SQL
+DYNAMIC RESULT SETS 2
+AS $$
+DECLARE c1 CURSOR WITH RETURN FOR SELECT * FROM cp_test2;
+DECLARE c2 CURSOR WITH RETURN FOR SELECT * FROM cp_test3;
+$$;
+CALL pdrstest1();
+ a
+---
+ 1
+ 2
+ 3
+(3 rows)
+
+ x | y
+-----+-----
+ abc | def
+ foo | bar
+(2 rows)
+
+CREATE PROCEDURE pdrstest2()
+LANGUAGE SQL
+DYNAMIC RESULT SETS 1
+AS $$
+CALL pdrstest1();
+DECLARE c3 CURSOR WITH RETURN FOR SELECT * FROM cp_test2 WHERE a < 2;
+$$;
+CALL pdrstest2();
+ a
+---
+ 1
+(1 row)
+
-- cleanup
DROP PROCEDURE ptest1;
DROP PROCEDURE ptest2;
-DROP TABLE cp_test;
+DROP TABLE cp_test, cp_test2, cp_test3;
DROP USER regress_cp_user1;
diff --git a/src/test/regress/sql/create_procedure.sql
b/src/test/regress/sql/create_procedure.sql
index 2ef1c82cea..36bc7e3240 100644
--- a/src/test/regress/sql/create_procedure.sql
+++ b/src/test/regress/sql/create_procedure.sql
@@ -167,11 +167,39 @@ CREATE USER regress_cp_user1;
DROP ROUTINE cp_testfunc1(int);
+-- dynamic result sets
+
+CREATE TABLE cp_test2 (a int);
+INSERT INTO cp_test2 VALUES (1), (2), (3);
+CREATE TABLE cp_test3 (x text, y text);
+INSERT INTO cp_test3 VALUES ('abc', 'def'), ('foo', 'bar');
+
+CREATE PROCEDURE pdrstest1()
+LANGUAGE SQL
+DYNAMIC RESULT SETS 2
+AS $$
+DECLARE c1 CURSOR WITH RETURN FOR SELECT * FROM cp_test2;
+DECLARE c2 CURSOR WITH RETURN FOR SELECT * FROM cp_test3;
+$$;
+
+CALL pdrstest1();
+
+CREATE PROCEDURE pdrstest2()
+LANGUAGE SQL
+DYNAMIC RESULT SETS 1
+AS $$
+CALL pdrstest1();
+DECLARE c3 CURSOR WITH RETURN FOR SELECT * FROM cp_test2 WHERE a < 2;
+$$;
+
+CALL pdrstest2();
+
+
-- cleanup
DROP PROCEDURE ptest1;
DROP PROCEDURE ptest2;
-DROP TABLE cp_test;
+DROP TABLE cp_test, cp_test2, cp_test3;
DROP USER regress_cp_user1;
--
2.29.2
#include <stdio.h>
#include <stdlib.h>
#include "libpq-fe.h"
static void
print_result(PGresult *res)
{
printf("=== result set:\n");
for (int row = 0; row < PQntuples(res); row++)
{
printf("row %d: ", row);
for (int col = 0; col < PQnfields(res); col++)
{
printf("%d/%s=%s; ", col, PQfname(res, col),
PQgetvalue(res, row, col));
}
printf("\n");
}
}
int
main(int argc, char *argv[])
{
PGconn *conn;
conn = PQconnectdb("");
if (PQstatus(conn) != CONNECTION_OK)
{
fprintf(stderr, "Connection to database failed: %s",
PQerrorMessage(conn));
exit(1);
}
PGresult *res;
int rc;
rc = PQsendQueryParams(conn, "CALL pdrstest1();",
0, NULL, NULL, NULL, NULL,
-1);
if (rc == 0)
{
fprintf(stderr, "query failed: %s", PQerrorMessage(conn));
exit(1);
}
while ((res = PQgetResult(conn)))
{
print_result(res);
}
PQfinish(conn);
return 0;
}
