public inbox for [email protected]  

help / color / mirror / Atom feed

From: Peter Eisentraut <[email protected]>
To: pgsql-hackers <[email protected]>
Subject: Re: dynamic result sets support in extended query protocol
Date: Tue, 29 Jun 2021 15:39:37 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>

Here is an updated patch with some merge conflicts resolved, to keep it 
fresh.  It's still pending in the commit fest from last time.

My focus right now is to work on the "psql - add SHOW_ALL_RESULTS 
option" patch (https://commitfest.postgresql.org/33/2096/) first, which 
is pretty much a prerequisite to this one.  The attached patch set 
contains a minimal variant of that patch in 0001 and 0002, just to get 
this working, but disregard those for the purposes of code review.

The 0003 patch contains comprehensive documentation and test changes 
that can explain the feature in its current form.

From 4511717c2eb8d90b467b8585b66cafcc7ef9dc7d Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <[email protected]>
Date: Tue, 8 Sep 2020 20:03:05 +0200
Subject: [PATCH v3 1/3] psql: Display multiple result sets

If a query returns multiple result sets, display all of them instead of
only the one that PQexec() returns.

Adjust various regression tests to handle the new additional output.
---
 src/bin/psql/common.c                      | 25 +++++-----
 src/test/regress/expected/copyselect.out   |  5 ++
 src/test/regress/expected/create_table.out | 11 +++--
 src/test/regress/expected/psql.out         |  6 +--
 src/test/regress/expected/sanity_check.out |  1 -
 src/test/regress/expected/transactions.out | 56 ++++++++++++++++++++++
 6 files changed, 82 insertions(+), 22 deletions(-)

diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index 9a00499510..1be1cf3a8a 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -1300,22 +1300,25 @@ SendQuery(const char *query)
 		if (pset.timing)
 			INSTR_TIME_SET_CURRENT(before);
 
-		results = PQexec(pset.db, query);
+		PQsendQuery(pset.db, query);
 
 		/* these operations are included in the timing result: */
 		ResetCancelConn();
-		OK = ProcessResult(&results);
-
-		if (pset.timing)
+		while ((results = PQgetResult(pset.db)))
 		{
-			INSTR_TIME_SET_CURRENT(after);
-			INSTR_TIME_SUBTRACT(after, before);
-			elapsed_msec = INSTR_TIME_GET_MILLISEC(after);
-		}
+			OK = ProcessResult(&results);
+
+			if (pset.timing)
+			{
+				INSTR_TIME_SET_CURRENT(after);
+				INSTR_TIME_SUBTRACT(after, before);
+				elapsed_msec = INSTR_TIME_GET_MILLISEC(after);
+			}
 
-		/* but printing results isn't: */
-		if (OK && results)
-			OK = PrintQueryResults(results);
+			/* but printing results isn't: */
+			if (OK && results)
+				OK = PrintQueryResults(results);
+		}
 	}
 	else
 	{
diff --git a/src/test/regress/expected/copyselect.out b/src/test/regress/expected/copyselect.out
index 72865fe1eb..a13e1b411b 100644
--- a/src/test/regress/expected/copyselect.out
+++ b/src/test/regress/expected/copyselect.out
@@ -136,6 +136,11 @@ copy (select 1) to stdout\; copy (select 2) to stdout\; select 0\; select 3; --
 
 create table test3 (c int);
 select 0\; copy test3 from stdin\; copy test3 from stdin\; select 1; -- 1
+ ?column? 
+----------
+        0
+(1 row)
+
  ?column? 
 ----------
         1
diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out
index ad89dd05c1..17ccce90ee 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -279,12 +279,13 @@ DEALLOCATE select1;
 -- (temporarily hide query, to avoid the long CREATE TABLE stmt)
 \set ECHO none
 INSERT INTO extra_wide_table(firstc, lastc) VALUES('first col', 'last col');
+ERROR:  relation "extra_wide_table" does not exist
+LINE 1: INSERT INTO extra_wide_table(firstc, lastc) VALUES('first co...
+                    ^
 SELECT firstc, lastc FROM extra_wide_table;
-  firstc   |  lastc   
------------+----------
- first col | last col
-(1 row)
-
+ERROR:  relation "extra_wide_table" does not exist
+LINE 1: SELECT firstc, lastc FROM extra_wide_table;
+                                  ^
 -- check that tables with oids cannot be created anymore
 CREATE TABLE withoid() WITH OIDS;
 ERROR:  syntax error at or near "OIDS"
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index 1b2f6bc418..c7f5891c40 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -258,11 +258,7 @@ union all
 select 'drop table gexec_test', 'select ''2000-01-01''::date as party_over'
 \gexec
 select 1 as ones
- ones 
-------
-    1
-(1 row)
-
+ERROR:  DECLARE CURSOR can only be used in transaction blocks
 select x.y, x.y*2 as double from generate_series(1,4) as x(y)
  y | double 
 ---+--------
diff --git a/src/test/regress/expected/sanity_check.out b/src/test/regress/expected/sanity_check.out
index d9ce961be2..bd3f1be90c 100644
--- a/src/test/regress/expected/sanity_check.out
+++ b/src/test/regress/expected/sanity_check.out
@@ -43,7 +43,6 @@ dupindexcols|t
 e_star|f
 emp|f
 equipment_r|f
-extra_wide_table|f
 f_star|f
 fast_emp4000|t
 float4_tbl|f
diff --git a/src/test/regress/expected/transactions.out b/src/test/regress/expected/transactions.out
index 61862d595d..d22027cb86 100644
--- a/src/test/regress/expected/transactions.out
+++ b/src/test/regress/expected/transactions.out
@@ -902,6 +902,16 @@ DROP TABLE abc;
 create temp table i_table (f1 int);
 -- psql will show only the last result in a multi-statement Query
 SELECT 1\; SELECT 2\; SELECT 3;
+ ?column? 
+----------
+        1
+(1 row)
+
+ ?column? 
+----------
+        2
+(1 row)
+
  ?column? 
 ----------
         3
@@ -916,6 +926,12 @@ insert into i_table values(1)\; select * from i_table;
 
 -- 1/0 error will cause rolling back the whole implicit transaction
 insert into i_table values(2)\; select * from i_table\; select 1/0;
+ f1 
+----
+  1
+  2
+(2 rows)
+
 ERROR:  division by zero
 select * from i_table;
  f1 
@@ -935,8 +951,18 @@ WARNING:  there is no transaction in progress
 -- begin converts implicit transaction into a regular one that
 -- can extend past the end of the Query
 select 1\; begin\; insert into i_table values(5);
+ ?column? 
+----------
+        1
+(1 row)
+
 commit;
 select 1\; begin\; insert into i_table values(6);
+ ?column? 
+----------
+        1
+(1 row)
+
 rollback;
 -- commit in implicit-transaction state commits but issues a warning.
 insert into i_table values(7)\; commit\; insert into i_table values(8)\; select 1/0;
@@ -963,22 +989,52 @@ rollback;  -- we are not in a transaction at this point
 WARNING:  there is no transaction in progress
 -- implicit transaction block is still a transaction block, for e.g. VACUUM
 SELECT 1\; VACUUM;
+ ?column? 
+----------
+        1
+(1 row)
+
 ERROR:  VACUUM cannot run inside a transaction block
 SELECT 1\; COMMIT\; VACUUM;
 WARNING:  there is no transaction in progress
+ ?column? 
+----------
+        1
+(1 row)
+
 ERROR:  VACUUM cannot run inside a transaction block
 -- we disallow savepoint-related commands in implicit-transaction state
 SELECT 1\; SAVEPOINT sp;
+ ?column? 
+----------
+        1
+(1 row)
+
 ERROR:  SAVEPOINT can only be used in transaction blocks
 SELECT 1\; COMMIT\; SAVEPOINT sp;
 WARNING:  there is no transaction in progress
+ ?column? 
+----------
+        1
+(1 row)
+
 ERROR:  SAVEPOINT can only be used in transaction blocks
 ROLLBACK TO SAVEPOINT sp\; SELECT 2;
 ERROR:  ROLLBACK TO SAVEPOINT can only be used in transaction blocks
 SELECT 2\; RELEASE SAVEPOINT sp\; SELECT 3;
+ ?column? 
+----------
+        2
+(1 row)
+
 ERROR:  RELEASE SAVEPOINT can only be used in transaction blocks
 -- but this is OK, because the BEGIN converts it to a regular xact
 SELECT 1\; BEGIN\; SAVEPOINT sp\; ROLLBACK TO SAVEPOINT sp\; COMMIT;
+ ?column? 
+----------
+        1
+(1 row)
+
 -- Tests for AND CHAIN in implicit transaction blocks
 SET TRANSACTION READ ONLY\; COMMIT AND CHAIN;  -- error
 ERROR:  COMMIT AND CHAIN can only be used in transaction blocks
-- 
2.32.0


From e80780d786c0885232e4b9564943ab5ea1462caf Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <[email protected]>
Date: Mon, 26 Apr 2021 12:35:05 +0200
Subject: [PATCH v3 2/3] XXX make tests pass for psql changes

---
 .../expected/pg_stat_statements.out           | 20 +++++++++++++++++++
 src/test/modules/test_extensions/Makefile     |  2 +-
 src/test/recovery/t/013_crash_restart.pl      |  3 +++
 3 files changed, 24 insertions(+), 1 deletion(-)

diff --git a/contrib/pg_stat_statements/expected/pg_stat_statements.out b/contrib/pg_stat_statements/expected/pg_stat_statements.out
index 40b5109b55..cd1a569a73 100644
--- a/contrib/pg_stat_statements/expected/pg_stat_statements.out
+++ b/contrib/pg_stat_statements/expected/pg_stat_statements.out
@@ -50,8 +50,28 @@ BEGIN \;
 SELECT 2.0 AS "float" \;
 SELECT 'world' AS "text" \;
 COMMIT;
+ float 
+-------
+   2.0
+(1 row)
+
+ text  
+-------
+ world
+(1 row)
+
 -- compound with empty statements and spurious leading spacing
 \;\;   SELECT 3 + 3 \;\;\;   SELECT ' ' || ' !' \;\;   SELECT 1 + 4 \;;
+ ?column? 
+----------
+        6
+(1 row)
+
+ ?column? 
+----------
+   !
+(1 row)
+
  ?column? 
 ----------
         5
diff --git a/src/test/modules/test_extensions/Makefile b/src/test/modules/test_extensions/Makefile
index 77ee4d5d9e..9dc6b9d428 100644
--- a/src/test/modules/test_extensions/Makefile
+++ b/src/test/modules/test_extensions/Makefile
@@ -12,7 +12,7 @@ DATA = test_ext1--1.0.sql test_ext2--1.0.sql test_ext3--1.0.sql \
        test_ext_cyclic1--1.0.sql test_ext_cyclic2--1.0.sql \
        test_ext_evttrig--1.0.sql test_ext_evttrig--1.0--2.0.sql
 
-REGRESS = test_extensions test_extdepend
+REGRESS = test_extensions #test_extdepend
 
 ifdef USE_PGXS
 PG_CONFIG = pg_config
diff --git a/src/test/recovery/t/013_crash_restart.pl b/src/test/recovery/t/013_crash_restart.pl
index 868a50b33d..9de5074a63 100644
--- a/src/test/recovery/t/013_crash_restart.pl
+++ b/src/test/recovery/t/013_crash_restart.pl
@@ -189,6 +189,8 @@
 
 # Check that psql sees the server as being terminated. No WARNING,
 # because signal handlers aren't being run on SIGKILL.
+ TODO: {
+	 local $TODO = 'FIXME';
 $killme_stdin .= q[
 SELECT 1;
 ];
@@ -199,6 +201,7 @@
 	),
 	"psql query died successfully after SIGKILL");
 $killme->finish;
+}
 
 # Wait till server restarts - we should get the WARNING here, but
 # sometimes the server is unable to send that, if interrupted while
-- 
2.32.0


From 99536cb7998efe4379f2ec2ffb82e62bcca0c231 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <[email protected]>
Date: Tue, 29 Jun 2021 14:28:33 +0200
Subject: [PATCH v3 3/3] 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 protected]
---
 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 f517a7d4af..7d600bcf6b 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -5869,6 +5869,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 4100198252..7f7498eeff 100644
--- a/doc/src/sgml/information_schema.sgml
+++ b/doc/src/sgml/information_schema.sgml
@@ -5884,7 +5884,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 4cd4bcba80..203f65b788 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -3117,7 +3117,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.)
@@ -3125,6 +3125,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
@@ -3183,7 +3187,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>
@@ -3201,8 +3205,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>
@@ -3579,6 +3584,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 01e87617f4..d05e10be1c 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 033fda92ee..c9fa7c5057 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
@@ -152,6 +153,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 03a14c8684..1c99b00eef 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 bbbd335bd0..a6ff2567ea 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 11d9dd60c2..2021dc62bc 100644
--- a/src/backend/catalog/information_schema.sql
+++ b/src/backend/catalog/information_schema.sql
@@ -1591,7 +1591,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 1f63d8081b..4549f17cad 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 1454d2fb67..963021d535 100644
--- a/src/backend/catalog/pg_proc.c
+++ b/src/backend/catalog/pg_proc.c
@@ -93,7 +93,8 @@ ProcedureCreate(const char *procedureName,
 				Datum proconfig,
 				Oid prosupport,
 				float4 procost,
-				float4 prorows)
+				float4 prorows,
+				int dynres)
 {
 	Oid			retval;
 	int			parameterCount;
@@ -312,6 +313,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 9f424216e2..c9670d87c0 100644
--- a/src/backend/catalog/sql_features.txt
+++ b/src/backend/catalog/sql_features.txt
@@ -485,7 +485,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 736d04780a..63cc4a89f5 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/syscache.h"
 #include "utils/typcache.h"
@@ -516,7 +517,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)
 	{
@@ -592,6 +594,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;
 
@@ -605,6 +616,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),
@@ -741,7 +759,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;
@@ -757,6 +776,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)
 	{
@@ -814,7 +834,8 @@ compute_function_attributes(ParseState *pstate,
 										  &cost_item,
 										  &rows_item,
 										  &support_item,
-										  &parallel_item))
+										  &parallel_item,
+										  &dynres_item))
 		{
 			/* recognized common option */
 			continue;
@@ -862,6 +883,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 */
+	}
 }
 
 
@@ -1073,6 +1099,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,
@@ -1097,6 +1124,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,
@@ -1106,7 +1134,7 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt *stmt)
 								&isWindowFunc, &volatility,
 								&isStrict, &security, &isLeakProof,
 								&proconfig, &procost, &prorows,
-								&prosupport, &parallel);
+								&prosupport, &parallel, &dynres);
 
 	if (!language)
 	{
@@ -1310,7 +1338,8 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt *stmt)
 						   PointerGetDatum(proconfig),
 						   prosupport,
 						   procost,
-						   prorows);
+						   prorows,
+						   dynres);
 }
 
 /*
@@ -1385,6 +1414,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);
@@ -1428,7 +1458,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);
 	}
 
@@ -1524,6 +1555,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);
@@ -2167,6 +2200,17 @@ 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);
+}
+
 /*
  * Execute CALL statement
  *
@@ -2206,6 +2250,7 @@ ExecuteCallStmt(CallStmt *stmt, ParamListInfo params, bool atomic, DestReceiver
 	AclResult	aclresult;
 	FmgrInfo	flinfo;
 	CallContext *callcontext;
+	int			prodynres;
 	EState	   *estate;
 	ExprContext *econtext;
 	HeapTuple	tp;
@@ -2246,6 +2291,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() */
@@ -2291,7 +2338,18 @@ ExecuteCallStmt(CallStmt *stmt, ParamListInfo params, bool atomic, DestReceiver
 	}
 
 	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);
 
 	if (fexpr->funcresulttype == VOIDOID)
@@ -2354,6 +2412,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 3ea30bcbc9..d511fb88d4 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 58ec65c6af..a1b8423a5e 100644
--- a/src/backend/commands/typecmds.c
+++ b/src/backend/commands/typecmds.c
@@ -1791,7 +1791,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
@@ -1856,7 +1857,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
@@ -1900,7 +1902,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);
@@ -1941,7 +1944,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 eb24195438..3931ddb3af 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -655,7 +655,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
@@ -700,7 +700,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
@@ -7898,6 +7898,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:
@@ -11277,6 +11281,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; }
 		;
 
 /*****************************************************************************
@@ -15543,6 +15553,7 @@ unreserved_keyword:
 			| DOMAIN_P
 			| DOUBLE_P
 			| DROP
+			| DYNAMIC
 			| EACH
 			| ENABLE_P
 			| ENCODING
@@ -15685,6 +15696,7 @@ unreserved_keyword:
 			| RESET
 			| RESTART
 			| RESTRICT
+			| RESULT
 			| RETURN
 			| RETURNS
 			| REVOKE
@@ -16084,6 +16096,7 @@ bare_label_keyword:
 			| DOMAIN_P
 			| DOUBLE_P
 			| DROP
+			| DYNAMIC
 			| EACH
 			| ELSE
 			| ENABLE_P
@@ -16268,6 +16281,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 8cea10c901..5bec54fde0 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"
@@ -1048,6 +1049,7 @@ exec_simple_query(const char *query_string)
 		Portal		portal;
 		DestReceiver *receiver;
 		int16		format;
+		ListCell   *lc;
 
 		pgstat_report_query_id(0, true);
 
@@ -1209,7 +1211,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,
@@ -1219,10 +1221,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)
 		{
 			/*
@@ -2050,6 +2076,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;
@@ -2202,6 +2229,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 ed43920264..258d7a118e 100644
--- a/src/backend/tcop/pquery.c
+++ b/src/backend/tcop/pquery.c
@@ -636,6 +636,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)
 	{
@@ -644,12 +646,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 9874a77805..a4504b6436 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 5c30e141f5..faa31c5097 100644
--- a/src/backend/utils/mmgr/portalmem.c
+++ b/src/backend/utils/mmgr/portalmem.c
@@ -1334,3 +1334,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 321152151d..b3af6bf990 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -12101,6 +12101,7 @@ dumpFunc(Archive *fout, const FuncInfo *finfo)
 	char	   *prorows;
 	char	   *prosupport;
 	char	   *proparallel;
+	int			prodynres;
 	char	   *lanname;
 	char	   *rettypename;
 	int			nallargs;
@@ -12202,10 +12203,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 >= 150000)
+		appendPQExpBufferStr(query,
+							 "prodynres\n");
+	else
+		appendPQExpBufferStr(query,
+							 "0 AS prodynres\n");
 
 	appendPQExpBuffer(query,
 					  "FROM pg_catalog.pg_proc p, pg_catalog.pg_language l\n"
@@ -12256,6 +12264,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"));
 
 	/*
@@ -12436,6 +12445,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 b33b8b0134..a831959339 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 42bf1c7519..2ba218863b 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(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 def9651b34..1c035e5d0f 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2808,6 +2808,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 f836acf876..9ef6dfdec4 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)
@@ -350,6 +351,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 2e5bbdd0ec..4bb7096ce2 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;
 
 	/*
 	 * Outermost ActiveSnapshot for execution of the portal's queries.  For
@@ -246,5 +258,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 2e83305348..7977b2a485 100644
--- a/src/interfaces/libpq/fe-protocol3.c
+++ b/src/interfaces/libpq/fe-protocol3.c
@@ -337,10 +337,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 7b156f3489..559534327a 100644
--- a/src/pl/plpgsql/src/expected/plpgsql_call.out
+++ b/src/pl/plpgsql/src/expected/plpgsql_call.out
@@ -454,3 +454,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 96bb77e0b1..e46d381f1b 100644
--- a/src/pl/plpgsql/src/pl_exec.c
+++ b/src/pl/plpgsql/src/pl_exec.c
@@ -4712,6 +4712,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
 	 */
diff --git a/src/pl/plpgsql/src/pl_gram.y b/src/pl/plpgsql/src/pl_gram.y
index 3fcca43b90..0b1e7ac770 100644
--- a/src/pl/plpgsql/src/pl_gram.y
+++ b/src/pl/plpgsql/src/pl_gram.y
@@ -214,7 +214,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
@@ -353,6 +353,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
 
 %%
 
@@ -530,7 +532,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
 					{
@@ -574,12 +576,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;
 					}
 				;
 
@@ -597,6 +599,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();
@@ -2000,6 +2016,10 @@ stmt_execsql	: K_IMPORT
 					{
 						$$ = make_execsql_stmt(K_INSERT, @1);
 					}
+				| K_WITH
+					{
+						$$ = make_execsql_stmt(K_WITH, @1);
+					}
 				| T_WORD
 					{
 						int			tok;
@@ -2122,6 +2142,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\"");
 
@@ -2575,6 +2619,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 fcb34f7c7f..cec7437c19 100644
--- a/src/pl/plpgsql/src/pl_unreserved_kwlist.h
+++ b/src/pl/plpgsql/src/pl_unreserved_kwlist.h
@@ -108,3 +108,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 8108d05060..d6a0945298 100644
--- a/src/pl/plpgsql/src/sql/plpgsql_call.sql
+++ b/src/pl/plpgsql/src/sql/plpgsql_call.sql
@@ -424,3 +424,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 46c827f979..b3802bd7c1 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 75cc0fcf2a..c3970726a6 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;
-- 
2.32.0



Attachments:

  [text/plain] v3-0001-psql-Display-multiple-result-sets.patch (7.0K, ../[email protected]/2-v3-0001-psql-Display-multiple-result-sets.patch)
  download | inline diff:
From 4511717c2eb8d90b467b8585b66cafcc7ef9dc7d Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <[email protected]>
Date: Tue, 8 Sep 2020 20:03:05 +0200
Subject: [PATCH v3 1/3] psql: Display multiple result sets

If a query returns multiple result sets, display all of them instead of
only the one that PQexec() returns.

Adjust various regression tests to handle the new additional output.
---
 src/bin/psql/common.c                      | 25 +++++-----
 src/test/regress/expected/copyselect.out   |  5 ++
 src/test/regress/expected/create_table.out | 11 +++--
 src/test/regress/expected/psql.out         |  6 +--
 src/test/regress/expected/sanity_check.out |  1 -
 src/test/regress/expected/transactions.out | 56 ++++++++++++++++++++++
 6 files changed, 82 insertions(+), 22 deletions(-)

diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index 9a00499510..1be1cf3a8a 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -1300,22 +1300,25 @@ SendQuery(const char *query)
 		if (pset.timing)
 			INSTR_TIME_SET_CURRENT(before);
 
-		results = PQexec(pset.db, query);
+		PQsendQuery(pset.db, query);
 
 		/* these operations are included in the timing result: */
 		ResetCancelConn();
-		OK = ProcessResult(&results);
-
-		if (pset.timing)
+		while ((results = PQgetResult(pset.db)))
 		{
-			INSTR_TIME_SET_CURRENT(after);
-			INSTR_TIME_SUBTRACT(after, before);
-			elapsed_msec = INSTR_TIME_GET_MILLISEC(after);
-		}
+			OK = ProcessResult(&results);
+
+			if (pset.timing)
+			{
+				INSTR_TIME_SET_CURRENT(after);
+				INSTR_TIME_SUBTRACT(after, before);
+				elapsed_msec = INSTR_TIME_GET_MILLISEC(after);
+			}
 
-		/* but printing results isn't: */
-		if (OK && results)
-			OK = PrintQueryResults(results);
+			/* but printing results isn't: */
+			if (OK && results)
+				OK = PrintQueryResults(results);
+		}
 	}
 	else
 	{
diff --git a/src/test/regress/expected/copyselect.out b/src/test/regress/expected/copyselect.out
index 72865fe1eb..a13e1b411b 100644
--- a/src/test/regress/expected/copyselect.out
+++ b/src/test/regress/expected/copyselect.out
@@ -136,6 +136,11 @@ copy (select 1) to stdout\; copy (select 2) to stdout\; select 0\; select 3; --
 
 create table test3 (c int);
 select 0\; copy test3 from stdin\; copy test3 from stdin\; select 1; -- 1
+ ?column? 
+----------
+        0
+(1 row)
+
  ?column? 
 ----------
         1
diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out
index ad89dd05c1..17ccce90ee 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -279,12 +279,13 @@ DEALLOCATE select1;
 -- (temporarily hide query, to avoid the long CREATE TABLE stmt)
 \set ECHO none
 INSERT INTO extra_wide_table(firstc, lastc) VALUES('first col', 'last col');
+ERROR:  relation "extra_wide_table" does not exist
+LINE 1: INSERT INTO extra_wide_table(firstc, lastc) VALUES('first co...
+                    ^
 SELECT firstc, lastc FROM extra_wide_table;
-  firstc   |  lastc   
------------+----------
- first col | last col
-(1 row)
-
+ERROR:  relation "extra_wide_table" does not exist
+LINE 1: SELECT firstc, lastc FROM extra_wide_table;
+                                  ^
 -- check that tables with oids cannot be created anymore
 CREATE TABLE withoid() WITH OIDS;
 ERROR:  syntax error at or near "OIDS"
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index 1b2f6bc418..c7f5891c40 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -258,11 +258,7 @@ union all
 select 'drop table gexec_test', 'select ''2000-01-01''::date as party_over'
 \gexec
 select 1 as ones
- ones 
-------
-    1
-(1 row)
-
+ERROR:  DECLARE CURSOR can only be used in transaction blocks
 select x.y, x.y*2 as double from generate_series(1,4) as x(y)
  y | double 
 ---+--------
diff --git a/src/test/regress/expected/sanity_check.out b/src/test/regress/expected/sanity_check.out
index d9ce961be2..bd3f1be90c 100644
--- a/src/test/regress/expected/sanity_check.out
+++ b/src/test/regress/expected/sanity_check.out
@@ -43,7 +43,6 @@ dupindexcols|t
 e_star|f
 emp|f
 equipment_r|f
-extra_wide_table|f
 f_star|f
 fast_emp4000|t
 float4_tbl|f
diff --git a/src/test/regress/expected/transactions.out b/src/test/regress/expected/transactions.out
index 61862d595d..d22027cb86 100644
--- a/src/test/regress/expected/transactions.out
+++ b/src/test/regress/expected/transactions.out
@@ -902,6 +902,16 @@ DROP TABLE abc;
 create temp table i_table (f1 int);
 -- psql will show only the last result in a multi-statement Query
 SELECT 1\; SELECT 2\; SELECT 3;
+ ?column? 
+----------
+        1
+(1 row)
+
+ ?column? 
+----------
+        2
+(1 row)
+
  ?column? 
 ----------
         3
@@ -916,6 +926,12 @@ insert into i_table values(1)\; select * from i_table;
 
 -- 1/0 error will cause rolling back the whole implicit transaction
 insert into i_table values(2)\; select * from i_table\; select 1/0;
+ f1 
+----
+  1
+  2
+(2 rows)
+
 ERROR:  division by zero
 select * from i_table;
  f1 
@@ -935,8 +951,18 @@ WARNING:  there is no transaction in progress
 -- begin converts implicit transaction into a regular one that
 -- can extend past the end of the Query
 select 1\; begin\; insert into i_table values(5);
+ ?column? 
+----------
+        1
+(1 row)
+
 commit;
 select 1\; begin\; insert into i_table values(6);
+ ?column? 
+----------
+        1
+(1 row)
+
 rollback;
 -- commit in implicit-transaction state commits but issues a warning.
 insert into i_table values(7)\; commit\; insert into i_table values(8)\; select 1/0;
@@ -963,22 +989,52 @@ rollback;  -- we are not in a transaction at this point
 WARNING:  there is no transaction in progress
 -- implicit transaction block is still a transaction block, for e.g. VACUUM
 SELECT 1\; VACUUM;
+ ?column? 
+----------
+        1
+(1 row)
+
 ERROR:  VACUUM cannot run inside a transaction block
 SELECT 1\; COMMIT\; VACUUM;
 WARNING:  there is no transaction in progress
+ ?column? 
+----------
+        1
+(1 row)
+
 ERROR:  VACUUM cannot run inside a transaction block
 -- we disallow savepoint-related commands in implicit-transaction state
 SELECT 1\; SAVEPOINT sp;
+ ?column? 
+----------
+        1
+(1 row)
+
 ERROR:  SAVEPOINT can only be used in transaction blocks
 SELECT 1\; COMMIT\; SAVEPOINT sp;
 WARNING:  there is no transaction in progress
+ ?column? 
+----------
+        1
+(1 row)
+
 ERROR:  SAVEPOINT can only be used in transaction blocks
 ROLLBACK TO SAVEPOINT sp\; SELECT 2;
 ERROR:  ROLLBACK TO SAVEPOINT can only be used in transaction blocks
 SELECT 2\; RELEASE SAVEPOINT sp\; SELECT 3;
+ ?column? 
+----------
+        2
+(1 row)
+
 ERROR:  RELEASE SAVEPOINT can only be used in transaction blocks
 -- but this is OK, because the BEGIN converts it to a regular xact
 SELECT 1\; BEGIN\; SAVEPOINT sp\; ROLLBACK TO SAVEPOINT sp\; COMMIT;
+ ?column? 
+----------
+        1
+(1 row)
+
 -- Tests for AND CHAIN in implicit transaction blocks
 SET TRANSACTION READ ONLY\; COMMIT AND CHAIN;  -- error
 ERROR:  COMMIT AND CHAIN can only be used in transaction blocks
-- 
2.32.0



  [text/plain] v3-0002-XXX-make-tests-pass-for-psql-changes.patch (2.4K, ../[email protected]/3-v3-0002-XXX-make-tests-pass-for-psql-changes.patch)
  download | inline diff:
From e80780d786c0885232e4b9564943ab5ea1462caf Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <[email protected]>
Date: Mon, 26 Apr 2021 12:35:05 +0200
Subject: [PATCH v3 2/3] XXX make tests pass for psql changes

---
 .../expected/pg_stat_statements.out           | 20 +++++++++++++++++++
 src/test/modules/test_extensions/Makefile     |  2 +-
 src/test/recovery/t/013_crash_restart.pl      |  3 +++
 3 files changed, 24 insertions(+), 1 deletion(-)

diff --git a/contrib/pg_stat_statements/expected/pg_stat_statements.out b/contrib/pg_stat_statements/expected/pg_stat_statements.out
index 40b5109b55..cd1a569a73 100644
--- a/contrib/pg_stat_statements/expected/pg_stat_statements.out
+++ b/contrib/pg_stat_statements/expected/pg_stat_statements.out
@@ -50,8 +50,28 @@ BEGIN \;
 SELECT 2.0 AS "float" \;
 SELECT 'world' AS "text" \;
 COMMIT;
+ float 
+-------
+   2.0
+(1 row)
+
+ text  
+-------
+ world
+(1 row)
+
 -- compound with empty statements and spurious leading spacing
 \;\;   SELECT 3 + 3 \;\;\;   SELECT ' ' || ' !' \;\;   SELECT 1 + 4 \;;
+ ?column? 
+----------
+        6
+(1 row)
+
+ ?column? 
+----------
+   !
+(1 row)
+
  ?column? 
 ----------
         5
diff --git a/src/test/modules/test_extensions/Makefile b/src/test/modules/test_extensions/Makefile
index 77ee4d5d9e..9dc6b9d428 100644
--- a/src/test/modules/test_extensions/Makefile
+++ b/src/test/modules/test_extensions/Makefile
@@ -12,7 +12,7 @@ DATA = test_ext1--1.0.sql test_ext2--1.0.sql test_ext3--1.0.sql \
        test_ext_cyclic1--1.0.sql test_ext_cyclic2--1.0.sql \
        test_ext_evttrig--1.0.sql test_ext_evttrig--1.0--2.0.sql
 
-REGRESS = test_extensions test_extdepend
+REGRESS = test_extensions #test_extdepend
 
 ifdef USE_PGXS
 PG_CONFIG = pg_config
diff --git a/src/test/recovery/t/013_crash_restart.pl b/src/test/recovery/t/013_crash_restart.pl
index 868a50b33d..9de5074a63 100644
--- a/src/test/recovery/t/013_crash_restart.pl
+++ b/src/test/recovery/t/013_crash_restart.pl
@@ -189,6 +189,8 @@
 
 # Check that psql sees the server as being terminated. No WARNING,
 # because signal handlers aren't being run on SIGKILL.
+ TODO: {
+	 local $TODO = 'FIXME';
 $killme_stdin .= q[
 SELECT 1;
 ];
@@ -199,6 +201,7 @@
 	),
 	"psql query died successfully after SIGKILL");
 $killme->finish;
+}
 
 # Wait till server restarts - we should get the WARNING here, but
 # sometimes the server is unable to send that, if interrupted while
-- 
2.32.0



  [text/plain] v3-0003-Dynamic-result-sets-from-procedures.patch (52.4K, ../[email protected]/4-v3-0003-Dynamic-result-sets-from-procedures.patch)
  download | inline diff:
From 99536cb7998efe4379f2ec2ffb82e62bcca0c231 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <[email protected]>
Date: Tue, 29 Jun 2021 14:28:33 +0200
Subject: [PATCH v3 3/3] 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 protected]
---
 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 f517a7d4af..7d600bcf6b 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -5869,6 +5869,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 4100198252..7f7498eeff 100644
--- a/doc/src/sgml/information_schema.sgml
+++ b/doc/src/sgml/information_schema.sgml
@@ -5884,7 +5884,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 4cd4bcba80..203f65b788 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -3117,7 +3117,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.)
@@ -3125,6 +3125,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
@@ -3183,7 +3187,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>
@@ -3201,8 +3205,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>
@@ -3579,6 +3584,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 01e87617f4..d05e10be1c 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 033fda92ee..c9fa7c5057 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
@@ -152,6 +153,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 03a14c8684..1c99b00eef 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 bbbd335bd0..a6ff2567ea 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 11d9dd60c2..2021dc62bc 100644
--- a/src/backend/catalog/information_schema.sql
+++ b/src/backend/catalog/information_schema.sql
@@ -1591,7 +1591,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 1f63d8081b..4549f17cad 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 1454d2fb67..963021d535 100644
--- a/src/backend/catalog/pg_proc.c
+++ b/src/backend/catalog/pg_proc.c
@@ -93,7 +93,8 @@ ProcedureCreate(const char *procedureName,
 				Datum proconfig,
 				Oid prosupport,
 				float4 procost,
-				float4 prorows)
+				float4 prorows,
+				int dynres)
 {
 	Oid			retval;
 	int			parameterCount;
@@ -312,6 +313,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 9f424216e2..c9670d87c0 100644
--- a/src/backend/catalog/sql_features.txt
+++ b/src/backend/catalog/sql_features.txt
@@ -485,7 +485,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 736d04780a..63cc4a89f5 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/syscache.h"
 #include "utils/typcache.h"
@@ -516,7 +517,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)
 	{
@@ -592,6 +594,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;
 
@@ -605,6 +616,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),
@@ -741,7 +759,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;
@@ -757,6 +776,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)
 	{
@@ -814,7 +834,8 @@ compute_function_attributes(ParseState *pstate,
 										  &cost_item,
 										  &rows_item,
 										  &support_item,
-										  &parallel_item))
+										  &parallel_item,
+										  &dynres_item))
 		{
 			/* recognized common option */
 			continue;
@@ -862,6 +883,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 */
+	}
 }
 
 
@@ -1073,6 +1099,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,
@@ -1097,6 +1124,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,
@@ -1106,7 +1134,7 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt *stmt)
 								&isWindowFunc, &volatility,
 								&isStrict, &security, &isLeakProof,
 								&proconfig, &procost, &prorows,
-								&prosupport, &parallel);
+								&prosupport, &parallel, &dynres);
 
 	if (!language)
 	{
@@ -1310,7 +1338,8 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt *stmt)
 						   PointerGetDatum(proconfig),
 						   prosupport,
 						   procost,
-						   prorows);
+						   prorows,
+						   dynres);
 }
 
 /*
@@ -1385,6 +1414,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);
@@ -1428,7 +1458,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);
 	}
 
@@ -1524,6 +1555,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);
@@ -2167,6 +2200,17 @@ 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);
+}
+
 /*
  * Execute CALL statement
  *
@@ -2206,6 +2250,7 @@ ExecuteCallStmt(CallStmt *stmt, ParamListInfo params, bool atomic, DestReceiver
 	AclResult	aclresult;
 	FmgrInfo	flinfo;
 	CallContext *callcontext;
+	int			prodynres;
 	EState	   *estate;
 	ExprContext *econtext;
 	HeapTuple	tp;
@@ -2246,6 +2291,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() */
@@ -2291,7 +2338,18 @@ ExecuteCallStmt(CallStmt *stmt, ParamListInfo params, bool atomic, DestReceiver
 	}
 
 	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);
 
 	if (fexpr->funcresulttype == VOIDOID)
@@ -2354,6 +2412,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 3ea30bcbc9..d511fb88d4 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 58ec65c6af..a1b8423a5e 100644
--- a/src/backend/commands/typecmds.c
+++ b/src/backend/commands/typecmds.c
@@ -1791,7 +1791,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
@@ -1856,7 +1857,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
@@ -1900,7 +1902,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);
@@ -1941,7 +1944,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 eb24195438..3931ddb3af 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -655,7 +655,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
@@ -700,7 +700,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
@@ -7898,6 +7898,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:
@@ -11277,6 +11281,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; }
 		;
 
 /*****************************************************************************
@@ -15543,6 +15553,7 @@ unreserved_keyword:
 			| DOMAIN_P
 			| DOUBLE_P
 			| DROP
+			| DYNAMIC
 			| EACH
 			| ENABLE_P
 			| ENCODING
@@ -15685,6 +15696,7 @@ unreserved_keyword:
 			| RESET
 			| RESTART
 			| RESTRICT
+			| RESULT
 			| RETURN
 			| RETURNS
 			| REVOKE
@@ -16084,6 +16096,7 @@ bare_label_keyword:
 			| DOMAIN_P
 			| DOUBLE_P
 			| DROP
+			| DYNAMIC
 			| EACH
 			| ELSE
 			| ENABLE_P
@@ -16268,6 +16281,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 8cea10c901..5bec54fde0 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"
@@ -1048,6 +1049,7 @@ exec_simple_query(const char *query_string)
 		Portal		portal;
 		DestReceiver *receiver;
 		int16		format;
+		ListCell   *lc;
 
 		pgstat_report_query_id(0, true);
 
@@ -1209,7 +1211,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,
@@ -1219,10 +1221,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)
 		{
 			/*
@@ -2050,6 +2076,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;
@@ -2202,6 +2229,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 ed43920264..258d7a118e 100644
--- a/src/backend/tcop/pquery.c
+++ b/src/backend/tcop/pquery.c
@@ -636,6 +636,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)
 	{
@@ -644,12 +646,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 9874a77805..a4504b6436 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 5c30e141f5..faa31c5097 100644
--- a/src/backend/utils/mmgr/portalmem.c
+++ b/src/backend/utils/mmgr/portalmem.c
@@ -1334,3 +1334,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 321152151d..b3af6bf990 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -12101,6 +12101,7 @@ dumpFunc(Archive *fout, const FuncInfo *finfo)
 	char	   *prorows;
 	char	   *prosupport;
 	char	   *proparallel;
+	int			prodynres;
 	char	   *lanname;
 	char	   *rettypename;
 	int			nallargs;
@@ -12202,10 +12203,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 >= 150000)
+		appendPQExpBufferStr(query,
+							 "prodynres\n");
+	else
+		appendPQExpBufferStr(query,
+							 "0 AS prodynres\n");
 
 	appendPQExpBuffer(query,
 					  "FROM pg_catalog.pg_proc p, pg_catalog.pg_language l\n"
@@ -12256,6 +12264,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"));
 
 	/*
@@ -12436,6 +12445,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 b33b8b0134..a831959339 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 42bf1c7519..2ba218863b 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(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 def9651b34..1c035e5d0f 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2808,6 +2808,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 f836acf876..9ef6dfdec4 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)
@@ -350,6 +351,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 2e5bbdd0ec..4bb7096ce2 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;
 
 	/*
 	 * Outermost ActiveSnapshot for execution of the portal's queries.  For
@@ -246,5 +258,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 2e83305348..7977b2a485 100644
--- a/src/interfaces/libpq/fe-protocol3.c
+++ b/src/interfaces/libpq/fe-protocol3.c
@@ -337,10 +337,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 7b156f3489..559534327a 100644
--- a/src/pl/plpgsql/src/expected/plpgsql_call.out
+++ b/src/pl/plpgsql/src/expected/plpgsql_call.out
@@ -454,3 +454,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 96bb77e0b1..e46d381f1b 100644
--- a/src/pl/plpgsql/src/pl_exec.c
+++ b/src/pl/plpgsql/src/pl_exec.c
@@ -4712,6 +4712,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
 	 */
diff --git a/src/pl/plpgsql/src/pl_gram.y b/src/pl/plpgsql/src/pl_gram.y
index 3fcca43b90..0b1e7ac770 100644
--- a/src/pl/plpgsql/src/pl_gram.y
+++ b/src/pl/plpgsql/src/pl_gram.y
@@ -214,7 +214,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
@@ -353,6 +353,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
 
 %%
 
@@ -530,7 +532,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
 					{
@@ -574,12 +576,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;
 					}
 				;
 
@@ -597,6 +599,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();
@@ -2000,6 +2016,10 @@ stmt_execsql	: K_IMPORT
 					{
 						$$ = make_execsql_stmt(K_INSERT, @1);
 					}
+				| K_WITH
+					{
+						$$ = make_execsql_stmt(K_WITH, @1);
+					}
 				| T_WORD
 					{
 						int			tok;
@@ -2122,6 +2142,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\"");
 
@@ -2575,6 +2619,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 fcb34f7c7f..cec7437c19 100644
--- a/src/pl/plpgsql/src/pl_unreserved_kwlist.h
+++ b/src/pl/plpgsql/src/pl_unreserved_kwlist.h
@@ -108,3 +108,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 8108d05060..d6a0945298 100644
--- a/src/pl/plpgsql/src/sql/plpgsql_call.sql
+++ b/src/pl/plpgsql/src/sql/plpgsql_call.sql
@@ -424,3 +424,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 46c827f979..b3802bd7c1 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 75cc0fcf2a..c3970726a6 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;
-- 
2.32.0

view thread (30+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected]
  Subject: Re: dynamic result sets support in extended query protocol
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox