Subject: Re: dynamic result sets support in extended query protocol

From: Julien Rouhaud <[email-masked]>
Date: Wed, 12 Jan 2022 18:20:55 +0800

Hi,

On Mon, Aug 30, 2021 at 02:11:34PM -0700, Zhihong Yu wrote:
> On Mon, Aug 30, 2021 at 1:23 PM Peter Eisentraut <
> [email-masked]> wrote:
> 
> > rebased patch set
> 
> +    <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.
> 
> Since there are two options listed, I think using 'These options are' would
> be better.
> 
> For CurrentProcedure(),
> 
> +       return InvalidOid;
> +   else
> +       return llast_oid(procedure_stack);
> 
> The word 'else' can be omitted.

The cfbot reports that the patch doesn't apply anymore:
http://cfbot.cputube.org/patch_36_2911.log.

Since you mentioned that this patch depends on the SHOW_ALL_RESULTS psql patch
which is still being worked on, I'm not expecting much activity here until the
prerequirements are done.  It also seems better to mark this patch as Waiting
on Author as further reviews are probably not really needed for now.

---

From: Peter Eisentraut <[email-masked]>
Date: Tue, 1 Feb 2022 15:40:46 +0100

> on Author as further reviews are probably not really needed for now.

Well, a review on the general architecture and approach would have been 
useful.  But I understand that without the psql work, it's difficult for 
a reviewer to even get started on this patch.  It's also similarly 
difficult for me to keep updating it.  So I'll set it to Returned with 
feedback for now and take it off the table.  I want to get back to it 
when the prerequisites are more settled.

---

From: Peter Eisentraut <[email-masked]>
Date: Fri, 14 Oct 2022 09:11:59 +0200

> when the prerequisites are more settled.

Now that the psql support for multiple result sets exists, I want to 
revive this patch.  It's the same as the last posted version, except now 
it doesn't require any psql changes or any weird test modifications anymore.

(Old news: This patch allows declaring a cursor WITH RETURN in a 
procedure to make 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.)
From 80311214144fba40006dea54817956c3e92110ce Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <[email-masked]>
Date: Fri, 14 Oct 2022 09:01:17 +0200
Subject: [PATCH v5] 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.

Discussion: https://www.postgresql.org/message-id/flat/[email-masked]
---
 doc/src/sgml/catalogs.sgml                    | 10 +++
 doc/src/sgml/information_schema.sgml          |  3 +-
 doc/src/sgml/plpgsql.sgml                     | 27 +++++-
 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           | 79 +++++++++++++++--
 src/backend/commands/portalcmds.c             | 23 +++++
 src/backend/commands/typecmds.c               | 12 ++-
 src/backend/parser/gram.y                     | 18 +++-
 src/backend/tcop/postgres.c                   | 61 ++++++++++++-
 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                 |  1 +
 src/include/nodes/parsenodes.h                |  1 +
 src/include/parser/kwlist.h                   |  2 +
 src/include/utils/portal.h                    | 14 +++
 src/interfaces/libpq/fe-protocol3.c           |  6 +-
 src/pl/plpgsql/src/expected/plpgsql_call.out  | 78 +++++++++++++++++
 src/pl/plpgsql/src/pl_exec.c                  |  6 ++
 src/pl/plpgsql/src/pl_gram.y                  | 58 +++++++++++--
 src/pl/plpgsql/src/pl_unreserved_kwlist.h     |  2 +
 src/pl/plpgsql/src/sql/plpgsql_call.sql       | 46 ++++++++++
 .../regress/expected/create_procedure.out     | 85 ++++++++++++++++++-
 src/test/regress/sql/create_procedure.sql     | 61 ++++++++++++-
 33 files changed, 719 insertions(+), 41 deletions(-)

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 00f833d210e7..16dbe93e2246 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -6020,6 +6020,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 350c75bc31ef..5fc9dc22aeff 100644
--- a/doc/src/sgml/information_schema.sgml
+++ b/doc/src/sgml/information_schema.sgml
@@ -5885,7 +5885,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/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index d85f89bf3033..58a997e15eef 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -3128,7 +3128,7 @@ <title>Declaring Cursor Variables</title>
      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>;
+<replaceable>name</replaceable> <optional> <optional> NO </optional> SCROLL </optional> CURSOR <optional> <optional> WITH RETURN </optional> ( <replaceable>arguments</replaceable> ) </optional> FOR <replaceable>query</replaceable>;
 </synopsis>
      (<literal>FOR</literal> can be replaced by <literal>IS</literal> for
      <productname>Oracle</productname> compatibility.)
@@ -3136,6 +3136,10 @@ <title>Declaring Cursor Variables</title>
      scrolling backward; if <literal>NO SCROLL</literal> is specified, backward
      fetches will be rejected; if neither specification appears, it is
      query-dependent whether backward fetches will be allowed.
+     If <literal>WITH RETURN</literal> is specified, the results of the
+     cursor, after it is opened, will be returned as a dynamic result set; see
+     <xref linkend="sql-declare"/> for details.  (<literal>WITHOUT
+     RETURN</literal> can also be specified but has no effect.)
      <replaceable>arguments</replaceable>, if specified, is a
      comma-separated list of pairs <literal><replaceable>name</replaceable>
      <replaceable>datatype</replaceable></literal> that define names to be
@@ -3194,7 +3198,7 @@ <title>Opening Cursors</title>
      <title><command>OPEN FOR</command> <replaceable>query</replaceable></title>
 
 <synopsis>
-OPEN <replaceable>unbound_cursorvar</replaceable> <optional> <optional> NO </optional> SCROLL </optional> FOR <replaceable>query</replaceable>;
+OPEN <replaceable>unbound_cursorvar</replaceable> <optional> <optional> NO </optional> SCROLL </optional> <optional> WITH RETURN </optional> FOR <replaceable>query</replaceable>;
 </synopsis>
 
        <para>
@@ -3212,8 +3216,9 @@ <title><command>OPEN FOR</command> <replaceable>query</replaceable></title>
         substituted is the one it has at the time of the <command>OPEN</command>;
         subsequent changes to the variable will not affect the cursor's
         behavior.
-        The <literal>SCROLL</literal> and <literal>NO SCROLL</literal>
-        options have the same meanings as for a bound cursor.
+        The options <literal>SCROLL</literal>, <literal>NO SCROLL</literal>,
+        and <literal>WITH RETURN</literal> have the same meanings as for a
+        bound cursor.
        </para>
 
        <para>
@@ -3590,6 +3595,20 @@ <title>Returning Cursors</title>
 COMMIT;
 </programlisting>
        </para>
+
+       <note>
+        <para>
+         Returning a cursor from a function as described here is a separate
+         mechanism from declaring a cursor <literal>WITH RETURN</literal>,
+         which automatically produces a result set for the client if the
+         cursor is left open when returning from the procedure.  Both
+         mechanisms can be used to achieve similar effects.  The differences
+         are mainly how the client application prefers to manage the cursors.
+         Furthermore, other SQL implementations have other programming models
+         that might map more easily to one or the other mechanism when doing a
+         migration.
+        </para>
+       </note>
      </sect3>
    </sect2>
 
diff --git a/doc/src/sgml/protocol.sgml b/doc/src/sgml/protocol.sgml
index 5fdd429e05d3..f11295168636 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 issued 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 20a623885f74..76b9425a08b5 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
@@ -157,6 +158,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 03a14c868458..1c99b00eef8b 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 }
@@ -176,6 +177,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 bbbd335bd0bf..a6ff2567ea3b 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 ] [ ASENSITIVE | 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>
 
@@ -131,6 +132,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>
@@ -323,6 +340,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 18725a02d1fb..7b440e761377 100644
--- a/src/backend/catalog/information_schema.sql
+++ b/src/backend/catalog/information_schema.sql
@@ -1593,7 +1593,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 0d0daa69b340..a8f0967d3d9d 100644
--- a/src/backend/catalog/pg_aggregate.c
+++ b/src/backend/catalog/pg_aggregate.c
@@ -640,7 +640,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 a9fe45e34714..667ddf289dc2 100644
--- a/src/backend/catalog/pg_proc.c
+++ b/src/backend/catalog/pg_proc.c
@@ -95,7 +95,8 @@ ProcedureCreate(const char *procedureName,
 				Datum proconfig,
 				Oid prosupport,
 				float4 procost,
-				float4 prorows)
+				float4 prorows,
+				int dynres)
 {
 	Oid			retval;
 	int			parameterCount;
@@ -314,6 +315,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 da7c9c772e09..eb8a2465e853 100644
--- a/src/backend/catalog/sql_features.txt
+++ b/src/backend/catalog/sql_features.txt
@@ -486,7 +486,7 @@ T433	Multiargument GROUPING function			YES
 T434	GROUP BY DISTINCT			YES	
 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 e6fcfc23b931..1f2400854312 100644
--- a/src/backend/commands/functioncmds.c
+++ b/src/backend/commands/functioncmds.c
@@ -72,6 +72,7 @@
 #include "utils/guc.h"
 #include "utils/lsyscache.h"
 #include "utils/memutils.h"
+#include "utils/portal.h"
 #include "utils/rel.h"
 #include "utils/snapmgr.h"
 #include "utils/syscache.h"
@@ -513,7 +514,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)
 	{
@@ -589,12 +591,28 @@ 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)
+			errorConflictingDefElem(defel, pstate);
+
+		*dynres_item = defel;
+	}
 	else
 		return false;
 
 	/* Recognized an option */
 	return true;
 
+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),
@@ -731,7 +749,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;
@@ -747,6 +766,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)
 	{
@@ -792,7 +812,8 @@ compute_function_attributes(ParseState *pstate,
 										  &cost_item,
 										  &rows_item,
 										  &support_item,
-										  &parallel_item))
+										  &parallel_item,
+										  &dynres_item))
 		{
 			/* recognized common option */
 			continue;
@@ -840,6 +861,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 */
+	}
 }

@@ -1051,6 +1077,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,
@@ -1075,6 +1102,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,
@@ -1084,7 +1112,7 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt *stmt)
 								&isWindowFunc, &volatility,
 								&isStrict, &security, &isLeakProof,
 								&proconfig, &procost, &prorows,
-								&prosupport, &parallel);
+								&prosupport, &parallel, &dynres);
 
 	if (!language)
 	{
@@ -1285,7 +1313,8 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt *stmt)
 						   PointerGetDatum(proconfig),
 						   prosupport,
 						   procost,
-						   prorows);
+						   prorows,
+						   dynres);
 }
 
 /*
@@ -1362,6 +1391,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);
@@ -1405,7 +1435,8 @@ AlterFunction(ParseState *pstate, AlterFunctionStmt *stmt)
 									 &cost_item,
 									 &rows_item,
 									 &support_item,
-									 &parallel_item) == false)
+									 &parallel_item,
+									 &dynres_item) == false)
 			elog(ERROR, "option \"%s\" not recognized", defel->defname);
 	}
 
@@ -1467,6 +1498,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);
 	if (set_items)
 	{
 		Datum		datum;
@@ -2138,6 +2171,17 @@ ExecuteDoStmt(ParseState *pstate, 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);
+}
+
 /*
  * Execute CALL statement
  *
@@ -2177,6 +2221,7 @@ ExecuteCallStmt(CallStmt *stmt, ParamListInfo params, bool atomic, DestReceiver
 	AclResult	aclresult;
 	FmgrInfo	flinfo;
 	CallContext *callcontext;
+	int			prodynres;
 	EState	   *estate;
 	ExprContext *econtext;
 	HeapTuple	tp;
@@ -2217,6 +2262,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;
+
 	ReleaseSysCache(tp);
 
 	/* safety check; see ExecInitFunc() */
@@ -2277,7 +2324,18 @@ ExecuteCallStmt(CallStmt *stmt, ParamListInfo params, bool atomic, DestReceiver
 
 	/* Here we actually call the procedure */
 	pgstat_init_function_usage(fcinfo, &fcusage);
-	retval = FunctionCallInvoke(fcinfo);
+
+	procedure_stack = lappend_oid(procedure_stack, fexpr->funcid);
+	PG_TRY();
+	{
+		retval = FunctionCallInvoke(fcinfo);
+	}
+	PG_FINALLY();
+	{
+		procedure_stack = list_delete_last(procedure_stack);
+	}
+	PG_END_TRY();
+
 	pgstat_end_function_usage(&fcusage, true);
 
 	/* Handle the procedure's outputs */
@@ -2338,6 +2396,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 9902c5c5669a..c42f88f2ddda 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"
@@ -140,6 +141,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 33b64fd2793b..942cbfa93f92 100644
--- a/src/backend/commands/typecmds.c
+++ b/src/backend/commands/typecmds.c
@@ -1775,7 +1775,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
@@ -1840,7 +1841,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
@@ -1884,7 +1886,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);
@@ -1922,7 +1925,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 94d5142a4a06..029d86fa30f1 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -689,7 +689,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 DEPTH 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
@@ -736,7 +736,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 RETURN 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
@@ -8512,6 +8512,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:
@@ -12401,6 +12405,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; }
 		;
 
 /*****************************************************************************
@@ -16722,6 +16732,7 @@ unreserved_keyword:
 			| DOMAIN_P
 			| DOUBLE_P
 			| DROP
+			| DYNAMIC
 			| EACH
 			| ENABLE_P
 			| ENCODING
@@ -16867,6 +16878,7 @@ unreserved_keyword:
 			| RESET
 			| RESTART
 			| RESTRICT
+			| RESULT
 			| RETURN
 			| RETURNS
 			| REVOKE
@@ -17267,6 +17279,7 @@ bare_label_keyword:
 			| DOMAIN_P
 			| DOUBLE_P
 			| DROP
+			| DYNAMIC
 			| EACH
 			| ELSE
 			| ENABLE_P
@@ -17454,6 +17467,7 @@ bare_label_keyword:
 			| RESET
 			| RESTART
 			| RESTRICT
+			| RESULT
 			| RETURN
 			| RETURNS
 			| REVOKE
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index 27dee29f420b..e60fca128a1e 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -33,6 +33,7 @@
 #include "access/xact.h"
 #include "catalog/pg_type.h"
 #include "commands/async.h"
+#include "commands/defrem.h"
 #include "commands/prepare.h"
 #include "common/pg_prng.h"
 #include "jit/jit.h"
@@ -1072,6 +1073,7 @@ exec_simple_query(const char *query_string)
 		Portal		portal;
 		DestReceiver *receiver;
 		int16		format;
+		ListCell   *lc;
 
 		pgstat_report_query_id(0, true);
 
@@ -1233,7 +1235,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,
@@ -1243,10 +1245,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		dynportal = lfirst(lc);
+
+			if (dest == DestRemote)
+				SetRemoteDestReceiverParams(receiver, dynportal);
+
+			PortalRun(dynportal,
+					  FETCH_ALL,
+					  true,
+					  true,
+					  receiver,
+					  receiver,
+					  NULL);
+
+			PortalDrop(dynportal, false);
+		}
+
+		/*
+		 * Drop the receiver.
+		 */
+		receiver->rDestroy(receiver);
+
 		if (lnext(parsetree_list, parsetree_item) == NULL)
 		{
 			/*
@@ -2058,6 +2084,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;
@@ -2194,6 +2221,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 == DestRemoteExecute)
+			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 */
diff --git a/src/backend/tcop/pquery.c b/src/backend/tcop/pquery.c
index 5aa5a350f387..d2e55501325a 100644
--- a/src/backend/tcop/pquery.c
+++ b/src/backend/tcop/pquery.c
@@ -641,6 +641,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)
 	{
@@ -649,12 +651,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 62418a051ac7..c20722e6bbdd 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 3a161bdb88d1..e27e082a618f 100644
--- a/src/backend/utils/mmgr/portalmem.c
+++ b/src/backend/utils/mmgr/portalmem.c
@@ -1289,3 +1289,51 @@ ForgetPortalSnapshots(void)
 		elog(ERROR, "portal snapshots (%d) did not account for all active snapshots (%d)",
 			 numPortalSnaps, numActiveSnaps);
 }
+
+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 bd9b066e4eb8..e6d17aedc345 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -11573,6 +11573,7 @@ dumpFunc(Archive *fout, const FuncInfo *finfo)
 	char	   *prorows;
 	char	   *prosupport;
 	char	   *proparallel;
+	int			prodynres;
 	char	   *lanname;
 	char	  **configitems = NULL;
 	int			nconfigitems = 0;
@@ -11640,10 +11641,17 @@ dumpFunc(Archive *fout, const FuncInfo *finfo)
 
 		if (fout->remoteVersion >= 140000)
 			appendPQExpBufferStr(query,
-								 "pg_get_function_sqlbody(p.oid) AS prosqlbody\n");
+								 "pg_get_function_sqlbody(p.oid) AS prosqlbody,\n");
 		else
 			appendPQExpBufferStr(query,
-								 "NULL AS prosqlbody\n");
+								 "NULL AS prosqlbody,\n");
+
+		if (fout->remoteVersion >= 160000)
+			appendPQExpBufferStr(query,
+								 "prodynres\n");
+		else
+			appendPQExpBufferStr(query,
+								 "0 AS prodynres\n");
 
 		appendPQExpBufferStr(query,
 							 "FROM pg_catalog.pg_proc p, pg_catalog.pg_language l\n"
@@ -11688,6 +11696,7 @@ dumpFunc(Archive *fout, const 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"));
 
 	/*
@@ -11806,6 +11815,9 @@ dumpFunc(Archive *fout, const 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 76310d4cc9a1..c5d2f5beba82 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;
@@ -211,7 +214,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 56d2bb661612..84591a529bc7 100644
--- a/src/include/commands/defrem.h
+++ b/src/include/commands/defrem.h
@@ -57,6 +57,7 @@ extern ObjectAddress CreateTransform(CreateTransformStmt *stmt);
 extern void IsThereFunctionInNamespace(const char *proname, int pronargs,
 									   oidvector *proargtypes, Oid nspOid);
 extern void ExecuteDoStmt(ParseState *pstate, DoStmt *stmt, bool atomic);
+extern Oid	CurrentProcedure(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 633e7671b3ea..ef10e2ff78dd 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2899,6 +2899,7 @@ typedef struct SecLabelStmt
 #define CURSOR_OPT_INSENSITIVE	0x0008	/* INSENSITIVE */
 #define CURSOR_OPT_ASENSITIVE	0x0010	/* ASENSITIVE */
 #define CURSOR_OPT_HOLD			0x0020	/* WITH HOLD */
+#define CURSOR_OPT_RETURN		0x0040	/* WITH RETURN */
 /* these planner-control flags do not correspond to any SQL grammar: */
 #define CURSOR_OPT_FAST_PLAN	0x0100	/* prefer fast-start plan */
 #define CURSOR_OPT_GENERIC_PLAN 0x0200	/* force use of generic plan */
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index ccc927851cb9..9cf45a02d3f9 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -144,6 +144,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)
@@ -353,6 +354,7 @@ 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)
diff --git a/src/include/utils/portal.h b/src/include/utils/portal.h
index aeddbdafe56a..eed9bb372dbc 100644
--- a/src/include/utils/portal.h
+++ b/src/include/utils/portal.h
@@ -132,6 +132,16 @@ typedef struct PortalData
 	SubTransactionId activeSubid;	/* the last subxact with activity */
 	int			createLevel;	/* creating subxact's nesting level */
 
+	/*
+	 * 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 */
@@ -160,6 +170,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;
 
 	/*
 	 * Outermost ActiveSnapshot for execution of the portal's queries.  For
@@ -248,5 +260,7 @@ extern void PortalHashTableDeleteAll(void);
 extern bool ThereAreNoReadyPortals(void);
 extern void HoldPinnedPortals(void);
 extern void ForgetPortalSnapshots(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 f001137b7692..0fa4c9537bd1 100644
--- a/src/interfaces/libpq/fe-protocol3.c
+++ b/src/interfaces/libpq/fe-protocol3.c
@@ -324,10 +324,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/pl/plpgsql/src/expected/plpgsql_call.out b/src/pl/plpgsql/src/expected/plpgsql_call.out
index 1ec6182a8da8..1c8872a75470 100644
--- a/src/pl/plpgsql/src/expected/plpgsql_call.out
+++ b/src/pl/plpgsql/src/expected/plpgsql_call.out
@@ -466,3 +466,81 @@ BEGIN
 END;
 $$;
 NOTICE:  <NULL>
+-- 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(x int)
+LANGUAGE plpgsql
+DYNAMIC RESULT SETS 2
+AS $$
+DECLARE
+  c1 CURSOR WITH RETURN (y int) FOR SELECT a * y AS ay FROM cp_test2;
+  c2 CURSOR WITH RETURN FOR SELECT * FROM cp_test3;
+BEGIN
+  OPEN c1(x);
+  IF x > 1 THEN
+    OPEN c2;
+  END IF;
+END;
+$$;
+CALL pdrstest1(1);
+ ay 
+----
+  1
+  2
+  3
+(3 rows)
+
+CALL pdrstest1(2);
+ ay 
+----
+  2
+  4
+  6
+(3 rows)
+
+  x  |  y  
+-----+-----
+ abc | def
+ foo | bar
+(2 rows)
+
+CREATE PROCEDURE pdrstest2(x int)
+LANGUAGE plpgsql
+DYNAMIC RESULT SETS 2
+AS $$
+DECLARE
+  c1 refcursor;
+  c2 refcursor;
+BEGIN
+  OPEN c1 WITH RETURN FOR SELECT a * x AS ax FROM cp_test2;
+  IF x > 1 THEN
+    OPEN c2 SCROLL WITH RETURN FOR SELECT * FROM cp_test3;
+  END IF;
+END;
+$$;
+CALL pdrstest2(1);
+ ax 
+----
+  1
+  2
+  3
+(3 rows)
+
+CALL pdrstest2(2);
+ ax 
+----
+  2
+  4
+  6
+(3 rows)
+
+  x  |  y  
+-----+-----
+ abc | def
+ foo | bar
+(2 rows)
+
+DROP TABLE cp_test2, cp_test3;
diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c
index a64734294805..07d21ad8ed79 100644
--- a/src/pl/plpgsql/src/pl_exec.c
+++ b/src/pl/plpgsql/src/pl_exec.c
@@ -4776,6 +4776,12 @@ exec_stmt_open(PLpgSQL_execstate *estate, PLpgSQL_stmt_open *stmt)
 		elog(ERROR, "could not open cursor: %s",
 			 SPI_result_code_string(SPI_result));
 
+	if (portal->cursorOptions & CURSOR_OPT_RETURN)
+	{
+		portal->procId = estate->func->fn_oid;
+		portal->createCid = GetCurrentCommandId(true);
+	}
+
 	/*
 	 * If cursor variable was NULL, store the generated portal name in it,
 	 * after verifying it's okay to assign to.
diff --git a/src/pl/plpgsql/src/pl_gram.y b/src/pl/plpgsql/src/pl_gram.y
index f7cf2b4b899a..e5e9222d3ec3 100644
--- a/src/pl/plpgsql/src/pl_gram.y
+++ b/src/pl/plpgsql/src/pl_gram.y
@@ -212,7 +212,7 @@ static	void			check_raise_parameters(PLpgSQL_stmt_raise *stmt);
 %type <datum>	getdiag_target
 %type <ival>	getdiag_item
 
-%type <ival>	opt_scrollable
+%type <ival>	opt_scrollable opt_with_return
 %type <fetch>	opt_fetch_direction
 
 %type <ival>	opt_transaction_chain
@@ -352,6 +352,8 @@ static	void			check_raise_parameters(PLpgSQL_stmt_raise *stmt);
 %token <keyword>	K_WARNING
 %token <keyword>	K_WHEN
 %token <keyword>	K_WHILE
+%token <keyword>	K_WITH
+%token <keyword>	K_WITHOUT
 
 %%
 
@@ -529,7 +531,7 @@ decl_statement	: decl_varname decl_const decl_datatype decl_collate decl_notnull
 						plpgsql_ns_additem($4->itemtype,
 										   $4->itemno, $1.name);
 					}
-				| decl_varname opt_scrollable K_CURSOR
+				| decl_varname opt_scrollable K_CURSOR opt_with_return
 					{ plpgsql_ns_push($1.name, PLPGSQL_LABEL_OTHER); }
 				  decl_cursor_args decl_is_for decl_cursor_query
 					{
@@ -573,12 +575,12 @@ decl_statement	: decl_varname decl_const decl_datatype decl_collate decl_notnull
 						curname_def->parseMode = RAW_PARSE_PLPGSQL_EXPR;
 						new->default_val = curname_def;
 
-						new->cursor_explicit_expr = $7;
-						if ($5 == NULL)
+						new->cursor_explicit_expr = $8;
+						if ($6 == NULL)
 							new->cursor_explicit_argrow = -1;
 						else
-							new->cursor_explicit_argrow = $5->dno;
-						new->cursor_options = CURSOR_OPT_FAST_PLAN | $2;
+							new->cursor_explicit_argrow = $6->dno;
+						new->cursor_options = CURSOR_OPT_FAST_PLAN | $2 | $4;
 					}
 				;
 
@@ -596,6 +598,20 @@ opt_scrollable :
 					}
 				;
 
+opt_with_return :
+					{
+						$$ = 0;
+					}
+				| K_WITH K_RETURN
+					{
+						$$ = CURSOR_OPT_RETURN;
+					}
+				| K_WITHOUT K_RETURN
+					{
+						$$ = 0;
+					}
+				;
+
 decl_cursor_query :
 					{
 						$$ = read_sql_stmt();
@@ -2003,6 +2019,10 @@ stmt_execsql	: K_IMPORT
 					{
 						$$ = make_execsql_stmt(K_MERGE, @1);
 					}
+				| K_WITH
+					{
+						$$ = make_execsql_stmt(K_WITH, @1);
+					}
 				| T_WORD
 					{
 						int			tok;
@@ -2125,6 +2145,30 @@ stmt_open		: K_OPEN cursor_variable
 								tok = yylex();
 							}
 
+							/* same for opt_with_return */
+							if (tok_is_keyword(tok, &yylval,
+											   K_WITH, "with"))
+							{
+								tok = yylex();
+								if (tok_is_keyword(tok, &yylval,
+												   K_RETURN, "return"))
+								{
+									new->cursor_options |= CURSOR_OPT_RETURN;
+									tok = yylex();
+								}
+							}
+							else if (tok_is_keyword(tok, &yylval,
+											   K_WITHOUT, "without"))
+							{
+								tok = yylex();
+								if (tok_is_keyword(tok, &yylval,
+												   K_RETURN, "return"))
+								{
+									new->cursor_options |= 0;
+									tok = yylex();
+								}
+							}
+
 							if (tok != K_FOR)
 								yyerror("syntax error, expected \"FOR\"");
 
@@ -2579,6 +2623,8 @@ unreserved_keyword	:
 				| K_USE_VARIABLE
 				| K_VARIABLE_CONFLICT
 				| K_WARNING
+				| K_WITH
+				| K_WITHOUT
 				;
 
 %%
diff --git a/src/pl/plpgsql/src/pl_unreserved_kwlist.h b/src/pl/plpgsql/src/pl_unreserved_kwlist.h
index ee2be1b212b4..daf0b1081de0 100644
--- a/src/pl/plpgsql/src/pl_unreserved_kwlist.h
+++ b/src/pl/plpgsql/src/pl_unreserved_kwlist.h
@@ -109,3 +109,5 @@ PG_KEYWORD("use_column", K_USE_COLUMN)
 PG_KEYWORD("use_variable", K_USE_VARIABLE)
 PG_KEYWORD("variable_conflict", K_VARIABLE_CONFLICT)
 PG_KEYWORD("warning", K_WARNING)
+PG_KEYWORD("with", K_WITH)
+PG_KEYWORD("without", K_WITHOUT)
diff --git a/src/pl/plpgsql/src/sql/plpgsql_call.sql b/src/pl/plpgsql/src/sql/plpgsql_call.sql
index 502839834803..9ace9d4e0676 100644
--- a/src/pl/plpgsql/src/sql/plpgsql_call.sql
+++ b/src/pl/plpgsql/src/sql/plpgsql_call.sql
@@ -436,3 +436,49 @@ CREATE PROCEDURE p1(v_cnt int, v_Text inout text = NULL)
   RAISE NOTICE '%', v_Text;
 END;
 $$;
+
+
+-- 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(x int)
+LANGUAGE plpgsql
+DYNAMIC RESULT SETS 2
+AS $$
+DECLARE
+  c1 CURSOR WITH RETURN (y int) FOR SELECT a * y AS ay FROM cp_test2;
+  c2 CURSOR WITH RETURN FOR SELECT * FROM cp_test3;
+BEGIN
+  OPEN c1(x);
+  IF x > 1 THEN
+    OPEN c2;
+  END IF;
+END;
+$$;
+
+CALL pdrstest1(1);
+CALL pdrstest1(2);
+
+CREATE PROCEDURE pdrstest2(x int)
+LANGUAGE plpgsql
+DYNAMIC RESULT SETS 2
+AS $$
+DECLARE
+  c1 refcursor;
+  c2 refcursor;
+BEGIN
+  OPEN c1 WITH RETURN FOR SELECT a * x AS ax FROM cp_test2;
+  IF x > 1 THEN
+    OPEN c2 SCROLL WITH RETURN FOR SELECT * FROM cp_test3;
+  END IF;
+END;
+$$;
+
+CALL pdrstest2(1);
+CALL pdrstest2(2);
+
+DROP TABLE cp_test2, cp_test3;
diff --git a/src/test/regress/expected/create_procedure.out b/src/test/regress/expected/create_procedure.out
index 46c827f9791c..b3802bd7c1db 100644
--- a/src/test/regress/expected/create_procedure.out
+++ b/src/test/regress/expected/create_procedure.out
@@ -375,9 +375,92 @@ 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)
+
+CREATE PROCEDURE pdrstest3(INOUT a text)
+LANGUAGE SQL
+DYNAMIC RESULT SETS 1
+AS $$
+DECLARE c4 CURSOR WITH RETURN FOR SELECT * FROM cp_test2;
+SELECT a || a;
+$$;
+CALL pdrstest3('x');
+ a  
+----
+ xx
+(1 row)
+
+ a 
+---
+ 1
+ 2
+ 3
+(3 rows)
+
+-- test the nested error handling
+CREATE TABLE cp_test_dummy (a int);
+CREATE PROCEDURE pdrstest4a()
+LANGUAGE SQL
+DYNAMIC RESULT SETS 1
+AS $$
+DECLARE c5a CURSOR WITH RETURN FOR SELECT * FROM cp_test_dummy;
+$$;
+CREATE PROCEDURE pdrstest4b()
+LANGUAGE SQL
+DYNAMIC RESULT SETS 1
+AS $$
+CALL pdrstest4a();
+$$;
+DROP TABLE cp_test_dummy;
+CALL pdrstest4b();
+ERROR:  relation "cp_test_dummy" does not exist
+LINE 2: DECLARE c5a CURSOR WITH RETURN FOR SELECT * FROM cp_test_dum...
+                                                         ^
+QUERY:  
+DECLARE c5a CURSOR WITH RETURN FOR SELECT * FROM cp_test_dummy;
+
+CONTEXT:  SQL function "pdrstest4a" during startup
+SQL function "pdrstest4b" statement 1
 -- cleanup
 DROP PROCEDURE ptest1;
 DROP PROCEDURE ptest1s;
 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 75cc0fcf2a6c..c3970726a6e0 100644
--- a/src/test/regress/sql/create_procedure.sql
+++ b/src/test/regress/sql/create_procedure.sql
@@ -242,12 +242,71 @@ 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();
+
+CREATE PROCEDURE pdrstest3(INOUT a text)
+LANGUAGE SQL
+DYNAMIC RESULT SETS 1
+AS $$
+DECLARE c4 CURSOR WITH RETURN FOR SELECT * FROM cp_test2;
+SELECT a || a;
+$$;
+
+CALL pdrstest3('x');
+
+-- test the nested error handling
+CREATE TABLE cp_test_dummy (a int);
+
+CREATE PROCEDURE pdrstest4a()
+LANGUAGE SQL
+DYNAMIC RESULT SETS 1
+AS $$
+DECLARE c5a CURSOR WITH RETURN FOR SELECT * FROM cp_test_dummy;
+$$;
+
+CREATE PROCEDURE pdrstest4b()
+LANGUAGE SQL
+DYNAMIC RESULT SETS 1
+AS $$
+CALL pdrstest4a();
+$$;
+
+DROP TABLE cp_test_dummy;
+
+CALL pdrstest4b();
+
+
 -- cleanup
 
 DROP PROCEDURE ptest1;
 DROP PROCEDURE ptest1s;
 DROP PROCEDURE ptest2;
 
-DROP TABLE cp_test;
+DROP TABLE cp_test, cp_test2, cp_test3;
 
 DROP USER regress_cp_user1;

base-commit: 39b8c293fcde1d845da4d7127a25d41df53faab5
-- 
2.37.3

---

From: Pavel Stehule <[email-masked]>
Date: Fri, 14 Oct 2022 19:22:30 +0200

Hi

> invocation.  The procedure needs to be declared with the DYNAMIC RESULT

I did a quick test of this patch, and it is working pretty well.

I have two ideas.

1. there can be possibility to set "dynamic result sets" to unknown. The
behaviour of the "dynamic result sets" option is a little bit confusing. I
expect the number of result sets should be exactly the same as this number.
But the warning is raised only when this number is acrossed. For this
implementation the correct name should be like "max dynamic result sets" or
some like this. At this moment, I see this feature "dynamic result sets"
more confusing, and because the effect is just a warning, then I don't see
a strong benefit. I can see some benefit if I can declare so CALL will be
without dynamic result sets, or with exact number of dynamic result sets or
with unknown number of dynamic result sets. And if the result is not
expected, then an exception should be raised (not warning).

2. Unfortunately, it doesn't work nicely with pagers. It starts a pager for
one result, and waits for the end, and starts pager for the second result,
and waits for the end. There is not a possibility to see all results at one
time. The current behavior is correct, but I don't think it is user
friendly. I think I can teach pspg to support multiple documents. But I
need a more robust protocol and some separators - minimally an empty line
(but some ascii control char can be safer). As second step we can introduce
new psql option like PSQL_MULTI_PAGER, that can be used when possible
result sets is higher than 1

Regards

Pavel

---

From: Peter Eisentraut <[email-masked]>
Date: Tue, 15 Nov 2022 15:58:57 +0100

On 14.10.22 19:22, Pavel Stehule wrote:
> behaviour of the "dynamic result sets" option is a little bit confusing. 
> I expect the number of result sets should be exactly the same as this 
> number. But the warning is raised only when this number is acrossed. For 
> this implementation the correct name should be like "max dynamic result 
> sets" or some like this. At this moment, I see this feature "dynamic 
> result sets" more confusing, and because the effect is just a warning, 
> then I don't see a strong benefit. I can see some benefit if I can 
> declare so CALL will be without dynamic result sets, or with exact 
> number of dynamic result sets or with unknown number of dynamic result 
> sets. And if the result is not expected, then an exception should be 
> raised (not warning).

All of this is specified by the SQL standard.  (What I mean by that is 
that if we want to deviate from that, we should have strong reasons 
beyond "it seems a bit odd".)

> 2. Unfortunately, it doesn't work nicely with pagers. It starts a pager 
> for one result, and waits for the end, and starts pager for the second 
> result, and waits for the end. There is not a possibility to see all 
> results at one time. The current behavior is correct, but I don't think 
> it is user friendly. I think I can teach pspg to support multiple 
> documents. But I need a more robust protocol and some separators - 
> minimally an empty line (but some ascii control char can be safer). As 
> second step we can introduce new psql option like PSQL_MULTI_PAGER, that 
> can be used when possible result sets is higher than 1

I think that is unrelated to this patch.  Multiple result sets already 
exist and libpq and psql handle them.  This patch introduces another way 
in which multiple result sets can be produced on the server, but it 
doesn't touch the client side.  So your concerns should be added either 
as a new feature or possibly as a bug against existing psql functionality.

---

From: Peter Eisentraut <[email-masked]>
Date: Tue, 22 Nov 2022 16:57:17 +0100

> invocation.  The procedure needs to be declared with the DYNAMIC RESULT 

I added tests using the new psql \bind command to test this 
functionality in the extended query protocol, which showed that this got 
broken since I first wrote this patch.  This "blame" is on the pipeline 
mode in libpq patch (acb7e4eb6b1c614c68a62fb3a6a5bba1af0a2659).  I need 
to spend more time on this and figure out how to repair it.  In the 
meantime, here is an updated patch set with the current status.
From 31ab22f7f4aab5666abec9c9b0f8e2e9a8e6421a Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <[email-masked]>
Date: Fri, 14 Oct 2022 09:01:17 +0200
Subject: [PATCH v6 1/2] Dynamic result sets from procedures

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 9ed2b020b7d9..1fd7ff81a764 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -6031,6 +6031,16 @@ <title><structname>pg_proc</structname> Columns</title>
       </para></entry>
      </row>
 
+     <row>
 
index dda667e68e8c..c9129559a570 100644
 
 <synopsis>
 
        <para>
 
        <para>

+   <para>

 
+   <varlistentry>
 
+    <varlistentry>

 
+   <varlistentry>
 
  <refsect1>
index a98445b741a1..e9fcc450bff9 100644
 
 	/*
index 69f43aa0ecbf..d7e3d2319e3e 100644
index 57489f65f2e7..a74023637f6d 100644
 
 		*parallel_item = defel;
 	}

+function_error:
+	ereport(ERROR,
 
 	foreach(option, options)
 	{

 
 
 	if (!language)
 	{
 
 /*

@@ -2144,6 +2177,17 @@ ExecuteDoStmt(ParseState *pstate, DoStmt *stmt, bool atomic)
 
+static List *procedure_stack;
+
+Oid
+CurrentProcedure(void)
+{
+	if (!procedure_stack)
+		return InvalidOid;
+	else
+		return llast_oid(procedure_stack);
+}
+
 /*
  * Execute CALL statement
  *
@@ -2183,6 +2227,7 @@ ExecuteCallStmt(CallStmt *stmt, ParamListInfo params, bool atomic, DestReceiver
 	AclResult	aclresult;
 	FmgrInfo	flinfo;
 	CallContext *callcontext;
+	int			prodynres;
 	EState	   *estate;
 	ExprContext *econtext;
 	HeapTuple	tp;
@@ -2223,6 +2268,8 @@ ExecuteCallStmt(CallStmt *stmt, ParamListInfo params, bool atomic, DestReceiver
 	if (((Form_pg_proc) GETSTRUCT(tp))->prosecdef)
 		callcontext->atomic = true;

 	/* safety check; see ExecInitFunc() */
@@ -2283,7 +2330,18 @@ ExecuteCallStmt(CallStmt *stmt, ParamListInfo params, bool atomic, DestReceiver

 	/* Handle the procedure's outputs */
@@ -2344,6 +2402,13 @@ ExecuteCallStmt(CallStmt *stmt, ParamListInfo params, bool atomic, DestReceiver
 			 fexpr->funcresulttype);

 /*
diff --git a/src/backend/commands/portalcmds.c b/src/backend/commands/portalcmds.c
index 09bcfd59be6b..7046267f7f0d 100644
--- a/src/backend/commands/portalcmds.c
+++ b/src/backend/commands/portalcmds.c
@@ -24,6 +24,7 @@
 #include <limits.h>
 
 #include "access/xact.h"
 
+	/*
index 7770a86bee08..45da9a73c982 100644
@@ -1777,7 +1777,8 @@ makeRangeConstructors(const char *name, Oid namespace,
 
 		/*
 		 * Make the constructors internally-dependent on the range type so
@@ -1842,7 +1843,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
@@ -1886,7 +1888,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);
@@ -1924,7 +1927,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 9384214942aa..9b8d24bc01b7 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -688,7 +688,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 DEPTH 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
@@ -735,7 +735,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 
 	RANGE READ REAL REASSIGN RECHECK RECURSIVE REF_P REFERENCES REFERENCING

 createfunc_opt_item:
 
 /*****************************************************************************
@@ -16767,6 +16777,7 @@ unreserved_keyword:
 			| DOMAIN_P
 			| DOUBLE_P
 			| DROP
+			| DYNAMIC
 			| EACH
 			| ENABLE_P
 			| ENCODING
@@ -16912,6 +16923,7 @@ unreserved_keyword:
 			| RESET
 			| RESTART
 			| RESTRICT
+			| RESULT
 			| RETURN
 			| RETURNS
 			| REVOKE
@@ -17312,6 +17324,7 @@ bare_label_keyword:
 			| DOMAIN_P
 			| DOUBLE_P
 			| DROP
+			| DYNAMIC
 			| EACH
 			| ELSE
 			| ENABLE_P
@@ -17499,6 +17512,7 @@ bare_label_keyword:
 			| RESET
 			| RESTART
 			| RESTRICT
+			| RESULT
 			| RETURN
 			| RETURNS
 			| REVOKE
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index 3082093d1eab..4653add4a5ea 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -33,6 +33,7 @@
 #include "access/xact.h"
 #include "catalog/pg_type.h"
 #include "commands/async.h"
+#include "commands/defrem.h"
 #include "commands/prepare.h"
 #include "common/pg_prng.h"
 #include "jit/jit.h"
@@ -1072,6 +1073,7 @@ exec_simple_query(const char *query_string)
 		Portal		portal;
 		DestReceiver *receiver;
 		int16		format;
+		ListCell   *lc;

 
 		/*

+		/*
 
+	/*
 
index 52e2db6452be..510403161fb7 100644

index 7b1ae6fdcf0b..4b132861ffeb 100644
index da427f4d4a17..08c1b285d6e8 100644

 
 	/*
 
+	if (prodynres > 0)

 
 
index 1d3ce246c927..5c22262f6b78 100644
index 7caff62af7f3..712facfdbf1f 100644
@@ -2900,6 +2900,7 @@ typedef struct SecLabelStmt
index 957ee18d8498..93faceda3090 100644
 
+	/*
 
 	/*
 
 #endif							/* PORTAL_H */
index 364bad2b882c..f0bfd7d7db5d 100644
@@ -320,10 +320,8 @@ pqParseInput3(PGconn *conn)

 
 
 %%

 
 
+opt_with_return :
+					{
+						$$ = 0;
+					}
+				| K_WITH K_RETURN
+					{

 
 %%

+-- dynamic result sets
+
 
 DROP PROCEDURE ptest1;
 DROP PROCEDURE ptest1s;
 DROP PROCEDURE ptest2;
 
-DROP TABLE cp_test;
 
2.38.1

From b107f416368c37d9f009d73b423748b787db223a Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <[email-masked]>
Date: Tue, 22 Nov 2022 16:31:13 +0100
Subject: [PATCH v6 2/2] WIP: Dynamic result sets extended query tests

This is currently broken due to/since
acb7e4eb6b1c614c68a62fb3a6a5bba1af0a2659.
---
 .../regress/expected/create_procedure.out     | 31 +++++++++++++++++++
 src/test/regress/sql/create_procedure.sql     |  4 +++
 2 files changed, 35 insertions(+)

diff --git a/src/test/regress/expected/create_procedure.out b/src/test/regress/expected/create_procedure.out
index b3802bd7c1db..2ca9c93b110c 100644
--- a/src/test/regress/expected/create_procedure.out
+++ b/src/test/regress/expected/create_procedure.out
@@ -401,6 +401,15 @@ CALL pdrstest1();
  foo | bar
 (2 rows)
 
+CALL pdrstest1() \bind \g
+ a 
+---
+ 1
+ 2
+ 3
+(3 rows)
+
+server sent data ("D" message) without prior row description ("T" message)
 CREATE PROCEDURE pdrstest2()
 LANGUAGE SQL
 DYNAMIC RESULT SETS 1
@@ -414,6 +423,12 @@ CALL pdrstest2();
  1
 (1 row)
 
+CALL pdrstest2() \bind \g
+ a 
+---
+ 1
+(1 row)
+
 CREATE PROCEDURE pdrstest3(INOUT a text)
 LANGUAGE SQL
 DYNAMIC RESULT SETS 1
@@ -434,6 +449,13 @@ CALL pdrstest3('x');
  3
 (3 rows)
 
+CALL pdrstest3($1) \bind 'y' \g
+ a  
+----
+ yy
+(1 row)
+
+server sent data ("D" message) without prior row description ("T" message)
 -- test the nested error handling
 CREATE TABLE cp_test_dummy (a int);
 CREATE PROCEDURE pdrstest4a()
@@ -456,6 +478,15 @@ LINE 2: DECLARE c5a CURSOR WITH RETURN FOR SELECT * FROM cp_test_dum...
 QUERY:  
 DECLARE c5a CURSOR WITH RETURN FOR SELECT * FROM cp_test_dummy;
 
+CONTEXT:  SQL function "pdrstest4a" during startup
+SQL function "pdrstest4b" statement 1
+CALL pdrstest4b() \bind \g
+ERROR:  relation "cp_test_dummy" does not exist
+LINE 2: DECLARE c5a CURSOR WITH RETURN FOR SELECT * FROM cp_test_dum...
+                                                         ^
+QUERY:  
+DECLARE c5a CURSOR WITH RETURN FOR SELECT * FROM cp_test_dummy;
+
 CONTEXT:  SQL function "pdrstest4a" during startup
 SQL function "pdrstest4b" statement 1
 -- cleanup
diff --git a/src/test/regress/sql/create_procedure.sql b/src/test/regress/sql/create_procedure.sql
index c3970726a6e0..97bace7f5958 100644
--- a/src/test/regress/sql/create_procedure.sql
+++ b/src/test/regress/sql/create_procedure.sql
@@ -258,6 +258,7 @@ CREATE PROCEDURE pdrstest1()
 $$;
 
 CALL pdrstest1();
+CALL pdrstest1() \bind \g
 
 CREATE PROCEDURE pdrstest2()
 LANGUAGE SQL
@@ -268,6 +269,7 @@ CREATE PROCEDURE pdrstest2()
 $$;
 
 CALL pdrstest2();
+CALL pdrstest2() \bind \g
 
 CREATE PROCEDURE pdrstest3(INOUT a text)
 LANGUAGE SQL
@@ -278,6 +280,7 @@ CREATE PROCEDURE pdrstest3(INOUT a text)
 $$;
 
 CALL pdrstest3('x');
+CALL pdrstest3($1) \bind 'y' \g
 
 -- test the nested error handling
 CREATE TABLE cp_test_dummy (a int);
@@ -299,6 +302,7 @@ CREATE PROCEDURE pdrstest4b()
 DROP TABLE cp_test_dummy;
 
 CALL pdrstest4b();
+CALL pdrstest4b() \bind \g

 -- cleanup
-- 
2.38.1