[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: [HACKERS] Dynamic result sets from procedures
From: Peter Eisentraut <peter.eisentraut () 2ndquadrant ! com>
Date: 2017-10-31 21:08:47
Message-ID: 4580ff7b-d610-eaeb-e06f-4d686896b93b () 2ndquadrant ! com
[Download RAW message or body]
This patch is more of a demo of what could be done, not my primary
focus, but if there is interest and some assistance, maybe we can make
something out of it. This patch also goes on top of "SQL procedures"
version 1.
The purpose is to return multiple result sets from a procedure. This
is, I think, a common request when coming from MS SQL and DB2. MS SQL
has a completely different procedure syntax, but this proposal is
compatible with DB2, which as usual was the model for the SQL standard.
So this is what it can do:
CREATE PROCEDURE pdrstest1()
LANGUAGE SQL
AS $$
DECLARE c1 CURSOR WITH RETURN FOR SELECT * FROM cp_test2;
DECLARE c2 CURSOR WITH RETURN FOR SELECT * FROM cp_test3;
$$;
CALL pdrstest1();
and that returns those two result sets to the client.
That's all it does for now. Things get more complex when you consider
nested calls. The SQL standard describes additional facilities how an
outer procedure can accept a called procedure's result sets, or not. In
the thread on transaction control, I mentioned that we might need some
kind of procedure call stack. Something like that would be needed here
as well. There are also probably some namespacing issues around the
cursors that need more investigation.
A more mundane issue is how we get psql to print multiple result sets.
I have included here a patch that does that, and you can see that new
result sets start popping up in the regression tests already. There is
also one need error that needs further investigation.
We need to think about how the \timing option should work in such
scenarios. Right now it does
start timer
run query
fetch result
stop timer
print result
If we had multiple result sets, the most natural flow would be
start timer
run query
while result sets
fetch result
print result
stop timer
print time
but that would include the printing time in the total time, which the
current code explicitly does not. We could also temporarily save the
result sets, like
start timer
run query
while result sets
fetch result
stop timer
foreach result set
print result
but that would have a lot more overhead, potentially.
Thoughts?
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
["v1-0001-psql-Display-multiple-result-sets.patch" (text/plain)]
From 2e5d50cb39b926b29a6081f2387b95621357a4a0 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter_e@gmx.net>
Date: Thu, 19 Oct 2017 08:18:47 -0400
Subject: [PATCH v1 1/2] 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/psql.out | 6 +---
src/test/regress/expected/transactions.out | 56 ++++++++++++++++++++++++++++++
4 files changed, 76 insertions(+), 16 deletions(-)
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index 9b59ee840b..2b6bd56e12 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -1390,22 +1390,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/psql.out b/src/test/regress/expected/psql.out
index 3818cfea7e..5ee3fd8b71 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -232,11 +232,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/transactions.out b/src/test/regress/expected/transactions.out
index a7fdcf45fd..eb78a8f551 100644
--- a/src/test/regress/expected/transactions.out
+++ b/src/test/regress/expected/transactions.out
@@ -666,6 +666,16 @@ DROP FUNCTION invert(x float8);
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
@@ -680,6 +690,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
@@ -699,8 +715,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;
@@ -727,22 +753,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)
+
-- Test for successful cleanup of an aborted transaction at session exit.
-- THIS MUST BE THE LAST TEST IN THIS FILE.
begin;
base-commit: 5f64f68a36bb9f34bf91eac2628902227bd24275
--
2.14.3
["v1-0002-Dynamic-result-sets-from-procedures.patch" (text/plain)]
From bfc77c2d3cd0295ca52be54bd856881f4fdca74b Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter_e@gmx.net>
Date: Thu, 19 Oct 2017 08:21:12 -0400
Subject: [PATCH v1 2/2] 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.
---
doc/src/sgml/ref/declare.sgml | 34 ++++++++++++++++-
src/backend/commands/functioncmds.c | 17 ++++++++-
src/backend/commands/portalcmds.c | 7 ++++
src/backend/parser/gram.y | 9 ++++-
src/backend/tcop/utility.c | 2 +-
src/backend/utils/mmgr/portalmem.c | 51 ++++++++++++++++++++++++++
src/include/commands/defrem.h | 3 +-
src/include/nodes/parsenodes.h | 19 +++++-----
src/include/parser/kwlist.h | 1 +
src/include/utils/portal.h | 7 ++++
src/test/regress/expected/create_procedure.out | 27 +++++++++++++-
src/test/regress/sql/create_procedure.sql | 19 +++++++++-
12 files changed, 180 insertions(+), 16 deletions(-)
diff --git a/doc/src/sgml/ref/declare.sgml b/doc/src/sgml/ref/declare.sgml
index a70e2466e5..a817b31889 100644
--- a/doc/src/sgml/ref/declare.sgml
+++ b/doc/src/sgml/ref/declare.sgml
@@ -27,7 +27,8 @@
<refsynopsisdiv>
<synopsis>
DECLARE <replaceable class="parameter">name</replaceable> [ BINARY ] [ INSENSITIVE ] \
[ [ NO ] SCROLL ]
- CURSOR [ { WITH | WITHOUT } HOLD ] FOR <replaceable \
class="parameter">query</replaceable> + CURSOR [ { WITH | WITHOUT } HOLD ] [ { \
WITH | WITHOUT } RETURN ] + FOR <replaceable class="parameter">query</replaceable>
</synopsis>
</refsynopsisdiv>
@@ -120,6 +121,22 @@ <title>Parameters</title>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>WITH RETURN</literal></term>
+ <term><literal>WITHOUT RETURN</literal></term>
+ <listitem>
+ <para>
+ This option is only valid for cursors defined inside a procedure.
+ <literal>WITH RETURN</literal> specifies that the cursor's result rows
+ will be provided as a result set of the procedure invocation. To
+ accomplish that, the cursor must be left open at the end of the
+ procedure. If multiple <literal>WITH RETURN</literal> cursors are
+ declared, then their results will be returned in the order they were
+ created. <literal>WITHOUT RETURN</literal> is the default.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><replaceable class="parameter">query</replaceable></term>
<listitem>
@@ -312,6 +329,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/commands/functioncmds.c \
b/src/backend/commands/functioncmds.c index 1f3156d870..28c26c9a7e 100644
--- a/src/backend/commands/functioncmds.c
+++ b/src/backend/commands/functioncmds.c
@@ -60,11 +60,13 @@
#include "parser/parse_expr.h"
#include "parser/parse_func.h"
#include "parser/parse_type.h"
+#include "tcop/pquery.h"
#include "utils/acl.h"
#include "utils/builtins.h"
#include "utils/fmgroids.h"
#include "utils/guc.h"
#include "utils/lsyscache.h"
+#include "utils/portal.h"
#include "utils/rel.h"
#include "utils/syscache.h"
#include "utils/tqual.h"
@@ -2209,7 +2211,7 @@ ExecuteDoStmt(DoStmt *stmt)
* Execute CALL statement
*/
void
-ExecuteCallStmt(ParseState *pstate, CallStmt *stmt)
+ExecuteCallStmt(ParseState *pstate, CallStmt *stmt, DestReceiver *dest)
{
List *targs;
ListCell *lc;
@@ -2280,4 +2282,17 @@ ExecuteCallStmt(ParseState *pstate, CallStmt *stmt)
}
FunctionCallInvoke(&fcinfo);
+
+ foreach (lc, GetReturnableCursors())
+ {
+ Portal portal = lfirst(lc);
+
+ PortalRun(portal,
+ FETCH_ALL,
+ true, /* XXX top level */
+ true,
+ dest,
+ dest,
+ NULL);
+ }
}
diff --git a/src/backend/commands/portalcmds.c b/src/backend/commands/portalcmds.c
index 76d6cf154c..d763a6fdc7 100644
--- a/src/backend/commands/portalcmds.c
+++ b/src/backend/commands/portalcmds.c
@@ -140,6 +140,13 @@ PerformCursorOpen(DeclareCursorStmt *cstmt, ParamListInfo \
params, portal->cursorOptions |= CURSOR_OPT_NO_SCROLL;
}
+ /*
+ * For returnable cursors, remember the command ID, so we can sort by
+ * creation order later.
+ */
+ if (portal->cursorOptions & CURSOR_OPT_RETURN)
+ portal->createCid = GetCurrentCommandId(true);
+
/*
* Start execution, inserting parameters if any.
*/
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index bf460ef83b..7fac779015 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -665,7 +665,7 @@ static Node *makeRecursiveViewSelect(char *relname, List \
*aliases, Node *query);
RANGE READ REAL REASSIGN RECHECK RECURSIVE REF REFERENCES REFERENCING
REFRESH REINDEX RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA
- RESET RESTART RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROLLUP
+ RESET RESTART RESTRICT RETURN RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROLLUP
ROUTINE ROUTINES ROW ROWS RULE
SAVEPOINT SCHEMA SCHEMAS SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE SEQUENCES
@@ -11004,6 +11004,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; }
;
/*****************************************************************************
@@ -15039,6 +15045,7 @@ unreserved_keyword:
| RESET
| RESTART
| RESTRICT
+ | RETURN
| RETURNS
| REVOKE
| ROLE
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 4da1f8f643..66e6edce76 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -658,7 +658,7 @@ standard_ProcessUtility(PlannedStmt *pstmt,
break;
case T_CallStmt:
- ExecuteCallStmt(pstate, castNode(CallStmt, parsetree));
+ ExecuteCallStmt(pstate, castNode(CallStmt, parsetree), dest);
break;
case T_ClusterStmt:
diff --git a/src/backend/utils/mmgr/portalmem.c b/src/backend/utils/mmgr/portalmem.c
index 89db08464f..fa60761dad 100644
--- a/src/backend/utils/mmgr/portalmem.c
+++ b/src/backend/utils/mmgr/portalmem.c
@@ -1155,3 +1155,54 @@ ThereAreNoReadyPortals(void)
return true;
}
+
+static List *
+list_sorted(List *list, int (*cmp) (const void *, const void *))
+{
+ int nel = list_length(list);
+ int i;
+ ListCell *lc;
+ void **ptrs;
+ List *ret = NIL;
+
+ ptrs = palloc(nel * sizeof(void *));
+ i = 0;
+ foreach(lc, list)
+ ptrs[i++] = lfirst(lc);
+
+ qsort(ptrs, nel, sizeof(void *), cmp);
+
+ for (i = 0; i < nel; i++)
+ ret = lappend(ret, ptrs[i]);
+
+ return ret;
+}
+
+static int
+cmp_portals_by_creation(const void *a, const void *b)
+{
+ const Portal *pa = a;
+ const Portal *pb = 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);
+ }
+
+ return list_sorted(ret, cmp_portals_by_creation);
+}
diff --git a/src/include/commands/defrem.h b/src/include/commands/defrem.h
index 13d5925b18..bc02f5716e 100644
--- a/src/include/commands/defrem.h
+++ b/src/include/commands/defrem.h
@@ -16,6 +16,7 @@
#include "catalog/objectaddress.h"
#include "nodes/parsenodes.h"
+#include "tcop/dest.h"
#include "utils/array.h"
/* commands/dropcmds.c */
@@ -59,7 +60,7 @@ extern void DropTransformById(Oid transformOid);
extern void IsThereFunctionInNamespace(const char *proname, int pronargs,
oidvector *proargtypes, Oid nspOid);
extern void ExecuteDoStmt(DoStmt *stmt);
-extern void ExecuteCallStmt(ParseState *pstate, CallStmt *stmt);
+extern void ExecuteCallStmt(ParseState *pstate, CallStmt *stmt, DestReceiver *dest);
extern Oid get_cast_oid(Oid sourcetypeid, Oid targettypeid, bool missing_ok);
extern Oid get_transform_oid(Oid type_id, Oid lang_id, bool missing_ok);
extern void interpret_function_parameter_list(ParseState *pstate,
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index b721240577..c7960ba4f3 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2632,16 +2632,17 @@ typedef struct SecLabelStmt
* of the query are always postponed until execution.
* ----------------------
*/
-#define CURSOR_OPT_BINARY 0x0001 /* BINARY */
-#define CURSOR_OPT_SCROLL 0x0002 /* SCROLL explicitly given */
-#define CURSOR_OPT_NO_SCROLL 0x0004 /* NO SCROLL explicitly given */
-#define CURSOR_OPT_INSENSITIVE 0x0008 /* INSENSITIVE */
-#define CURSOR_OPT_HOLD 0x0010 /* WITH HOLD */
+#define CURSOR_OPT_BINARY (1 << 0) /* BINARY */
+#define CURSOR_OPT_SCROLL (1 << 1) /* SCROLL explicitly given */
+#define CURSOR_OPT_NO_SCROLL (1 << 2) /* NO SCROLL explicitly given */
+#define CURSOR_OPT_INSENSITIVE (1 << 3) /* INSENSITIVE */
+#define CURSOR_OPT_HOLD (1 << 4) /* WITH HOLD */
+#define CURSOR_OPT_RETURN (1 << 5) /* WITH RETURN */
/* these planner-control flags do not correspond to any SQL grammar: */
-#define CURSOR_OPT_FAST_PLAN 0x0020 /* prefer fast-start plan */
-#define CURSOR_OPT_GENERIC_PLAN 0x0040 /* force use of generic plan */
-#define CURSOR_OPT_CUSTOM_PLAN 0x0080 /* force use of custom plan */
-#define CURSOR_OPT_PARALLEL_OK 0x0100 /* parallel mode OK */
+#define CURSOR_OPT_FAST_PLAN (1 << 8) /* prefer fast-start plan */
+#define CURSOR_OPT_GENERIC_PLAN (1 << 9) /* force use of generic plan */
+#define CURSOR_OPT_CUSTOM_PLAN (1 << 10) /* force use of custom plan */
+#define CURSOR_OPT_PARALLEL_OK (1 << 11) /* parallel mode OK */
typedef struct DeclareCursorStmt
{
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index a932400058..566546fe38 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -335,6 +335,7 @@ PG_KEYWORD("replica", REPLICA, UNRESERVED_KEYWORD)
PG_KEYWORD("reset", RESET, UNRESERVED_KEYWORD)
PG_KEYWORD("restart", RESTART, UNRESERVED_KEYWORD)
PG_KEYWORD("restrict", RESTRICT, UNRESERVED_KEYWORD)
+PG_KEYWORD("return", RETURN, UNRESERVED_KEYWORD)
PG_KEYWORD("returning", RETURNING, RESERVED_KEYWORD)
PG_KEYWORD("returns", RETURNS, UNRESERVED_KEYWORD)
PG_KEYWORD("revoke", REVOKE, UNRESERVED_KEYWORD)
diff --git a/src/include/utils/portal.h b/src/include/utils/portal.h
index cb6f00081d..08890e5c6a 100644
--- a/src/include/utils/portal.h
+++ b/src/include/utils/portal.h
@@ -130,6 +130,12 @@ typedef struct PortalData
SubTransactionId createSubid; /* the creating subxact */
SubTransactionId activeSubid; /* the last subxact with activity */
+ /*
+ * 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) */
const char *commandTag; /* command tag for original query */
@@ -237,5 +243,6 @@ extern PlannedStmt *PortalGetPrimaryStmt(Portal portal);
extern void PortalCreateHoldStore(Portal portal);
extern void PortalHashTableDeleteAll(void);
extern bool ThereAreNoReadyPortals(void);
+extern List *GetReturnableCursors(void);
#endif /* PORTAL_H */
diff --git a/src/test/regress/expected/create_procedure.out \
b/src/test/regress/expected/create_procedure.out index eeb129d71f..219118cb16 100644
--- a/src/test/regress/expected/create_procedure.out
+++ b/src/test/regress/expected/create_procedure.out
@@ -79,8 +79,33 @@ ALTER ROUTINE testfunc1a RENAME TO testfunc1;
ALTER ROUTINE ptest1(text) RENAME TO ptest1a;
ALTER ROUTINE ptest1a RENAME TO ptest1;
DROP ROUTINE 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
+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)
+
-- cleanup
DROP PROCEDURE ptest1;
DROP PROCEDURE ptest2;
-DROP TABLE cp_test;
+DROP TABLE cp_test, cp_test2, cp_test3;
DROP USER regress_user1;
diff --git a/src/test/regress/sql/create_procedure.sql \
b/src/test/regress/sql/create_procedure.sql index f09ba2ad30..911882151c 100644
--- a/src/test/regress/sql/create_procedure.sql
+++ b/src/test/regress/sql/create_procedure.sql
@@ -69,11 +69,28 @@ CREATE USER regress_user1;
DROP ROUTINE 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
+AS $$
+DECLARE c1 CURSOR WITH RETURN FOR SELECT * FROM cp_test2;
+DECLARE c2 CURSOR WITH RETURN FOR SELECT * FROM cp_test3;
+$$;
+
+CALL pdrstest1();
+
+
-- cleanup
DROP PROCEDURE ptest1;
DROP PROCEDURE ptest2;
-DROP TABLE cp_test;
+DROP TABLE cp_test, cp_test2, cp_test3;
DROP USER regress_user1;
--
2.14.3
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic