[WIP] UNNEST(REFCURSOR): allowing SELECT to consume data from a REFCURSOR
Started by Dent Johnover 6 years ago20 messages
Hi folks,
Prompted originally by a post by Roman Pekar [1]/messages/by-id/CAAcdnuzHDnDX73jBb9CZZE=Sv3gDTk8E6-SGRGYEUZbLAy0QRA@mail.gmail.com </messages/by-id/CAAcdnuzHDnDX73jBb9CZZE=Sv3gDTk8E6-SGRGYEUZbLAy0QRA@mail.gmail.com>, I wanted to share a revised version of a patch that allows REFCURSOR results to be consumed as data in a regular SQL query as well as my thoughts on how to improve the area as a whole.
In order to be clear about the purpose and how I see it fitting into a broader context, I’ve started a new thread and I’d welcome discussion about it.
Background
----------
The ambition of this contribution is to make PostgreSQL able to efficiently support procedural language functions that either produce or consume sets (or both).
PostgreSQL already has some support for this in functions that return SETOFs. However, as my review [3]/messages/by-id/DE237364-EB7A-4851-9337-F9F6491E46A6@qqdd.eu </messages/by-id/DE237364-EB7A-4851-9337-F9F6491E46A6@qqdd.eu> identified, there are some gaps in PostgreSQL’s current capability, as well as scope for extension to improve its overall capability.
This first patch addresses only a small part of the overall ambition, but I wanted to share both the patch and the overall ambition as work in progress, and I’d welcome comments on both. (The patch is still based on 12beta2.)
Problems to be solved
---------------------
1. Allow procedural languages (PLs) to efficiently consume sets of records
PostgreSQL does allow PL functions to consume sets, however it does so be feeding records to the function, one row per function invocation. REFCURSORs, however can be supplied as input parameters and their content consumed by the function as it wishes, but only if the PL supports the REFCURSOR concept.
Typically, PLs do allow SQL queries to be executed within the PL function [5, 6, 7]. However REFCURSOR results cannot be effectively consumed in a regular SQL SELECT, significantly limiting their use.
2. Allow plpgsql functions to efficiently return sets of records
By ‘efficiently’, I mean that a large result set should not be required to be staged before the executor is able to process it. Staging is not an issue for small sets, but for large sets and especially if they are subject to further processing, intermediate staging it is a performance disadvantage.
PostgreSQL already has some support for this functions that return SETOFs. At present, plpgsql cannot take advantage of this support while also achieving the efficiency criteria because, as the documentation [4]https://www.postgresql.org/docs/10/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING <https://www.postgresql.org/docs/10/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING> notes, all returned data is staged before it is retuned.
Addressing this limitation could also of benefit to other PLs, however a quick survey finds at least PL Python is already well-adapted to efficiently return SETOFs.
3. Allow optimisation of a returned query
plpgsql offers a syntactic shortcut to return the results of a SQL query directly. Despite appearing to return a query, the RETURN QUERY syntax actually returns the /results/ of the query [4]https://www.postgresql.org/docs/10/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING <https://www.postgresql.org/docs/10/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING>. This means the optimiser has no opportunity to influence its execution, such as by pushing down expressions into its WHERE clause, or taking advantage of alternative indexes to modify its sort order.
Other PLs are similarly constrained. Most PLs lack plpgsql’s syntactic sugar, but even though some PLs are better able to efficiently return SETOFs row-by-row, the optimiser cannot see “inside” the query that the PL executes even if its intent is to return the results directly.
Only SQL language functions are afforded the luxury of integration into then outer statement’s plan [8]https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions <https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions>, but even SQL language functions are somewhat constrained in the types of dynamism that are permitted.
4. Allow a set-returning query to be supplied as an input parameter
It is possible to supply a scalar value, or function call that returns a scalar value as an input parameter. And, with problems 1 & 2 addressed, sets can be supplied as input parameters. However, a literal set-returning SQL query cannot be supplied as a parameter (not without PostgreSQL invoking the ‘calling’ function for each row in the set). REFCURSORs cannot be constructed natively from SQL.
A simplistic response would provide a trivial constructor for REFCURSORs, accepting the query as a text parameter. However it is quite unnatural to supply SQL in textual form, more especially to do so safely. So the challenge is to allow a set-returning subquery to be provided as a parameter in literal form.
Why are these problems important?
---------------------------------
My personal wish is for PostgreSQL to offer a feature set that is consistent with itself and without arbitrary limitation. A better argument might be that that it is desirable to match the features of other RDBMSs [9]https://docs.oracle.com/cd/B19306_01/appdev.102/b14289/dcitblfns.htm <https://docs.oracle.com/cd/B19306_01/appdev.102/b14289/dcitblfns.htm>, or for reason of the use cases they address [10]/messages/by-id/005701c6dc2c$49011fc0$0a00a8c0@trivadis.com </messages/by-id/005701c6dc2c$49011fc0$0a00a8c0@trivadis.com> that are new or push the boundaries of what PostgreSQL can do [1]/messages/by-id/CAAcdnuzHDnDX73jBb9CZZE=Sv3gDTk8E6-SGRGYEUZbLAy0QRA@mail.gmail.com </messages/by-id/CAAcdnuzHDnDX73jBb9CZZE=Sv3gDTk8E6-SGRGYEUZbLAy0QRA@mail.gmail.com>, or that are important such as fulfilling a DW/ETL need [11]https://oracle-base.com/articles/misc/pipelined-table-functions <https://oracle-base.com/articles/misc/pipelined-table-functions>, or to more directly address approaches touted of NoSQL such as Map Reduce [12]https://blogs.oracle.com/datawarehousing/mapreduce-oracle-tablefunctions <https://blogs.oracle.com/datawarehousing/mapreduce-oracle-tablefunctions>.
Design and implementation
-------------------------
1. Set returning function (SRF) for REFCURSOR
Tackling first problems 1 and (part of) 2, it seems easy and obvious to allow that REFCURSORs can be consumed in a SELECT query.
PostgreSQL already allows an array to be consumed one record per entry via the UNNEST(anyarray) built-in function [13]https://www.postgresql.org/docs/10/functions-array.html <https://www.postgresql.org/docs/10/functions-array.html>. Overloading UNNEST() to accept a REFCURSOR argument can be done easily, and the executor’s SRF machinery allows the result set to be consumed efficiently.
With such an implementation, and given a REFCURSOR-returning function, kv() the following syntax illustrates trivial usage:
SELECT *
FROM UNNEST (kv ('A'))
AS (key TEXT, val NUMERIC);
With this UNNEST() construct, it is possible to consume a returned REFCURSOR inline in a single SQL statement.
To complete the example, the function kv() might trivially be defined as:
CREATE FUNCTION kv (suffix text)
RETURNS REFCURSOR
STABLE LANGUAGE plpgsql
AS $$
DECLARE
cur REFCURSOR;
BEGIN
OPEN cur FOR EXECUTE
'SELECT * FROM kv_table_' || suffix;
RETURN cur;
END;
$$;
Other obvious example setup includes:
create table kv_table_a (key text, value numeric);
insert into kv_table_a select 'ITEM_A', generate_series (0, 99);
It is also possible to accept a REFCURSOR as an input parameter:
CREATE FUNCTION limit_val (input_refcur text, val_limit numeric)
RETURNS REFCURSOR
STABLE LANGUAGE plpgsql
AS $$
DECLARE
cur REFCURSOR;
BEGIN
OPEN cur FOR SELECT * FROM UNNEST (input_refcur::REFCURSOR) as (key text, value numeric) WHERE value < val_limit;
RETURN cur;
END;
$$;
SELECT *
FROM UNNEST (limit_val (kv ('A')::text, 10))
AS (key TEXT, val NUMERIC);
Having this construct, it is possible for plpgsql FUNCTION’s to both accept and return REFCURSOR variables. In plpgsql, is would be unnecessary to cast the REFCURSOR to and from text, but other PLs, presumably lacking first class knowledge of the REFCURSOR type, probably need to do so. In above example, limit_val() illustrates how a REFCURSOR can be accepted in text form.
In my patch, I’ve used the SPI APIs to access the Portal which lies behind the REFCURSOR. Although SPI seems to offer an easy interface, and it’s also what plpgsql uses internally, I’m not sure it wouldn’t be better to access the Portal directly.
It is interesting to note that Oracle names its similar construct TABLE() [9]https://docs.oracle.com/cd/B19306_01/appdev.102/b14289/dcitblfns.htm <https://docs.oracle.com/cd/B19306_01/appdev.102/b14289/dcitblfns.htm>, rather than UNNEST(), and in later releases, its use is optional. TABLE is a reserved word and it would be unusual to overload it, although we could educate the parser to treat it specially. Oracle compatibility is an important consideration, but this is a niche area.
If we continue to use REFCURSOR, it is difficult to make some function call-like construct optional because it is already syntactically possible to select FROM a REFCURSOR-returning function. For example, SELECT * FROM kv (‘A’), is a valid and effective expression, despite being of questionable construction and utility.
An alternative might build on top of existing support for returning SETOFs, which already requires no UNNEST()-like construct. This is attractive in principle, but it makes some of the further extensions discussed below more awkward (in my opinion).
2. Query-bound REFCURSORs
Problem 3 could be addressed by educating the planner in how to extract the query inside the Portal behind the REFCURSOR.
At present, REFCURSORs are only bound to a query once they are OPENed, but when they are OPENed, the query also is fully planned, ready for immediate execution. The ambition is to allow the REFCURSOR’s query to be inlined within the outer query’s plan, so it seems wasteful to expend planner cycles, only for the plan to be thrown away.
The proposed implementation would (1) create an intermediate BOUND state for REFCURSORs, and (2) educate plpgsql about how to BIND unbound_cursorvar FOR query.
My first idea was to modify the REFCURSOR type itself, creating a new state, and adding storage for the BOUND query, but this seems unfeasible without extensive hackery. The REFCURSOR type is a trivial veneer atop a C string (which contains the Portal name), so there is no internal structure to extend.
So my plan is to retain the direct coupling of REFCURSOR<->Portal, and to allow plpgsql to set the query text at BIND time via PortalDefineQuery(). Existing plpgsql code should be unaffected as it need know nothing about the new BOUND state.
In order for any of this to work, the planner has to be able to extract the query from the returned Portal. It seems inline_set_returning_function() is the right place to make this extraction. Adding specific recognition for a function call to UNNEST() with single argument of type REFCURSOR is easy, and existing eval_const_expressions() semantics determine whether the single argument expression can be evaluated at plan time. (Of course, if it cannot, then it falls through to be processed at execution time by the REFCURSOR set returning function (SRF) described above.)
It feels slightly uncomfortable to have UNNEST(REFCURSOR) operate as a regular function, and also have specific recognition for UNNEST() elsewhere in the planner machinery. Arguably, this is already a kind of specific knowledge that inline_set_returning_function() has for SQL language FUNCTIONs, but the recognition I propose for UNNEST(REFCURSOR) is much narrower. An alternative might be to introduce a new type that inline_set_returning_function() can recognise (for example, INLINEABLE_QUERY), or to entirely separate the SRF case from the inlining case at a syntax level (for example, retaining UNNEST(REFCURSOR) as the SRF, but using INLINE(REFCURSOR) for the case at hand).
I’d welcome input here. Although the implementation seems quite feasible, the SQL and plpgsql syntax is less obvious.
3. Literal subquery type
Problem 4 could be addressed by educating the parser specially about the REFCURSOR type when faced with a literal query.
Consider this example:
SELECT *
FROM UNNEST (
limit_val (
REFCURSOR (
SELECT key || '_COPY', value FROM kv_table_a
), 25)
) AS (key TEXT, val NUMERIC);
The REFCURSOR(literal_query) construct could be made to result in a BOUND REFCURSOR, in this case, with SELECT key || '_COPY', value FROM kv_table_a, and then passed as a constant parameter to limit_val().
Usefully, at present, the construct yields a syntax error: although REFCURSOR(string) is an already valid construct (being equivalent to CAST (string AS REFCURSOR)), it’s not acceptable to provide a literal subquery without parenthesis. So, while REFCURSOR ((SELECT 'some_cursor')) is roughly equivalent to CAST ('some_cursor' AS REFCURSOR), the near-alternative of REFCURSOR (SELECT 'some_cursor') is quite simply not valid.
If I’m right, the task is simply a matter of ‘plumbing through’ special knowledge of a REFCURSOR(literal_subquery) construct through the parser. It seems tricky as there are many affected code sites, but the work seems uncomplicated.
Educating the parser about special types seems, again, slightly uncomfortable. An alternative might be to create an intermediate construct: for example, QUERY(literal_subquery) might be made to return the parse tree as a pg_node_tree (similar to how VIEWs are exposed in the system catalogue), and REFCURSOR(pg_node_tree) could consume it, yielding a joined-up construct of REFCURSOR(QUERY(literal_subquery)). However, we might also simply accept REFCURSOR(literal_subquery) to be special, and if/when other need is found for a literal subquery as a parameter, then this becomes the way to supply it.
For point of reference, Oracle seems to have gone the way of making CURSOR(literal_subquery) do something similar, yielding a REF CURSOR, which allows the CURSOR to be passed by reference [2]https://docs.oracle.com/database/121/SQLRF/expressions006.htm#SQLRF52077 <https://docs.oracle.com/database/121/SQLRF/expressions006.htm#SQLRF52077>.
Other problems
--------------
1. This contribution does not actually address the limitation in plpgsql, that the “current implementation of RETURN NEXT and RETURN QUERY stores the entire result set before returning from the function” [4]https://www.postgresql.org/docs/10/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING <https://www.postgresql.org/docs/10/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING>. My original investigation [3]/messages/by-id/DE237364-EB7A-4851-9337-F9F6491E46A6@qqdd.eu </messages/by-id/DE237364-EB7A-4851-9337-F9F6491E46A6@qqdd.eu> presumed this limitation to apply generally to all PLs, but I now realise this is not the case: at least the Python PL allows efficient return of SETOFs [5]https://www.postgresql.org/docs/10/plpython-database.html#id-1.8.11.15.3 <https://www.postgresql.org/docs/10/plpython-database.html#id-1.8.11.15.3>. With this in mind, I see the plpgsql limitation as less encumbering (as plpython is presumably broadly available) but I’d be interested to know if this view is shared.
2. A perhaps more significant problem is the apparent duplication of plpgsql’s RETURN QUERY syntax. One could perhaps conceive that plpgsql supported an additional marker, for example, RETURN [INLINEABLE] QUERY. It is difficult to see this fitting well with other PLs.
3. The current proposal also requires to declare the expected record with an AS (...) construction. This is rather inconvenient, but it is difficult to see how it could be avoided.
4. Other PLs can use REFCURSORS by virtue of REFCURSOR being a thin veneer atop string. It is coherent if one understands how the PostgreSQL type system works, but quite strange otherwise. Better integration into other PLs and their type systems might be important.
5. As mentioned, SETOF is a near-equivalent for some use cases. There’s no way to cast the results of a function RETURNING SETOF to a REFCURSOR without something like REFCURSOR (SELECT * from <some function>(...)). It might be useful to offer a little syntactic sugar. Perhaps we could invent NEST(SETOF <some RECORD type>) could return REFCURSOR.
References
----------
[1]: /messages/by-id/CAAcdnuzHDnDX73jBb9CZZE=Sv3gDTk8E6-SGRGYEUZbLAy0QRA@mail.gmail.com </messages/by-id/CAAcdnuzHDnDX73jBb9CZZE=Sv3gDTk8E6-SGRGYEUZbLAy0QRA@mail.gmail.com>
[2]: https://docs.oracle.com/database/121/SQLRF/expressions006.htm#SQLRF52077 <https://docs.oracle.com/database/121/SQLRF/expressions006.htm#SQLRF52077>
[3]: /messages/by-id/DE237364-EB7A-4851-9337-F9F6491E46A6@qqdd.eu </messages/by-id/DE237364-EB7A-4851-9337-F9F6491E46A6@qqdd.eu>
[4]: https://www.postgresql.org/docs/10/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING <https://www.postgresql.org/docs/10/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING>
[5]: https://www.postgresql.org/docs/10/plpython-database.html#id-1.8.11.15.3 <https://www.postgresql.org/docs/10/plpython-database.html#id-1.8.11.15.3>
[6]: https://www.postgresql.org/docs/10/plpgsql-cursors.html#PLPGSQL-CURSOR-DECLARATIONS <https://www.postgresql.org/docs/10/plpgsql-cursors.html#PLPGSQL-CURSOR-DECLARATIONS>
[7]: https://github.com/tada/pljava/wiki/Using-jdbc <https://github.com/tada/pljava/wiki/Using-jdbc>
[8]: https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions <https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions>
[9]: https://docs.oracle.com/cd/B19306_01/appdev.102/b14289/dcitblfns.htm <https://docs.oracle.com/cd/B19306_01/appdev.102/b14289/dcitblfns.htm>
[10]: /messages/by-id/005701c6dc2c$49011fc0$0a00a8c0@trivadis.com </messages/by-id/005701c6dc2c$49011fc0$0a00a8c0@trivadis.com>
[11]: https://oracle-base.com/articles/misc/pipelined-table-functions <https://oracle-base.com/articles/misc/pipelined-table-functions>
[12]: https://blogs.oracle.com/datawarehousing/mapreduce-oracle-tablefunctions <https://blogs.oracle.com/datawarehousing/mapreduce-oracle-tablefunctions>
[13]: https://www.postgresql.org/docs/10/functions-array.html <https://www.postgresql.org/docs/10/functions-array.html>
Attachments:
unnest-refcursor-v2.patchapplication/octet-stream; name=unnest-refcursor-v2.patch; x-unix-mode=0644Download
diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c
index 3ae1556..7f1d729 100644
--- a/src/backend/utils/adt/arrayfuncs.c
+++ b/src/backend/utils/adt/arrayfuncs.c
@@ -18,7 +18,9 @@
#include <math.h>
#include "access/htup_details.h"
+#include "access/xact.h"
#include "catalog/pg_type.h"
+#include "executor/spi.h"
#include "funcapi.h"
#include "libpq/pqformat.h"
#include "nodes/nodeFuncs.h"
@@ -5939,9 +5941,227 @@ array_fill_internal(ArrayType *dims, ArrayType *lbs,
return result;
}
+/*
+ * UNNEST (REFCURSOR)
+ */
+Datum
+refcursor_unnest(PG_FUNCTION_ARGS)
+{
+ typedef struct
+ {
+ Portal portal;
+ } refcursor_unnest_fctx;
+
+ ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+ FuncCallContext *funcctx;
+ refcursor_unnest_fctx *fctx;
+ bool connected = false;
+
+ /* stuff done only on the first call of the function */
+ bool first_call = SRF_IS_FIRSTCALL();
+ if (first_call)
+ {
+ /* create a function context for cross-call persistence */
+ funcctx = SRF_FIRSTCALL_INIT();
+
+ /* Check to see if caller supports us returning a tuplestore */
+ if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("set-valued function called in context that cannot accept a set")));
+ if (!(rsinfo->allowedModes & SFRM_Materialize))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("materialize mode required, but it is not " \
+ "allowed in this context")));
+
+ /*
+ * switch to memory context appropriate for multiple function calls
+ */
+ MemoryContext oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ /* allocate memory for user context */
+ fctx = (refcursor_unnest_fctx *) palloc(sizeof(refcursor_unnest_fctx));
+
+ MemoryContextSwitchTo(oldcontext);
+
+ char *portal_name = text_to_cstring(PG_GETARG_TEXT_PP(0));
+
+ if (SPI_connect() != SPI_OK_CONNECT)
+ elog(ERROR, "could not connect to SPI manager");
+
+ /* remember to disconnect later... */
+ connected = true;
+
+ Portal portal = SPI_cursor_find(portal_name);
+
+ if (portal == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_CURSOR),
+ errmsg("cursor \"%s\" does not exist", portal_name)));
+
+ // FIXME: verify it's only a SELECT
+
+ /* initialize state */
+ fctx->portal = portal;
+
+ funcctx->user_fctx = fctx;
+
+ // Reset cursor position to top of set.
+ // FIXME: what if the cursor isn't scrollable?
+ SPI_scroll_cursor_move (portal, FETCH_ABSOLUTE, 0);
+ }
+
+ /* stuff done on every call of the function */
+ funcctx = SRF_PERCALL_SETUP();
+ fctx = funcctx->user_fctx;
+
+ if (!connected) /* if we have not connected above ... */
+ if (SPI_connect() != SPI_OK_CONNECT)
+ elog(ERROR, "could not connect to SPI manager");
+
+ // Retrieve a single row...
+ SPI_cursor_fetch(fctx->portal, true, 1);
+
+ // Initialise the Tuple Desriptor. (This can't be done until we have done our first fetch.)
+ if (first_call)
+ {
+ MemoryContext oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ /* Build a tuplestore to return our results in */
+ rsinfo->setDesc = CreateTupleDescCopy (SPI_tuptable->tupdesc);
+
+ MemoryContextSwitchTo(oldcontext);
+ rsinfo->returnMode = SFRM_ValuePerCall;
+ }
+
+ bool next;
+ Datum result;
+
+ Assert (SPI_processed <= 1);
+
+ if (SPI_processed == 1)
+ {
+ result = PointerGetDatum (SPI_returntuple (SPI_tuptable->vals[0], SPI_tuptable->tupdesc));
+
+ next = true;
+ }
+ else // no rows retrieved
+ {
+ next = false;
+ }
+
+ if (SPI_finish() != SPI_OK_FINISH)
+ elog(ERROR, "could not disconnect from SPI manager");
+ connected = false;
+
+ if (next)
+ SRF_RETURN_NEXT (funcctx, result);
+ else
+ SRF_RETURN_DONE(funcctx);
+}
+
+
+/*
+ * Planner support function for UNNEST (REFCURSOR)
+ */
+Datum
+refcursor_unnest_support(PG_FUNCTION_ARGS)
+{
+ Node *rawreq = (Node *) PG_GETARG_POINTER(0);
+ Node *ret = NULL;
+
+ if (!IsA(rawreq, SupportRequestRows)
+ && !IsA (rawreq, SupportRequestCost))
+ PG_RETURN_POINTER(NULL);
+
+ Node *req_node;
+ if (IsA(rawreq, SupportRequestRows))
+ {
+ SupportRequestRows *req = (SupportRequestRows *) rawreq;
+
+ req_node = req->node;
+ }
+ else if (IsA (rawreq, SupportRequestCost))
+ {
+ SupportRequestCost *req = (SupportRequestCost *) rawreq;
+
+ req_node = req->node;
+ }
+
+ // The call to UNNEST should be in a FuncExpr node.
+ if (!is_funcclause(req_node))
+ PG_RETURN_POINTER(NULL);
+
+ List *args = ((FuncExpr *) req_node)->args;
+ Node *arg1 = linitial(args);
+
+ if (arg1 == NULL)
+ PG_RETURN_POINTER(NULL);
+
+ // We can only estimate the cost if the REFCURSOR is
+ // already simplified to a Const.
+ if (!IsA (arg1, Const))
+ PG_RETURN_POINTER(NULL);
+
+ Const *cexpr = (Const *) arg1;
+
+ if (cexpr->constisnull)
+ PG_RETURN_POINTER(NULL);
+
+ if (cexpr->consttype != REFCURSOROID)
+ PG_RETURN_POINTER(NULL);
+
+ // We can ignore a check on the collation because we are not
+ // interested in sorting, and typemod because REFCURSOR has
+ // no modifyable attributes.
+
+ Oid typoutput;
+ bool typIsVarlena;
+ getTypeOutputInfo(cexpr->consttype, &typoutput, &typIsVarlena);
+
+ char *portal_name = OidOutputFunctionCall(typoutput, cexpr->constvalue);
+
+ if (SPI_connect() != SPI_OK_CONNECT)
+ elog(ERROR, "could not connect to SPI manager");
+
+ Portal portal = SPI_cursor_find(portal_name);
+
+ if (portal == NULL)
+ PG_RETURN_POINTER(NULL);
+
+ QueryDesc *qdesc = portal->queryDesc;
+ if (qdesc == NULL)
+ PG_RETURN_POINTER(NULL);
+
+ PlanState *planstate = qdesc->planstate;
+ if (planstate == NULL)
+ PG_RETURN_POINTER(NULL);
+
+ if (IsA(rawreq, SupportRequestRows))
+ {
+ SupportRequestRows *req = (SupportRequestRows *) rawreq;
+
+ req->rows = planstate->plan->plan_rows;
+ }
+ else if (IsA (rawreq, SupportRequestCost))
+ {
+ SupportRequestCost *req = (SupportRequestCost *) rawreq;
+
+ req->startup = planstate->plan->startup_cost;
+ req->per_tuple = (planstate->plan->total_cost - planstate->plan->startup_cost) / planstate->plan->plan_rows;
+ }
+
+ if (SPI_finish() != SPI_OK_FINISH)
+ elog(ERROR, "could not disconnect from SPI manager");
+
+ ret = (Node *) rawreq;
+
+ PG_RETURN_POINTER(ret);
+}
/*
- * UNNEST
+ * UNNEST (array)
*/
Datum
array_unnest(PG_FUNCTION_ARGS)
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 8733524..311d74f 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -1550,6 +1550,13 @@
{ oid => '3996', descr => 'planner support for array_unnest',
proname => 'array_unnest_support', prorettype => 'internal',
proargtypes => 'internal', prosrc => 'array_unnest_support' },
+{ oid => '12921', descr => 'expand refcursor to set of rows',
+ proname => 'unnest', prorows => '100', prosupport => 'refcursor_unnest_support',
+ proretset => 't', prorettype => 'record', proargtypes => 'refcursor',
+ prosrc => 'refcursor_unnest' },
+{ oid => '12923', descr => 'planner support for refcursor_unnest',
+ proname => 'refcursor_unnest_support', prorettype => 'internal',
+ proargtypes => 'internal', prosrc => 'refcursor_unnest_support' },
{ oid => '3167',
descr => 'remove any occurrences of an element from an array',
proname => 'array_remove', proisstrict => 'f', prorettype => 'anyarray',
Re: [WIP] UNNEST(REFCURSOR): allowing SELECT to consume data from a REFCURSOR
Hi John,
Thanks for pushing this, for me it looks like promising start! I need a bit
more time to go through the code (and I'm not an expert in Postgres
internals in any way) but I really appreciate you doing this.
Roman
Re: [WIP] UNNEST(REFCURSOR): allowing SELECT to consume data from a REFCURSOR
Hi folks,
I’ve made a revision of this patch.
The significant change is to access the Portal using Portal APIs rather than through SPI. It seems the persisted state necessary to survive being used to retrieve a row at a time inside an SRF just isn’t a good fit for SPI.
It turned out there was upstream machinery in the FunctionScan node that prevented Postgres being able to pipeline SRFs, even if they return ValuePerCall. So, in practice, this patch is of limited benefit without another patch that changes that behaviour (see [1]https://commitfest.postgresql.org/26/2372/ <https://commitfest.postgresql.org/26/2372/>). Nevertheless, the code is independent so I’m submitting the two changes separately.
I’ll push this into the Jan commit fest.
denty.
[1]: https://commitfest.postgresql.org/26/2372/ <https://commitfest.postgresql.org/26/2372/>
Attachments:
unnest-refcursor-v3.patchapplication/octet-stream; name=unnest-refcursor-v3.patch; x-unix-mode=0644Download
diff --git a/src/backend/utils/adt/Makefile b/src/backend/utils/adt/Makefile
index 5800432..7c71ed4 100644
--- a/src/backend/utils/adt/Makefile
+++ b/src/backend/utils/adt/Makefile
@@ -25,7 +25,7 @@ OBJS = acl.o amutils.o arrayfuncs.o array_expanded.o array_selfuncs.o \
pg_upgrade_support.o pgstatfuncs.o \
pseudotypes.o quote.o rangetypes.o rangetypes_gist.o \
rangetypes_selfuncs.o rangetypes_spgist.o rangetypes_typanalyze.o \
- regexp.o regproc.o ri_triggers.o rowtypes.o ruleutils.o \
+ refcursor.o regexp.o regproc.o ri_triggers.o rowtypes.o ruleutils.o \
selfuncs.o tid.o timestamp.o trigfuncs.o \
tsginidx.o tsgistidx.o tsquery.o tsquery_cleanup.o tsquery_gist.o \
tsquery_op.o tsquery_rewrite.o tsquery_util.o tsrank.o \
diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c
index 8fcdf82..45136e5 100644
--- a/src/backend/utils/adt/arrayfuncs.c
+++ b/src/backend/utils/adt/arrayfuncs.c
@@ -5941,7 +5941,7 @@ array_fill_internal(ArrayType *dims, ArrayType *lbs,
/*
- * UNNEST
+ * UNNEST (array)
*/
Datum
array_unnest(PG_FUNCTION_ARGS)
diff --git a/src/backend/utils/adt/refcursor.c b/src/backend/utils/adt/refcursor.c
new file mode 100644
index 0000000..1f68d08
--- /dev/null
+++ b/src/backend/utils/adt/refcursor.c
@@ -0,0 +1,376 @@
+/*-------------------------------------------------------------------------
+ *
+ * refcursor.c
+ *
+ * IDENTIFICATION
+ * src/backend/utils/adt/refcursor.c
+ *
+ *-------------------------------------------------------------------------
+ */
+
+#include "postgres.h"
+
+#include "access/htup_details.h"
+#include "catalog/pg_type.h"
+#include "funcapi.h"
+#include "nodes/nodeFuncs.h"
+#include "nodes/supportnodes.h"
+#include "optimizer/optimizer.h"
+#include "tcop/pquery.h"
+#include "utils/builtins.h"
+#include "utils/datum.h"
+#include "utils/lsyscache.h"
+#include "utils/refcursor.h"
+#include "utils/typcache.h"
+
+
+typedef struct SingleSlotDestReceiver
+{
+ DestReceiver pub;
+
+ TupleTableSlot *received_slot;
+ TupleDesc tupdesc;
+} SingleSlotDestReceiver;
+
+/*
+ * sqlfunction_startup --- executor startup
+ */
+static void
+ssdr_startup(DestReceiver *self, int operation, TupleDesc typeinfo)
+{
+ SingleSlotDestReceiver *myState = (SingleSlotDestReceiver *) self;
+
+ myState->tupdesc = typeinfo;
+}
+
+/*
+ * sqlfunction_receive --- receive one tuple
+ */
+static bool
+ssdr_receive(TupleTableSlot *slot, DestReceiver *self)
+{
+ SingleSlotDestReceiver *myState = (SingleSlotDestReceiver *) self;
+
+ myState->received_slot = slot;
+
+ return true;
+}
+
+/*
+ * sqlfunction_shutdown --- executor end
+ */
+static void
+ssdr_shutdown(DestReceiver *self)
+{
+ /* no-op */
+}
+
+/*
+ * sqlfunction_destroy --- release DestReceiver object
+ */
+static void
+ssdr_destroy(DestReceiver *self)
+{
+ pfree(self);
+}
+
+/*
+ * CreateSingleSlotDestReceiver -- create a DestReceiver
+ * that acquires a single tupleslot
+ */
+static DestReceiver *
+CreateSingleSlotDestReceiver(void)
+{
+ SingleSlotDestReceiver *self = (SingleSlotDestReceiver *) palloc0(sizeof(SingleSlotDestReceiver));
+
+ self->pub.receiveSlot = ssdr_receive;
+ self->pub.rStartup = ssdr_startup;
+ self->pub.rShutdown = ssdr_shutdown;
+ self->pub.rDestroy = ssdr_destroy;
+ self->pub.mydest = -1;
+
+ /* private fields will be set by ssdr_startup */
+
+ return (DestReceiver *) self;
+}
+
+/*
+ * UNNEST (REFCURSOR)
+ */
+Datum
+refcursor_unnest(PG_FUNCTION_ARGS)
+{
+ typedef struct
+ {
+ Portal portal;
+ SingleSlotDestReceiver *dest;
+ } refcursor_unnest_fctx;
+
+ ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+ FuncCallContext *funcctx;
+ refcursor_unnest_fctx *fctx;
+ FetchDirection direction;
+ uint64 howMany;
+ uint64 nfetched;
+
+ /* stuff done only on the first call of the function */
+ bool first_call = SRF_IS_FIRSTCALL();
+ if (first_call)
+ {
+ /* create a function context for cross-call persistence */
+ funcctx = SRF_FIRSTCALL_INIT();
+
+ /* Check to see if caller supports us returning a set */
+ if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("set-valued function called in context that cannot accept a set")));
+ if (!(rsinfo->allowedModes & SFRM_ValuePerCall))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("value per call mode required, but it is not " \
+ "allowed in this context")));
+
+ /*
+ * switch to memory context appropriate for multiple function calls
+ */
+ MemoryContext oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ /* allocate memory for user context */
+ fctx = (refcursor_unnest_fctx *) palloc(sizeof(refcursor_unnest_fctx));
+
+ fctx->dest = (SingleSlotDestReceiver *) CreateSingleSlotDestReceiver();
+
+ MemoryContextSwitchTo(oldcontext);
+
+ char *portal_name = text_to_cstring(PG_GETARG_TEXT_PP(0));
+
+ fctx->portal = GetPortalByName(portal_name);
+
+ /* Check that the portal exists */
+ if (!PortalIsValid(fctx->portal))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_CURSOR),
+ errmsg("cursor \"%s\" does not exist", portal_name)));
+
+ /* ensure the Portal is ready (has already been OPEN'ed) */
+ if (! (fctx->portal->status == PORTAL_DEFINED ||
+ fctx->portal->status == PORTAL_READY))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_CURSOR_STATE),
+ errmsg("cursor \"%s\" is not OPEN", portal_name)));
+
+ /*
+ * Ensure the Portal returns some results (so is not a utility
+ * command, or set of multiple statements.
+ */
+ if (! (fctx->portal->strategy == PORTAL_ONE_SELECT ||
+ fctx->portal->strategy == PORTAL_ONE_RETURNING ||
+ fctx->portal->strategy == PORTAL_ONE_MOD_WITH))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_CURSOR_DEFINITION),
+ errmsg("cursor \"%s\" does not return result set", portal_name)));
+
+ pfree(portal_name);
+
+ funcctx->user_fctx = fctx;
+ }
+
+ /* stuff done on every call of the function */
+ funcctx = SRF_PERCALL_SETUP();
+ fctx = funcctx->user_fctx;
+
+ if (first_call)
+ {
+ direction = FETCH_ABSOLUTE; /* start from absolute position */
+ howMany = 1; /* position = 1 (top); also reads one row */
+ }
+ else
+ {
+ direction = FETCH_FORWARD; /* othrewise advance forward */
+ howMany = 1; /* count = 1 (read one row) */
+ }
+
+ /* Run the cursor... */
+ nfetched = PortalRunFetch (fctx->portal, direction, howMany,
+ (DestReceiver *) fctx->dest);
+
+ /*
+ * Initialise the Tuple Desriptor. (This can't be done until
+ * we have done our first fetch.)
+ */
+ if (first_call)
+ {
+ MemoryContext oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ /* Build a tuplestore to return our results in */
+ rsinfo->setDesc = CreateTupleDescCopy (fctx->dest->tupdesc);
+
+ /* For RECORD results, make sure a typmod has been assigned */
+ if (rsinfo->setDesc->tdtypeid == RECORDOID &&
+ rsinfo->setDesc->tdtypmod < 0)
+ assign_record_type_typmod(rsinfo->setDesc);
+
+ MemoryContextSwitchTo(oldcontext);
+ rsinfo->returnMode = SFRM_ValuePerCall;
+ }
+
+ Assert (nfetched <= 1);
+
+ if (nfetched == 1)
+ {
+ /*
+ * Convert the TableTupleSlot to a HeapTuple (doesn't
+ * materialise and doesn't copy unless unavoidable).
+ */
+ HeapTuple tuple = ExecFetchSlotHeapTuple (fctx->dest->received_slot, /* materialise */ false, NULL);
+
+ /*
+ * Avoid making a copy if the HeapTuple is already
+ * fully in memory and marked with correct typeid/typmod.
+ */
+ Datum datum = PointerGetDatum (tuple->t_data);
+ if (HeapTupleHasExternal(tuple) ||
+ HeapTupleHeaderGetTypeId(tuple->t_data) != rsinfo->setDesc->tdtypeid ||
+ HeapTupleHeaderGetTypMod(tuple->t_data) != rsinfo->setDesc->tdtypmod)
+ {
+ /*
+ * Copy the tuple as a Datum, ensuring it is
+ * fully in memory in the process.
+ */
+ datum = heap_copy_tuple_as_datum (tuple, rsinfo->setDesc);
+ }
+
+ /*
+ * Obtain HeapTupleHeader for the Datum, which is in
+ * memory, so should not require a copy.
+ */
+ HeapTupleHeader result = DatumGetHeapTupleHeader (datum);
+
+ SRF_RETURN_NEXT (funcctx, PointerGetDatum (result));
+ }
+ else /* no rows retrieved */
+ {
+ /* it will have been pfree()'ed by ssdr_destroy() */
+ fctx->dest = NULL;
+
+ SRF_RETURN_DONE(funcctx);
+ }
+}
+
+
+/*
+ * Planner support function for UNNEST (REFCURSOR)
+ */
+Datum
+refcursor_unnest_support(PG_FUNCTION_ARGS)
+{
+ Node *rawreq = (Node *) PG_GETARG_POINTER(0);
+ Node *ret = NULL;
+
+ if (!IsA(rawreq, SupportRequestRows)
+ && !IsA (rawreq, SupportRequestCost))
+ PG_RETURN_POINTER(NULL);
+
+ Node *req_node = NULL;
+ if (IsA(rawreq, SupportRequestRows))
+ {
+ SupportRequestRows *req = (SupportRequestRows *) rawreq;
+
+ req_node = req->node;
+ }
+ else if (IsA (rawreq, SupportRequestCost))
+ {
+ SupportRequestCost *req = (SupportRequestCost *) rawreq;
+
+ req_node = req->node;
+ }
+
+ /* The call to UNNEST should be in a FuncExpr node. */
+ if (!is_funcclause(req_node))
+ PG_RETURN_POINTER(NULL);
+
+ List *args = ((FuncExpr *) req_node)->args;
+ Node *arg1 = linitial(args);
+
+ if (arg1 == NULL)
+ PG_RETURN_POINTER(NULL);
+
+ /*
+ * We can only estimate the cost if the REFCURSOR is
+ * already simplified to a Const.
+ */
+ if (!IsA (arg1, Const))
+ PG_RETURN_POINTER(NULL);
+
+ Const *cexpr = (Const *) arg1;
+
+ if (cexpr->constisnull)
+ PG_RETURN_POINTER(NULL);
+
+ if (cexpr->consttype != REFCURSOROID)
+ PG_RETURN_POINTER(NULL);
+
+ /*
+ * We can ignore a check on the collation because we are not
+ * interested in sorting, and typemod because REFCURSOR has
+ * no modifyable attributes.
+ */
+ Oid typoutput;
+ bool typIsVarlena;
+ getTypeOutputInfo(cexpr->consttype, &typoutput, &typIsVarlena);
+
+ char *portal_name = OidOutputFunctionCall(typoutput, cexpr->constvalue);
+
+ Portal portal = GetPortalByName(portal_name);
+
+ /* Check that the portal exists */
+ if (!PortalIsValid(portal))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_CURSOR),
+ errmsg("cursor \"%s\" does not exist", portal_name)));
+
+ /* ensure the Portal is ready (has already been OPEN'ed) */
+ if (! (portal->status == PORTAL_DEFINED ||
+ portal->status == PORTAL_READY))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_CURSOR_STATE),
+ errmsg("cursor \"%s\" is not OPEN", portal_name)));
+
+ /*
+ * Ensure the Portal returns some results (so is
+ * not a utility command, or set of multiple statements.
+ */
+ if (! (portal->strategy == PORTAL_ONE_SELECT ||
+ portal->strategy == PORTAL_ONE_RETURNING ||
+ portal->strategy == PORTAL_ONE_MOD_WITH))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_CURSOR_DEFINITION),
+ errmsg("cursor \"%s\" does not return result set", portal_name)));
+
+ QueryDesc *qdesc = portal->queryDesc;
+ if (qdesc == NULL)
+ PG_RETURN_POINTER(NULL);
+
+ PlanState *planstate = qdesc->planstate;
+ if (planstate == NULL)
+ PG_RETURN_POINTER(NULL);
+
+ if (IsA(rawreq, SupportRequestRows))
+ {
+ SupportRequestRows *req = (SupportRequestRows *) rawreq;
+
+ req->rows = planstate->plan->plan_rows;
+ }
+ else if (IsA (rawreq, SupportRequestCost))
+ {
+ SupportRequestCost *req = (SupportRequestCost *) rawreq;
+
+ req->startup = planstate->plan->startup_cost;
+ req->per_tuple = (planstate->plan->total_cost - planstate->plan->startup_cost) / planstate->plan->plan_rows;
+ }
+
+ ret = (Node *) rawreq;
+
+ PG_RETURN_POINTER(ret);
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index d352f9a..0d9bec6 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -1550,6 +1550,13 @@
{ oid => '3996', descr => 'planner support for array_unnest',
proname => 'array_unnest_support', prorettype => 'internal',
proargtypes => 'internal', prosrc => 'array_unnest_support' },
+{ oid => '12921', descr => 'expand refcursor to set of rows',
+ proname => 'unnest', prorows => '100', prosupport => 'refcursor_unnest_support',
+ proretset => 't', prorettype => 'record', proargtypes => 'refcursor',
+ prosrc => 'refcursor_unnest' },
+{ oid => '12923', descr => 'planner support for refcursor_unnest',
+ proname => 'refcursor_unnest_support', prorettype => 'internal',
+ proargtypes => 'internal', prosrc => 'refcursor_unnest_support' },
{ oid => '3167',
descr => 'remove any occurrences of an element from an array',
proname => 'array_remove', proisstrict => 'f', prorettype => 'anyarray',
diff --git a/src/include/utils/refcursor.h b/src/include/utils/refcursor.h
new file mode 100644
index 0000000..e636193
--- /dev/null
+++ b/src/include/utils/refcursor.h
@@ -0,0 +1,15 @@
+/*-------------------------------------------------------------------------
+*
+* refcursor.c
+*
+* IDENTIFICATION
+* src/include/utils/refcursor.c
+*
+*-------------------------------------------------------------------------
+*/
+
+#ifndef refcursor_h
+#define refcursor_h
+
+
+#endif /* refcursor_h */
diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out
index e85b294..f05e9b3 100644
--- a/src/test/regress/expected/plpgsql.out
+++ b/src/test/regress/expected/plpgsql.out
@@ -2078,6 +2078,227 @@ select refcursor_test2(20000, 20000) as "Should be false",
f | t
(1 row)
+-- Check ability to consume from REFCURSOR in SELECT
+explain (verbose, costs off) select * from unnest(return_unnamed_refcursor()) as (a int);
+ QUERY PLAN
+-----------------------------------------------------
+ Function Scan on pg_catalog.unnest
+ Output: a
+ Function Call: unnest(return_unnamed_refcursor())
+(3 rows)
+
+select * from unnest(return_unnamed_refcursor()) as (a int);
+ a
+-----
+ 5
+ 50
+ 500
+(3 rows)
+
+explain (verbose, costs off) select * from unnest(return_unnamed_refcursor()) as (a int) where a >= 50;
+ QUERY PLAN
+-----------------------------------------------------
+ Function Scan on pg_catalog.unnest
+ Output: a
+ Function Call: unnest(return_unnamed_refcursor())
+ Filter: (unnest.a >= 50)
+(4 rows)
+
+select * from unnest(return_unnamed_refcursor()) as (a int) where a >= 50;
+ a
+-----
+ 50
+ 500
+(2 rows)
+
+explain (verbose, costs off) select * from unnest(return_unnamed_refcursor()) as (a int) order by a desc;
+ QUERY PLAN
+-----------------------------------------------------------
+ Sort
+ Output: a
+ Sort Key: unnest.a DESC
+ -> Function Scan on pg_catalog.unnest
+ Output: a
+ Function Call: unnest(return_unnamed_refcursor())
+(6 rows)
+
+select * from unnest(return_unnamed_refcursor()) as (a int) order by a desc;
+ a
+-----
+ 500
+ 50
+ 5
+(3 rows)
+
+-- Check multiple scan of REFCURSOR
+explain (verbose, costs off) select * from (select 1 nr union all select 2) as scan, unnest(return_unnamed_refcursor()) as (a int) order by nr;
+ QUERY PLAN
+-----------------------------------------------------------------
+ Sort
+ Output: (1), unnest.a
+ Sort Key: (1)
+ -> Nested Loop
+ Output: (1), unnest.a
+ -> Function Scan on pg_catalog.unnest
+ Output: unnest.a
+ Function Call: unnest(return_unnamed_refcursor())
+ -> Materialize
+ Output: (1)
+ -> Append
+ -> Result
+ Output: 1
+ -> Result
+ Output: 2
+(15 rows)
+
+select * from (select 1 nr union all select 2) as scan, unnest(return_unnamed_refcursor()) as (a int) order by nr;
+ nr | a
+----+-----
+ 1 | 5
+ 1 | 50
+ 1 | 500
+ 2 | 5
+ 2 | 50
+ 2 | 500
+(6 rows)
+
+-- Check multiple reference to single REFCURSOR
+explain (verbose, costs off)
+ select * from
+ unnest(return_unnamed_refcursor()) as a(n int),
+ unnest(return_unnamed_refcursor()) as b(n int)
+ order by a.n, b.n;
+ QUERY PLAN
+-----------------------------------------------------------------
+ Sort
+ Output: a.n, b.n
+ Sort Key: a.n, b.n
+ -> Nested Loop
+ Output: a.n, b.n
+ -> Function Scan on pg_catalog.unnest a
+ Output: a.n
+ Function Call: unnest(return_unnamed_refcursor())
+ -> Function Scan on pg_catalog.unnest b
+ Output: b.n
+ Function Call: unnest(return_unnamed_refcursor())
+(11 rows)
+
+select * from
+ unnest(return_unnamed_refcursor()) as a(n int),
+ unnest(return_unnamed_refcursor()) as b(n int)
+ order by a.n, b.n;
+ n | n
+-----+-----
+ 5 | 5
+ 5 | 50
+ 5 | 500
+ 50 | 5
+ 50 | 50
+ 50 | 500
+ 500 | 5
+ 500 | 50
+ 500 | 500
+(9 rows)
+
+explain (verbose, costs off)
+ select r.cur::text, a.n, b.n from
+ (select return_unnamed_refcursor() cur) r,
+ unnest (r.cur) as a(n int),
+ unnest (r.cur) as b(n int)
+ order by r.cur::text, a.n, b.n;
+ QUERY PLAN
+-------------------------------------------------------------------------
+ Sort
+ Output: (((return_unnamed_refcursor()))::text), a.n, b.n
+ Sort Key: (((return_unnamed_refcursor()))::text), a.n, b.n
+ -> Nested Loop
+ Output: ((return_unnamed_refcursor()))::text, a.n, b.n
+ -> Nested Loop
+ Output: (return_unnamed_refcursor()), a.n
+ -> Result
+ Output: return_unnamed_refcursor()
+ -> Function Scan on pg_catalog.unnest a
+ Output: a.n
+ Function Call: unnest((return_unnamed_refcursor()))
+ -> Function Scan on pg_catalog.unnest b
+ Output: b.n
+ Function Call: unnest((return_unnamed_refcursor()))
+(15 rows)
+
+select r.cur::text, a.n, b.n from
+ (select return_unnamed_refcursor() cur) r,
+ unnest (r.cur) as a(n int),
+ unnest (r.cur) as b(n int)
+ order by r.cur::text, a.n, b.n;
+ cur | n | n
+---------------------+-----+-----
+ <unnamed portal 11> | 5 | 5
+ <unnamed portal 11> | 5 | 50
+ <unnamed portal 11> | 5 | 500
+ <unnamed portal 11> | 50 | 5
+ <unnamed portal 11> | 50 | 50
+ <unnamed portal 11> | 50 | 500
+ <unnamed portal 11> | 500 | 5
+ <unnamed portal 11> | 500 | 50
+ <unnamed portal 11> | 500 | 500
+(9 rows)
+
+-- Check use of REFCURSOR in WITH
+with rcq as (
+ select * from unnest(return_unnamed_refcursor()) as (a int)
+)
+select * from rcq where a <= 50;
+ a
+----
+ 5
+ 50
+(2 rows)
+
+-- Check attempt to UPDATE/DELETE REFCURSOR fails
+update unnest(return_unnamed_refcursor()) set a = 2;
+ERROR: syntax error at or near "("
+LINE 1: update unnest(return_unnamed_refcursor()) set a = 2;
+ ^
+delete from unnest(return_unnamed_refcursor());
+ERROR: syntax error at or near "("
+LINE 1: delete from unnest(return_unnamed_refcursor());
+ ^
+-- Check type consistency
+select * from unnest(return_unnamed_refcursor()) as (a int, b int);
+ERROR: function return row and query-specified return row do not match
+DETAIL: Returned row contains 1 attribute, but query expects 2.
+select * from unnest(return_unnamed_refcursor()) as (a text);
+ERROR: function return row and query-specified return row do not match
+DETAIL: Returned type integer at ordinal position 1, but query expects text.
+select * from unnest(return_unnamed_refcursor()) as (a jsonb);
+ERROR: function return row and query-specified return row do not match
+DETAIL: Returned type integer at ordinal position 1, but query expects jsonb.
+-- Check consumption from REFCURSOR inside plpgsql FUNCTION
+create function refcursor_test3(input refcursor) returns refcursor as $$
+declare
+ rc refcursor;
+begin
+ open rc for select a, a+1 as a_plus_1 from unnest(input) as (a int);
+ return rc;
+end
+$$ language plpgsql;
+explain (verbose, costs off) select * from unnest(refcursor_test3(return_unnamed_refcursor())) as (a int, ap1 int) where a >= 50;
+ QUERY PLAN
+----------------------------------------------------------------------
+ Function Scan on pg_catalog.unnest
+ Output: a, ap1
+ Function Call: unnest(refcursor_test3(return_unnamed_refcursor()))
+ Filter: (unnest.a >= 50)
+(4 rows)
+
+select * from unnest(refcursor_test3(return_unnamed_refcursor())) as (a int, ap1 int) where a >= 50;
+ a | ap1
+-----+-----
+ 50 | 51
+ 500 | 501
+(2 rows)
+
+drop function refcursor_test3;
--
-- tests for cursors with named parameter arguments
--
diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql
index 70deadf..3328ebd 100644
--- a/src/test/regress/sql/plpgsql.sql
+++ b/src/test/regress/sql/plpgsql.sql
@@ -1805,6 +1805,69 @@ $$ language plpgsql;
select refcursor_test2(20000, 20000) as "Should be false",
refcursor_test2(20, 20) as "Should be true";
+-- Check ability to consume from REFCURSOR in SELECT
+explain (verbose, costs off) select * from unnest(return_unnamed_refcursor()) as (a int);
+select * from unnest(return_unnamed_refcursor()) as (a int);
+explain (verbose, costs off) select * from unnest(return_unnamed_refcursor()) as (a int) where a >= 50;
+select * from unnest(return_unnamed_refcursor()) as (a int) where a >= 50;
+explain (verbose, costs off) select * from unnest(return_unnamed_refcursor()) as (a int) order by a desc;
+select * from unnest(return_unnamed_refcursor()) as (a int) order by a desc;
+
+-- Check multiple scan of REFCURSOR
+explain (verbose, costs off) select * from (select 1 nr union all select 2) as scan, unnest(return_unnamed_refcursor()) as (a int) order by nr;
+select * from (select 1 nr union all select 2) as scan, unnest(return_unnamed_refcursor()) as (a int) order by nr;
+
+-- Check multiple reference to single REFCURSOR
+explain (verbose, costs off)
+ select * from
+ unnest(return_unnamed_refcursor()) as a(n int),
+ unnest(return_unnamed_refcursor()) as b(n int)
+ order by a.n, b.n;
+select * from
+ unnest(return_unnamed_refcursor()) as a(n int),
+ unnest(return_unnamed_refcursor()) as b(n int)
+ order by a.n, b.n;
+explain (verbose, costs off)
+ select r.cur::text, a.n, b.n from
+ (select return_unnamed_refcursor() cur) r,
+ unnest (r.cur) as a(n int),
+ unnest (r.cur) as b(n int)
+ order by r.cur::text, a.n, b.n;
+select r.cur::text, a.n, b.n from
+ (select return_unnamed_refcursor() cur) r,
+ unnest (r.cur) as a(n int),
+ unnest (r.cur) as b(n int)
+ order by r.cur::text, a.n, b.n;
+
+-- Check use of REFCURSOR in WITH
+with rcq as (
+ select * from unnest(return_unnamed_refcursor()) as (a int)
+)
+select * from rcq where a <= 50;
+
+-- Check attempt to UPDATE/DELETE REFCURSOR fails
+update unnest(return_unnamed_refcursor()) set a = 2;
+delete from unnest(return_unnamed_refcursor());
+
+-- Check type consistency
+select * from unnest(return_unnamed_refcursor()) as (a int, b int);
+select * from unnest(return_unnamed_refcursor()) as (a text);
+select * from unnest(return_unnamed_refcursor()) as (a jsonb);
+
+-- Check consumption from REFCURSOR inside plpgsql FUNCTION
+create function refcursor_test3(input refcursor) returns refcursor as $$
+declare
+ rc refcursor;
+begin
+ open rc for select a, a+1 as a_plus_1 from unnest(input) as (a int);
+ return rc;
+end
+$$ language plpgsql;
+explain (verbose, costs off) select * from unnest(refcursor_test3(return_unnamed_refcursor())) as (a int, ap1 int) where a >= 50;
+select * from unnest(refcursor_test3(return_unnamed_refcursor())) as (a int, ap1 int) where a >= 50;
+
+drop function refcursor_test3;
+
--
-- tests for cursors with named parameter arguments
--
Re: [WIP] UNNEST(REFCURSOR): allowing SELECT to consume data from a REFCURSOR
Dent John wrote:
I’ve made a revision of this patch.
Some comments:
* the commitfest app did not extract up the patch from the mail,
possibly because it's buried in the MIME structure of the mail
(using plain text instead of HTML messages might help with that).
The patch has no status in http://commitfest.cputube.org/
probably because of this too.
* unnest-refcursor-v3.patch needs a slight rebase because this chunk
in the Makefile fails
- regexp.o regproc.o ri_triggers.o rowtypes.o ruleutils.o \
+ refcursor.o regexp.o regproc.o ri_triggers.o rowtypes.o ruleutils.o \* I'm under the impression that refcursor_unnest() is functionally
equivalent to a plpgsql implementation like this:
create function unnest(x refcursor) returns setof record as $$
declare
r record;
begin
loop
fetch x into r;
exit when not found;
return next r;
end loop;
end $$ language plpgsql;
but it would differ in performance, because internally a materialization step
could be avoided, but only when the other patch "Allow FunctionScans to
pipeline results" gets in?
Or are performance benefits expected right away with this patch?
*
--- a/src/backend/utils/adt/arrayfuncs.c
+++ b/src/backend/utils/adt/arrayfuncs.c
@@ -5941,7 +5941,7 @@ array_fill_internal(ArrayType *dims, ArrayType *lbs, /*
- * UNNEST
+ * UNNEST (array)
*/This chunk looks unnecessary?
* some user-facing doc would be needed.
* Is it good to overload "unnest" rather than choosing a specific
function name?
Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite
Re: [WIP] UNNEST(REFCURSOR): allowing SELECT to consume data from a REFCURSOR
On 9 Jan 2020, at 17:43, Daniel Verite <daniel@manitou-mail.org> wrote:
[…]
(using plain text instead of HTML messages might help with that).
Thanks. I’ll do that next time.
[…] * unnest-refcursor-v3.patch needs a slight rebase because this chunk in the Makefile fails - regexp.o regproc.o ri_triggers.o rowtypes.o ruleutils.o \ + refcursor.o regexp.o regproc.o ri_triggers.o rowtypes.o ruleutils.o \
Likewise I’ll make that rebase in the next version.
* I'm under the impression that refcursor_unnest() is functionally
equivalent to a plpgsql implementation like this:[…]
but it would differ in performance, because internally a materialization step
could be avoided, but only when the other patch "Allow FunctionScans to
pipeline results" gets in?
Yes. That’s at least true if unnest(x) is used in the FROM. If it’s used in the SELECT, actually it can get the performance benefit right away. However, in the SELECT case, there’s a bit of a gotcha because anonymous records can’t easily be manipulated because they have no type information available. So to make a useful performance contribution, it does need to be combined with another change — either to make it FROM pipeline as in my other patch, or perhaps enabling anonymous record types to be cast or otherwise manipulated.
[…]
/*
- * UNNEST
+ * UNNEST (array)
*/This chunk looks unnecessary?
It was for purpose of disambiguating. But indeed it is unnecessary. Choosing a different name would avoid need for it.
* some user-facing doc would be needed.
Indeed. I fully intend that. I figured I’d get the concept on a firmer footing first.
* Is it good to overload "unnest" rather than choosing a specific
function name?
Yeah. I wondered about that. A couple of syntactically obvious ideas were:
SELECT … FROM TABLE (x) (which is what I think Oracle does, but is reserved)
SELECT … FROM CURSOR (x) (which seems likely to confuse, but, surprisingly, isn’t actually reserved)
SELECT … FROM FETCH (x) (which I quite like, but is reserved)
SELECT … FROM ROWS_FROM (x) (is okay, but conflicts with our ROWS FROM construct)
So I kind of landed on UNNEST(x) out of lack of better idea. EXPAND(x) could be an option. Actually ROWS_IN(x) or ROWS_OF(x) might work.
Do you have any preference or suggestion?
Thanks a lot for the feedback.
denty.
Re: [WIP] UNNEST(REFCURSOR): allowing SELECT to consume data from a REFCURSOR
Dent John wrote:
Yes. That’s at least true if unnest(x) is used in the FROM. If it’s used in
the SELECT, actually it can get the performance benefit right away
At a quick glance, I don't see it called in the select-list in any
of the regression tests. When trying it, it appears to crash (segfault):
postgres=# begin;
BEGIN
postgres=# declare c cursor for select oid::int as i, relname::text as r from
pg_class;
DECLARE CURSOR
postgres=# select unnest('c'::refcursor);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
The build is configured with:
./configure --enable-debug --with-icu --with-perl --enable-depend
Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite
Re: [WIP] UNNEST(REFCURSOR): allowing SELECT to consume data from a REFCURSOR
On 10 Jan 2020, at 15:45, Daniel Verite <daniel@manitou-mail.org> wrote:
At a quick glance, I don't see it called in the select-list in any
of the regression tests. […]
Yep. I didn’t test it because I figured it wasn’t particularly useful in that context. I’ll add some tests for that too once I get to the root of the problem.
postgres=# select unnest('c'::refcursor);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
Okay. That’s pretty bad, isn’t it.
It’s crashing when it’s checking that the returned tuple matches the declared return type in rsinfo->setDesc. Seems rsinfo->setDesc gets overwritten. So I think I have a memory management problem.
To be honest, I wasn’t fully sure I’d got a clear understanding of what is in what memory context, but things seemed to work so I figured it was close. Seems I was wrong. I need a bit of time to review. Leave it with me, but I guess it’ll take to next weekend before I get more time.
denty.
Re: [WIP] UNNEST(REFCURSOR): allowing SELECT to consume data from a REFCURSOR
Dent John wrote:
It’s crashing when it’s checking that the returned tuple matches the
declared return type in rsinfo->setDesc. Seems rsinfo->setDesc gets
overwritten. So I think I have a memory management problem.
What is the expected result anyway? A single column with a "record"
type? FWIW I notice that with plpgsql, this is not allowed to happen:
CREATE FUNCTION cursor_unnest(x refcursor) returns setof record
as $$
declare
r record;
begin
loop
fetch x into r;
exit when not found;
return next r;
end loop;
end $$ language plpgsql;
begin;
declare c cursor for select oid::int as i, relname::text as r from pg_class;
select cursor_unnest('c');
ERROR: set-valued function called in context that cannot accept a set
CONTEXT: PL/pgSQL function cursor_unnest(refcursor) line 8 at RETURN NEXT
Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite
Re: [WIP] UNNEST(REFCURSOR): allowing SELECT to consume data from a REFCURSOR
On 14 Jan 2020, at 14:53, Daniel Verite <daniel@manitou-mail.org> wrote:
What is the expected result anyway? A single column with a "record"
type? FWIW I notice that with plpgsql, this is not allowed to happen:
Hmm. How interesting.
I had not really investigated what happens in the case of a function returning SETOF (untyped) RECORD in a SELECT clause because, whatever the result, there’s no mechanism to access the individual fields.
As you highlight, it doesn’t work at all in plpgsql, and plperl is the same.
However, SQL language functions get away with it. For example, inspired by _pg_expandarray():
CREATE OR REPLACE FUNCTION public.my_pg_expandarray(anyarray)
RETURNS SETOF record
LANGUAGE sql
IMMUTABLE PARALLEL SAFE STRICT
AS $function$
select $1[s], s - pg_catalog.array_lower($1,1) + 1
from pg_catalog.generate_series(pg_catalog.array_lower($1,1),
pg_catalog.array_upper($1,1), 1) as g(s)
$function$
postgres=# select my_pg_expandarray (array[0, 1, 2, 3, 4]);
my_pg_expandarray
-------------------
(0,1)
(1,2)
(2,3)
(3,4)
(4,5)
(5 rows)
Back in the FROM clause, it’s possible to manipulate the individual fields:
postgres=# select b, a from my_pg_expandarray (array[0, 1, 2, 3, 4]) as r(a int, b int);
b | a
---+---
1 | 0
2 | 1
3 | 2
4 | 3
5 | 4
(5 rows)
It’s quite interesting. All the other PLs make explicit checks for rsinfo.expectedDesc being non-NULL, but fmgr_sql() explicitly calls out the contrary: “[…] note we do not require caller to provide an expectedDesc.” So I guess either there’s something special about the SQL PL, or perhaps the other PLs are just inheriting a pattern of being cautious.
Either way, though, there’s no way that I can see to "get at” the fields inside the anonymous record that is returned when the function is in the SELECT list.
But back to the failure, I still need to make it not crash. I guess it doesn’t matter whether I simply refuse to work if called from the SELECT list, or just return an anonymous record, like fmgr_sql() does.
d.
Re: [WIP] UNNEST(REFCURSOR): allowing SELECT to consume data from a REFCURSOR
On 11 Jan 2020, at 12:04, Dent John <denty@QQdd.eu> wrote:
On 10 Jan 2020, at 15:45, Daniel Verite <daniel@manitou-mail.org> wrote:
postgres=# select unnest('c'::refcursor);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.Okay. That’s pretty bad, isn’t it.
I’ve addressed the issue, which was due to me allocating the TupleDesc in the multi_call_memory_ctx, which seemed quite reasonable, but it actually needs to be in ecxt_per_query_memory. It seems tSRF-mode queries are much more sensitive to the misstep.
A v4 patch is attached, which also renames UNNEST(REFCURSOR) to ROWS_IN(REFCURSOR), adds a test case for use in tSRF mode, and makes some minor fixes to the support function.
I have not yet made steps towards documentation, nor yet rebased, so the Makefile chunk will probably still fail.
denty.
Attachments:
unnest-refcursor-v4.patchapplication/octet-stream; name=unnest-refcursor-v4.patch; x-unix-mode=0644Download
diff --git a/src/backend/utils/adt/Makefile b/src/backend/utils/adt/Makefile
index 5800432..7c71ed4 100644
--- a/src/backend/utils/adt/Makefile
+++ b/src/backend/utils/adt/Makefile
@@ -25,7 +25,7 @@ OBJS = acl.o amutils.o arrayfuncs.o array_expanded.o array_selfuncs.o \
pg_upgrade_support.o pgstatfuncs.o \
pseudotypes.o quote.o rangetypes.o rangetypes_gist.o \
rangetypes_selfuncs.o rangetypes_spgist.o rangetypes_typanalyze.o \
- regexp.o regproc.o ri_triggers.o rowtypes.o ruleutils.o \
+ refcursor.o regexp.o regproc.o ri_triggers.o rowtypes.o ruleutils.o \
selfuncs.o tid.o timestamp.o trigfuncs.o \
tsginidx.o tsgistidx.o tsquery.o tsquery_cleanup.o tsquery_gist.o \
tsquery_op.o tsquery_rewrite.o tsquery_util.o tsrank.o \
diff --git a/src/backend/utils/adt/refcursor.c b/src/backend/utils/adt/refcursor.c
new file mode 100644
index 0000000..e9b7c66
--- /dev/null
+++ b/src/backend/utils/adt/refcursor.c
@@ -0,0 +1,386 @@
+/*-------------------------------------------------------------------------
+ *
+ * refcursor.c
+ *
+ * IDENTIFICATION
+ * src/backend/utils/adt/refcursor.c
+ *
+ *-------------------------------------------------------------------------
+ */
+
+#include "postgres.h"
+
+#include "access/htup_details.h"
+#include "catalog/pg_type.h"
+#include "funcapi.h"
+#include "nodes/nodeFuncs.h"
+#include "nodes/supportnodes.h"
+#include "optimizer/optimizer.h"
+#include "tcop/pquery.h"
+#include "utils/builtins.h"
+#include "utils/datum.h"
+#include "utils/lsyscache.h"
+#include "utils/refcursor.h"
+#include "utils/typcache.h"
+
+
+typedef struct SingleSlotDestReceiver
+{
+ DestReceiver pub;
+
+ TupleTableSlot *received_slot;
+ TupleDesc tupdesc;
+} SingleSlotDestReceiver;
+
+/*
+ * sqlfunction_startup --- executor startup
+ */
+static void
+ssdr_startup(DestReceiver *self, int operation, TupleDesc typeinfo)
+{
+ SingleSlotDestReceiver *myState = (SingleSlotDestReceiver *) self;
+
+ myState->tupdesc = typeinfo;
+}
+
+/*
+ * sqlfunction_receive --- receive one tuple
+ */
+static bool
+ssdr_receive(TupleTableSlot *slot, DestReceiver *self)
+{
+ SingleSlotDestReceiver *myState = (SingleSlotDestReceiver *) self;
+
+ myState->received_slot = slot;
+
+ return true;
+}
+
+/*
+ * sqlfunction_shutdown --- executor end
+ */
+static void
+ssdr_shutdown(DestReceiver *self)
+{
+ /* no-op */
+}
+
+/*
+ * sqlfunction_destroy --- release DestReceiver object
+ */
+static void
+ssdr_destroy(DestReceiver *self)
+{
+ pfree(self);
+}
+
+/*
+ * CreateSingleSlotDestReceiver -- create a DestReceiver
+ * that acquires a single tupleslot
+ */
+static DestReceiver *
+CreateSingleSlotDestReceiver(void)
+{
+ SingleSlotDestReceiver *self = (SingleSlotDestReceiver *) palloc(sizeof(SingleSlotDestReceiver));
+
+ self->pub.receiveSlot = ssdr_receive;
+ self->pub.rStartup = ssdr_startup;
+ self->pub.rShutdown = ssdr_shutdown;
+ self->pub.rDestroy = ssdr_destroy;
+ self->pub.mydest = -1;
+
+ /* private fields will be set by ssdr_startup */
+
+ return (DestReceiver *) self;
+}
+
+/*
+ * ROWS_IN (REFCURSOR)
+ */
+Datum
+rows_in_refcursor(PG_FUNCTION_ARGS)
+{
+ typedef struct
+ {
+ Portal portal;
+ SingleSlotDestReceiver *dest;
+ TupleDesc tupdesc;
+ } rows_in_refcursor_fctx;
+
+ ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+ FuncCallContext *funcctx;
+ rows_in_refcursor_fctx *fctx;
+ FetchDirection direction;
+ uint64 howMany;
+ uint64 nfetched;
+
+ /* stuff done only on the first call of the function */
+ bool first_call = SRF_IS_FIRSTCALL();
+ if (first_call)
+ {
+ /* create a function context for cross-call persistence */
+ funcctx = SRF_FIRSTCALL_INIT();
+
+ /* Check to see if caller supports us returning a set */
+ if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("set-valued function called in context that cannot accept a set")));
+ if (!(rsinfo->allowedModes & SFRM_ValuePerCall))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("value per call mode required, but it is not " \
+ "allowed in this context")));
+
+ /*
+ * switch to memory context appropriate for multiple function calls
+ */
+ MemoryContext oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ /* allocate memory for user context */
+ fctx = (rows_in_refcursor_fctx *) palloc(sizeof(rows_in_refcursor_fctx));
+
+ fctx->dest = (SingleSlotDestReceiver *) CreateSingleSlotDestReceiver();
+
+ MemoryContextSwitchTo(oldcontext);
+
+ char *portal_name = text_to_cstring(PG_GETARG_TEXT_PP(0));
+
+ fctx->portal = GetPortalByName(portal_name);
+
+ /* Check that the portal exists */
+ if (!PortalIsValid(fctx->portal))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_CURSOR),
+ errmsg("cursor \"%s\" does not exist", portal_name)));
+
+ /* ensure the Portal is ready (has already been OPEN'ed) */
+ if (! (fctx->portal->status == PORTAL_DEFINED ||
+ fctx->portal->status == PORTAL_READY))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_CURSOR_STATE),
+ errmsg("cursor \"%s\" is not OPEN", portal_name)));
+
+ /*
+ * Ensure the Portal returns some results (so is not a utility
+ * command, or set of multiple statements.
+ */
+ if (! (fctx->portal->strategy == PORTAL_ONE_SELECT ||
+ fctx->portal->strategy == PORTAL_ONE_RETURNING ||
+ fctx->portal->strategy == PORTAL_ONE_MOD_WITH))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_CURSOR_DEFINITION),
+ errmsg("cursor \"%s\" does not return result set", portal_name)));
+
+ pfree(portal_name);
+
+ funcctx->user_fctx = fctx;
+ }
+
+ /* stuff done on every call of the function */
+ funcctx = SRF_PERCALL_SETUP();
+ fctx = funcctx->user_fctx;
+
+ rsinfo->returnMode = SFRM_ValuePerCall;
+
+ if (first_call)
+ {
+ direction = FETCH_ABSOLUTE; /* start from absolute position */
+ howMany = 1; /* position = 1 (top); also reads one row */
+ }
+ else
+ {
+ direction = FETCH_FORWARD; /* othrewise advance forward */
+ howMany = 1; /* count = 1 (read one row) */
+ }
+
+ /* Run the cursor... */
+ nfetched = PortalRunFetch (fctx->portal, direction, howMany,
+ (DestReceiver *) fctx->dest);
+
+ /*
+ * Initialise the Tuple Desriptor. (This can't be done until
+ * we have done our first fetch.)
+ */
+ if (first_call)
+ {
+ MemoryContext per_query_ctx = rsinfo->econtext->ecxt_per_query_memory;
+ MemoryContext oldcontext = MemoryContextSwitchTo(per_query_ctx);
+
+ fctx->tupdesc = CreateTupleDescCopy (fctx->dest->tupdesc);
+
+ /* For RECORD results, make sure a typmod has been assigned */
+ if (fctx->tupdesc->tdtypeid == RECORDOID &&
+ fctx->tupdesc->tdtypmod < 0)
+ assign_record_type_typmod(fctx->tupdesc);
+
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ rsinfo->setDesc = fctx->tupdesc;
+
+ Assert (nfetched <= 1);
+
+ if (nfetched == 1)
+ {
+ /*
+ * Convert the TableTupleSlot to a HeapTuple (doesn't
+ * materialise and doesn't copy unless unavoidable).
+ */
+ HeapTuple tuple = ExecFetchSlotHeapTuple (fctx->dest->received_slot,
+ /* materialise */ false,
+ NULL);
+
+ /*
+ * Avoid making a copy if the HeapTuple is already
+ * fully in memory and marked with correct typeid/typmod.
+ */
+ Datum datum = PointerGetDatum (tuple->t_data);
+ if (HeapTupleHasExternal(tuple) ||
+ HeapTupleHeaderGetTypeId(tuple->t_data) != fctx->tupdesc->tdtypeid ||
+ HeapTupleHeaderGetTypMod(tuple->t_data) != fctx->tupdesc->tdtypmod)
+ {
+ /*
+ * Copy the tuple as a Datum, ensuring it is
+ * fully in memory in the process.
+ */
+ datum = heap_copy_tuple_as_datum (tuple, fctx->tupdesc);
+ }
+
+ /*
+ * Obtain HeapTupleHeader for the Datum, which is in
+ * memory, so should not require a copy.
+ */
+ HeapTupleHeader result = DatumGetHeapTupleHeader (datum);
+
+ SRF_RETURN_NEXT (funcctx, PointerGetDatum (result));
+ }
+ else /* no rows retrieved */
+ {
+ /* it will have been pfree()'ed by ssdr_destroy() */
+ fctx->dest = NULL;
+
+ /* fctx itself will be released when multi_call_memory_ctx goes. */
+
+ SRF_RETURN_DONE(funcctx);
+ }
+}
+
+
+/*
+ * Planner support function for ROWS_IN (REFCURSOR)
+ */
+Datum
+rows_in_refcursor_support(PG_FUNCTION_ARGS)
+{
+ Node *rawreq = (Node *) PG_GETARG_POINTER(0);
+ Node *ret = NULL;
+
+ if (!IsA(rawreq, SupportRequestRows)
+ && !IsA (rawreq, SupportRequestCost))
+ PG_RETURN_POINTER(NULL);
+
+ Node *req_node = NULL;
+ if (IsA(rawreq, SupportRequestRows))
+ {
+ SupportRequestRows *req = (SupportRequestRows *) rawreq;
+
+ req_node = req->node;
+ }
+ else if (IsA (rawreq, SupportRequestCost))
+ {
+ SupportRequestCost *req = (SupportRequestCost *) rawreq;
+
+ req_node = req->node;
+ }
+
+ /* The call to ROWS_IN should be in a FuncExpr node. */
+ if (!is_funcclause(req_node))
+ PG_RETURN_POINTER(NULL);
+
+ List *args = ((FuncExpr *) req_node)->args;
+ if (args == NULL)
+ PG_RETURN_POINTER(NULL);
+
+ Node *arg1 = linitial(args);
+
+ /*
+ * We can only estimate the cost if the REFCURSOR is
+ * already simplified to a Const.
+ */
+ if (!IsA (arg1, Const))
+ PG_RETURN_POINTER(NULL);
+
+ Const *cexpr = (Const *) arg1;
+
+ if (cexpr->constisnull)
+ PG_RETURN_POINTER(NULL);
+
+ if (cexpr->consttype != REFCURSOROID)
+ PG_RETURN_POINTER(NULL);
+
+ /*
+ * We can ignore a check on the collation because we are not
+ * interested in sorting, and typemod because REFCURSOR has
+ * no modifyable attributes.
+ */
+ Oid typoutput;
+ bool typIsVarlena;
+ getTypeOutputInfo(cexpr->consttype, &typoutput, &typIsVarlena);
+
+ char *portal_name = OidOutputFunctionCall(typoutput, cexpr->constvalue);
+
+ Portal portal = GetPortalByName(portal_name);
+
+ /* Check that the portal exists */
+ if (!PortalIsValid(portal))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_CURSOR),
+ errmsg("cursor \"%s\" does not exist", portal_name)));
+
+ /* ensure the Portal is ready (has already been OPEN'ed) */
+ if (! (portal->status == PORTAL_DEFINED ||
+ portal->status == PORTAL_READY))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_CURSOR_STATE),
+ errmsg("cursor \"%s\" is not OPEN", portal_name)));
+
+ /*
+ * Ensure the Portal returns some results (so is
+ * not a utility command, or set of multiple statements.
+ */
+ if (! (portal->strategy == PORTAL_ONE_SELECT ||
+ portal->strategy == PORTAL_ONE_RETURNING ||
+ portal->strategy == PORTAL_ONE_MOD_WITH))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_CURSOR_DEFINITION),
+ errmsg("cursor \"%s\" does not return result set", portal_name)));
+
+ QueryDesc *qdesc = portal->queryDesc;
+ if (qdesc == NULL)
+ PG_RETURN_POINTER(NULL);
+
+ PlanState *planstate = qdesc->planstate;
+ if (planstate == NULL)
+ PG_RETURN_POINTER(NULL);
+
+ if (IsA(rawreq, SupportRequestRows))
+ {
+ SupportRequestRows *req = (SupportRequestRows *) rawreq;
+
+ req->rows = planstate->plan->plan_rows;
+ }
+ else if (IsA (rawreq, SupportRequestCost))
+ {
+ SupportRequestCost *req = (SupportRequestCost *) rawreq;
+
+ req->startup = planstate->plan->startup_cost;
+ req->per_tuple = (planstate->plan->total_cost - planstate->plan->startup_cost);
+ if (planstate->plan->plan_rows != 0.0)
+ req->per_tuple /= planstate->plan->plan_rows;
+ }
+
+ ret = (Node *) rawreq;
+
+ PG_RETURN_POINTER(ret);
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index d352f9a..fa30b8c 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -1550,6 +1550,13 @@
{ oid => '3996', descr => 'planner support for array_unnest',
proname => 'array_unnest_support', prorettype => 'internal',
proargtypes => 'internal', prosrc => 'array_unnest_support' },
+{ oid => '12921', descr => 'expand refcursor to set of rows',
+ proname => 'rows_in', prorows => '100', prosupport => 'rows_in_refcursor_support',
+ proretset => 't', prorettype => 'record', proargtypes => 'refcursor',
+ prosrc => 'rows_in_refcursor' },
+{ oid => '12923', descr => 'planner support for rows_in',
+ proname => 'rows_in_refcursor_support', prorettype => 'internal',
+ proargtypes => 'internal', prosrc => 'rows_in_refcursor_support' },
{ oid => '3167',
descr => 'remove any occurrences of an element from an array',
proname => 'array_remove', proisstrict => 'f', prorettype => 'anyarray',
diff --git a/src/include/utils/refcursor.h b/src/include/utils/refcursor.h
new file mode 100644
index 0000000..e636193
--- /dev/null
+++ b/src/include/utils/refcursor.h
@@ -0,0 +1,15 @@
+/*-------------------------------------------------------------------------
+*
+* refcursor.c
+*
+* IDENTIFICATION
+* src/include/utils/refcursor.c
+*
+*-------------------------------------------------------------------------
+*/
+
+#ifndef refcursor_h
+#define refcursor_h
+
+
+#endif /* refcursor_h */
diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out
index e85b294..9ef14a0 100644
--- a/src/test/regress/expected/plpgsql.out
+++ b/src/test/regress/expected/plpgsql.out
@@ -2078,6 +2078,298 @@ select refcursor_test2(20000, 20000) as "Should be false",
f | t
(1 row)
+-- Check ability to consume from REFCURSOR in SELECT
+explain (verbose, costs off) select * from rows_in(return_unnamed_refcursor()) as (a int);
+ QUERY PLAN
+------------------------------------------------------
+ Function Scan on pg_catalog.rows_in
+ Output: a
+ Function Call: rows_in(return_unnamed_refcursor())
+(3 rows)
+
+select * from rows_in(return_unnamed_refcursor()) as (a int);
+ a
+-----
+ 5
+ 50
+ 500
+(3 rows)
+
+explain (verbose, costs off) select * from rows_in(return_unnamed_refcursor()) as (a int) where a >= 50;
+ QUERY PLAN
+------------------------------------------------------
+ Function Scan on pg_catalog.rows_in
+ Output: a
+ Function Call: rows_in(return_unnamed_refcursor())
+ Filter: (rows_in.a >= 50)
+(4 rows)
+
+select * from rows_in(return_unnamed_refcursor()) as (a int) where a >= 50;
+ a
+-----
+ 50
+ 500
+(2 rows)
+
+explain (verbose, costs off) select * from rows_in(return_unnamed_refcursor()) as (a int) order by a desc;
+ QUERY PLAN
+------------------------------------------------------------
+ Sort
+ Output: a
+ Sort Key: rows_in.a DESC
+ -> Function Scan on pg_catalog.rows_in
+ Output: a
+ Function Call: rows_in(return_unnamed_refcursor())
+(6 rows)
+
+select * from rows_in(return_unnamed_refcursor()) as (a int) order by a desc;
+ a
+-----
+ 500
+ 50
+ 5
+(3 rows)
+
+-- Check multiple scan of REFCURSOR
+explain (verbose, costs off) select * from (select 1 nr union all select 2) as scan, rows_in(return_unnamed_refcursor()) as (a int) order by nr;
+ QUERY PLAN
+------------------------------------------------------------------
+ Sort
+ Output: (1), rows_in.a
+ Sort Key: (1)
+ -> Nested Loop
+ Output: (1), rows_in.a
+ -> Function Scan on pg_catalog.rows_in
+ Output: rows_in.a
+ Function Call: rows_in(return_unnamed_refcursor())
+ -> Materialize
+ Output: (1)
+ -> Append
+ -> Result
+ Output: 1
+ -> Result
+ Output: 2
+(15 rows)
+
+select * from (select 1 nr union all select 2) as scan, rows_in(return_unnamed_refcursor()) as (a int) order by nr;
+ nr | a
+----+-----
+ 1 | 5
+ 1 | 50
+ 1 | 500
+ 2 | 5
+ 2 | 50
+ 2 | 500
+(6 rows)
+
+-- Check multiple reference to single REFCURSOR
+explain (verbose, costs off)
+ select * from
+ rows_in(return_unnamed_refcursor()) as a(n int),
+ rows_in(return_unnamed_refcursor()) as b(n int)
+ order by a.n, b.n;
+ QUERY PLAN
+------------------------------------------------------------------
+ Sort
+ Output: a.n, b.n
+ Sort Key: a.n, b.n
+ -> Nested Loop
+ Output: a.n, b.n
+ -> Function Scan on pg_catalog.rows_in a
+ Output: a.n
+ Function Call: rows_in(return_unnamed_refcursor())
+ -> Function Scan on pg_catalog.rows_in b
+ Output: b.n
+ Function Call: rows_in(return_unnamed_refcursor())
+(11 rows)
+
+select * from
+ rows_in(return_unnamed_refcursor()) as a(n int),
+ rows_in(return_unnamed_refcursor()) as b(n int)
+ order by a.n, b.n;
+ n | n
+-----+-----
+ 5 | 5
+ 5 | 50
+ 5 | 500
+ 50 | 5
+ 50 | 50
+ 50 | 500
+ 500 | 5
+ 500 | 50
+ 500 | 500
+(9 rows)
+
+explain (verbose, costs off)
+ select r.cur::text, a.n, b.n from
+ (select return_unnamed_refcursor() cur) r,
+ rows_in (r.cur) as a(n int),
+ rows_in (r.cur) as b(n int)
+ order by r.cur::text, a.n, b.n;
+ QUERY PLAN
+--------------------------------------------------------------------------
+ Sort
+ Output: (((return_unnamed_refcursor()))::text), a.n, b.n
+ Sort Key: (((return_unnamed_refcursor()))::text), a.n, b.n
+ -> Nested Loop
+ Output: ((return_unnamed_refcursor()))::text, a.n, b.n
+ -> Nested Loop
+ Output: (return_unnamed_refcursor()), a.n
+ -> Result
+ Output: return_unnamed_refcursor()
+ -> Function Scan on pg_catalog.rows_in a
+ Output: a.n
+ Function Call: rows_in((return_unnamed_refcursor()))
+ -> Function Scan on pg_catalog.rows_in b
+ Output: b.n
+ Function Call: rows_in((return_unnamed_refcursor()))
+(15 rows)
+
+select r.cur::text, a.n, b.n from
+ (select return_unnamed_refcursor() cur) r,
+ rows_in (r.cur) as a(n int),
+ rows_in (r.cur) as b(n int)
+ order by r.cur::text, a.n, b.n;
+ cur | n | n
+---------------------+-----+-----
+ <unnamed portal 11> | 5 | 5
+ <unnamed portal 11> | 5 | 50
+ <unnamed portal 11> | 5 | 500
+ <unnamed portal 11> | 50 | 5
+ <unnamed portal 11> | 50 | 50
+ <unnamed portal 11> | 50 | 500
+ <unnamed portal 11> | 500 | 5
+ <unnamed portal 11> | 500 | 50
+ <unnamed portal 11> | 500 | 500
+(9 rows)
+
+-- Check use of REFCURSOR in WITH
+with rcq as (
+ select * from rows_in(return_unnamed_refcursor()) as (a int)
+)
+select * from rcq where a <= 50;
+ a
+----
+ 5
+ 50
+(2 rows)
+
+-- Check attempt to UPDATE/DELETE REFCURSOR fails
+update rows_in(return_unnamed_refcursor()) set a = 2;
+ERROR: syntax error at or near "("
+LINE 1: update rows_in(return_unnamed_refcursor()) set a = 2;
+ ^
+delete from rows_in(return_unnamed_refcursor());
+ERROR: syntax error at or near "("
+LINE 1: delete from rows_in(return_unnamed_refcursor());
+ ^
+-- Check type consistency
+select * from rows_in(return_unnamed_refcursor()) as (a int, b int);
+ERROR: function return row and query-specified return row do not match
+DETAIL: Returned row contains 1 attribute, but query expects 2.
+select * from rows_in(return_unnamed_refcursor()) as (a text);
+ERROR: function return row and query-specified return row do not match
+DETAIL: Returned type integer at ordinal position 1, but query expects text.
+select * from rows_in(return_unnamed_refcursor()) as (a jsonb);
+ERROR: function return row and query-specified return row do not match
+DETAIL: Returned type integer at ordinal position 1, but query expects jsonb.
+-- Check consumption from REFCURSOR inside plpgsql FUNCTION
+create function refcursor_test3(input refcursor) returns refcursor as $$
+declare
+ rc refcursor;
+begin
+ open rc for select a, a+1 as a_plus_1 from rows_in(input) as (a int);
+ return rc;
+end
+$$ language plpgsql;
+explain (verbose, costs off) select * from rows_in(refcursor_test3(return_unnamed_refcursor())) as (a int, ap1 int) where a >= 50;
+ QUERY PLAN
+-----------------------------------------------------------------------
+ Function Scan on pg_catalog.rows_in
+ Output: a, ap1
+ Function Call: rows_in(refcursor_test3(return_unnamed_refcursor()))
+ Filter: (rows_in.a >= 50)
+(4 rows)
+
+select * from rows_in(refcursor_test3(return_unnamed_refcursor())) as (a int, ap1 int) where a >= 50;
+ a | ap1
+-----+-----
+ 50 | 51
+ 500 | 501
+(2 rows)
+
+drop function refcursor_test3;
+-- Check consumption from REFCURSOR defined with USING
+create function refcursor_test4(minimum int) returns refcursor as $$
+declare
+ rc refcursor;
+begin
+ OPEN rc FOR EXECUTE 'select a from rc_test where a >= $1'
+ USING (minimum);
+ return rc;
+end
+$$ language plpgsql;
+explain (verbose, costs off)
+ select * from rows_in(refcursor_test4(50)) as (a int);
+ QUERY PLAN
+-----------------------------------------------
+ Function Scan on pg_catalog.rows_in
+ Output: a
+ Function Call: rows_in(refcursor_test4(50))
+(3 rows)
+
+select * from rows_in(refcursor_test4(50)) as (a int);
+ a
+-----
+ 50
+ 500
+(2 rows)
+
+drop function refcursor_test4;
+-- Check consumption from REFCURSOR defined in immediate mode
+begin;
+declare c cursor for
+ select 1 as i, 'one' v
+ union all
+ select 2, 'two';
+select * from rows_in('c'::refcursor) as (i int, v text);
+ i | v
+---+-----
+ 1 | one
+ 2 | two
+(2 rows)
+
+rollback;
+-- Check consumption from REFCURSOR in target list (tSRF)
+-- (This isn't particularly useful, but it should work.)
+begin;
+declare c cursor for
+ select 1 as i, 'one' v
+ union all
+ select 2, 'two';
+select rows_in('c'::refcursor);
+ rows_in
+---------
+ (1,one)
+ (2,two)
+(2 rows)
+
+rollback;
+begin;
+declare c cursor for
+ select 1 as i, 'one' v
+ union all
+ select 2, 'two';
+select rows_in('c'::refcursor), * from rows_in('c'::refcursor) as (i int, v text);
+ rows_in | i | v
+---------+---+-----
+ (1,one) | 1 | one
+ (2,two) | 1 | one
+ (1,one) | 2 | two
+ (2,two) | 2 | two
+(4 rows)
+
+rollback;
--
-- tests for cursors with named parameter arguments
--
diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql
index 70deadf..eb77410 100644
--- a/src/test/regress/sql/plpgsql.sql
+++ b/src/test/regress/sql/plpgsql.sql
@@ -1805,6 +1805,111 @@ $$ language plpgsql;
select refcursor_test2(20000, 20000) as "Should be false",
refcursor_test2(20, 20) as "Should be true";
+-- Check ability to consume from REFCURSOR in SELECT
+explain (verbose, costs off) select * from rows_in(return_unnamed_refcursor()) as (a int);
+select * from rows_in(return_unnamed_refcursor()) as (a int);
+explain (verbose, costs off) select * from rows_in(return_unnamed_refcursor()) as (a int) where a >= 50;
+select * from rows_in(return_unnamed_refcursor()) as (a int) where a >= 50;
+explain (verbose, costs off) select * from rows_in(return_unnamed_refcursor()) as (a int) order by a desc;
+select * from rows_in(return_unnamed_refcursor()) as (a int) order by a desc;
+
+-- Check multiple scan of REFCURSOR
+explain (verbose, costs off) select * from (select 1 nr union all select 2) as scan, rows_in(return_unnamed_refcursor()) as (a int) order by nr;
+select * from (select 1 nr union all select 2) as scan, rows_in(return_unnamed_refcursor()) as (a int) order by nr;
+
+-- Check multiple reference to single REFCURSOR
+explain (verbose, costs off)
+ select * from
+ rows_in(return_unnamed_refcursor()) as a(n int),
+ rows_in(return_unnamed_refcursor()) as b(n int)
+ order by a.n, b.n;
+select * from
+ rows_in(return_unnamed_refcursor()) as a(n int),
+ rows_in(return_unnamed_refcursor()) as b(n int)
+ order by a.n, b.n;
+explain (verbose, costs off)
+ select r.cur::text, a.n, b.n from
+ (select return_unnamed_refcursor() cur) r,
+ rows_in (r.cur) as a(n int),
+ rows_in (r.cur) as b(n int)
+ order by r.cur::text, a.n, b.n;
+select r.cur::text, a.n, b.n from
+ (select return_unnamed_refcursor() cur) r,
+ rows_in (r.cur) as a(n int),
+ rows_in (r.cur) as b(n int)
+ order by r.cur::text, a.n, b.n;
+
+-- Check use of REFCURSOR in WITH
+with rcq as (
+ select * from rows_in(return_unnamed_refcursor()) as (a int)
+)
+select * from rcq where a <= 50;
+
+-- Check attempt to UPDATE/DELETE REFCURSOR fails
+update rows_in(return_unnamed_refcursor()) set a = 2;
+delete from rows_in(return_unnamed_refcursor());
+
+-- Check type consistency
+select * from rows_in(return_unnamed_refcursor()) as (a int, b int);
+select * from rows_in(return_unnamed_refcursor()) as (a text);
+select * from rows_in(return_unnamed_refcursor()) as (a jsonb);
+
+-- Check consumption from REFCURSOR inside plpgsql FUNCTION
+create function refcursor_test3(input refcursor) returns refcursor as $$
+declare
+ rc refcursor;
+begin
+ open rc for select a, a+1 as a_plus_1 from rows_in(input) as (a int);
+ return rc;
+end
+$$ language plpgsql;
+explain (verbose, costs off) select * from rows_in(refcursor_test3(return_unnamed_refcursor())) as (a int, ap1 int) where a >= 50;
+select * from rows_in(refcursor_test3(return_unnamed_refcursor())) as (a int, ap1 int) where a >= 50;
+
+drop function refcursor_test3;
+
+-- Check consumption from REFCURSOR defined with USING
+create function refcursor_test4(minimum int) returns refcursor as $$
+declare
+ rc refcursor;
+begin
+ OPEN rc FOR EXECUTE 'select a from rc_test where a >= $1'
+ USING (minimum);
+ return rc;
+end
+$$ language plpgsql;
+explain (verbose, costs off)
+ select * from rows_in(refcursor_test4(50)) as (a int);
+select * from rows_in(refcursor_test4(50)) as (a int);
+
+drop function refcursor_test4;
+
+-- Check consumption from REFCURSOR defined in immediate mode
+begin;
+declare c cursor for
+ select 1 as i, 'one' v
+ union all
+ select 2, 'two';
+select * from rows_in('c'::refcursor) as (i int, v text);
+rollback;
+
+-- Check consumption from REFCURSOR in target list (tSRF)
+-- (This isn't particularly useful, but it should work.)
+begin;
+declare c cursor for
+ select 1 as i, 'one' v
+ union all
+ select 2, 'two';
+select rows_in('c'::refcursor);
+rollback;
+begin;
+declare c cursor for
+ select 1 as i, 'one' v
+ union all
+ select 2, 'two';
+select rows_in('c'::refcursor), * from rows_in('c'::refcursor) as (i int, v text);
+rollback;
+
--
-- tests for cursors with named parameter arguments
--
Re: [WIP] UNNEST(REFCURSOR): allowing SELECT to consume data from a REFCURSOR
On 19 Jan 2020, at 22:30, Dent John <denty@QQdd.eu> wrote:
I have not yet made steps towards documentation, nor yet rebased, so the Makefile chunk will probably still fail.
Attached patch addresses these points, so should now apply cleanly agains dev.
I also changed the OID assigned to ROWS_IN and its support function.
In passing, I noticed there is one existing function that can consume and make good use of ROWS_IN’s result when used in the target list, which is row_to_json. This is good, as it makes ROWS_IN useful even outside of a change to allow results in the FROM to be pipelined. I’ve called out row_to_json specifically in the documentation change.
denty.
Attachments:
unnest-refcursor-v5a.patchapplication/octet-stream; name=unnest-refcursor-v5a.patch; x-unix-mode=0644Download
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 6c4359d..c9b0d28 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -16803,6 +16803,113 @@ SELECT * FROM pg_ls_dir('.') WITH ORDINALITY AS t(ls,n);
</sect1>
+ <sect1 id="functions-cursor">
+ <title>Cursor Manipulation Functions</title>
+
+ <para>
+ This section describes functions that cursors to be manipulated
+ in normal <command>SELECT</command> queries.
+ </para>
+
+ <para>
+ The creation of cursors using <xref linkend="sql-declare"/>
+ or within a procedural language such as
+ <application>PL/pgSQL</application>
+ (see <xref linkend="plpgsql-cursor-declarations"/>), and other
+ manipulaton outside of a <command>SELECT</command> query is
+ described elsewhere in this manual.
+ </para>
+
+ <sect2>
+ <title><literal><function>ROWS_IN(<parameter>cursor</parameter>)</function></literal></title>
+
+ <indexterm zone="functions-cursor">
+ <primary>cursor manipulation functions</primary>
+ <secondary>functions</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>rows_in</primary>
+ </indexterm>
+
+<synopsis>
+<function>ROWS_IN(<parameter>cursor</parameter> <type>REFCURSOR</type>)
+ RETURNS <type>SETOF RECORD</type></function>
+</synopsis>
+
+ <para>
+ <function>ROWS_IN</function> retrieves rows from a previously-created
+ cursor, or a <type>REFCURSOR</type> returned by a function. It may be
+ considered as equivalent to <command>FETCH ALL</command>, but able
+ to be integrated into a general query.
+ </para>
+
+ <para>
+ <function>ROWS_IN</function> would typically be placed in the
+ FROM clause. Since the columns specified in the
+ <type>REFCURSOR</type> may not be determinable at query plan
+ time, it is required to use a column definition list. For example:
+ </para>
+
+<programlisting>
+SELECT cur.i, cur.t
+ FROM
+ ROWS_IN(fn(...)) AS cur(i int, t text);
+</programlisting>
+
+ <para>
+ The <type>REFCURSOR</type> must be open, and the query must be a
+ <command>SELECT</command> statement. If the <type>REFCURSOR</type>’s
+ output does not
+ correspond to that declared in the query, an error is raised at
+ query execution time. <command>UPDATE</command> and other DML
+ statements are not permitted, even if they return a set of rows.
+ Even if the <type>REFCURSOR</type> returns rows from a physical table, they may
+ not be <command>SELECT</command>ed <literal>FOR UPDATE</literal>.
+ </para>
+
+ <para>
+ It is also possible to place <function>ROWS_IN</function> in the
+ <command>SELECT</command> list. Tools to manipulate the results from an untyped
+ <type>RECORD</type> are relatively few, making its use limited in that context.
+ One exception is <function>row_to_json</function> (see
+ <xref linkend="functions-json-creation-table"/>).
+ </para>
+
+ <note>
+ <para>
+ It is acceptable to use multiple <function>ROWS_IN</function> constructs
+ referencing multiple <type>REFCURSOR</type>s in the same
+ query. If several references
+ are made to the same <type>REFCURSOR</type>, place the construction
+ inside a <literal>WITH</literal> subquery
+ marked <literal>MATERIALIZE ALWAYS</literal>.
+ </para>
+ </note>
+
+ <note>
+ <para>
+ When placed in the <literal>FROM</literal> clause,
+ <function>ROWS_IN</function> results are staged
+ before being processed by the executor. Do not rely upon this
+ behaviour as it may be changed in future. When placed in the
+ <command>SELECT</command> list, rows are always released one at a time.
+ </para>
+ </note>
+
+ <note>
+ <para>
+ When the <type>REFCURSOR</type> is returned from a <literal>VOLATILE</literal>
+ function, the query associated with the <type>REFCURSOR</type> is
+ always executed as is. When the <type>REFCURSOR</type> is returned
+ from a <literal>STABLE</literal> or <literal>IMMUTABLE</literal> function, in
+ future, the planner may be able to inline the <type>REFCURSOR</type>
+ query into the outer query.
+ </para>
+ </note>
+ </sect2>
+ </sect1>
+
<sect1 id="functions-info">
<title>System Information Functions and Operators</title>
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index 2987a55..db1a6bd 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -3427,6 +3427,16 @@ COMMIT;
</programlisting>
</para>
</sect3>
+ <sect3>
+ <title>Querying cursors</title>
+
+ <para>
+ Section <xref linkend="functions-cursor"/> discusses the use of
+ <function>ROWS_IN</function> which allows a returned
+ <type>REFCURSOR</type> to be queried in a normal
+ <command>SELECT</command> command.
+ </para>
+ </sect3>
</sect2>
<sect2 id="plpgsql-cursor-for-loop">
diff --git a/src/backend/utils/adt/Makefile b/src/backend/utils/adt/Makefile
index 790d7a2..ae5d6d3 100644
--- a/src/backend/utils/adt/Makefile
+++ b/src/backend/utils/adt/Makefile
@@ -81,6 +81,7 @@ OBJS = \
rangetypes_selfuncs.o \
rangetypes_spgist.o \
rangetypes_typanalyze.o \
+ refcursor.o \
regexp.o \
regproc.o \
ri_triggers.o \
diff --git a/src/backend/utils/adt/refcursor.c b/src/backend/utils/adt/refcursor.c
new file mode 100644
index 0000000..e9b7c66
--- /dev/null
+++ b/src/backend/utils/adt/refcursor.c
@@ -0,0 +1,386 @@
+/*-------------------------------------------------------------------------
+ *
+ * refcursor.c
+ *
+ * IDENTIFICATION
+ * src/backend/utils/adt/refcursor.c
+ *
+ *-------------------------------------------------------------------------
+ */
+
+#include "postgres.h"
+
+#include "access/htup_details.h"
+#include "catalog/pg_type.h"
+#include "funcapi.h"
+#include "nodes/nodeFuncs.h"
+#include "nodes/supportnodes.h"
+#include "optimizer/optimizer.h"
+#include "tcop/pquery.h"
+#include "utils/builtins.h"
+#include "utils/datum.h"
+#include "utils/lsyscache.h"
+#include "utils/refcursor.h"
+#include "utils/typcache.h"
+
+
+typedef struct SingleSlotDestReceiver
+{
+ DestReceiver pub;
+
+ TupleTableSlot *received_slot;
+ TupleDesc tupdesc;
+} SingleSlotDestReceiver;
+
+/*
+ * sqlfunction_startup --- executor startup
+ */
+static void
+ssdr_startup(DestReceiver *self, int operation, TupleDesc typeinfo)
+{
+ SingleSlotDestReceiver *myState = (SingleSlotDestReceiver *) self;
+
+ myState->tupdesc = typeinfo;
+}
+
+/*
+ * sqlfunction_receive --- receive one tuple
+ */
+static bool
+ssdr_receive(TupleTableSlot *slot, DestReceiver *self)
+{
+ SingleSlotDestReceiver *myState = (SingleSlotDestReceiver *) self;
+
+ myState->received_slot = slot;
+
+ return true;
+}
+
+/*
+ * sqlfunction_shutdown --- executor end
+ */
+static void
+ssdr_shutdown(DestReceiver *self)
+{
+ /* no-op */
+}
+
+/*
+ * sqlfunction_destroy --- release DestReceiver object
+ */
+static void
+ssdr_destroy(DestReceiver *self)
+{
+ pfree(self);
+}
+
+/*
+ * CreateSingleSlotDestReceiver -- create a DestReceiver
+ * that acquires a single tupleslot
+ */
+static DestReceiver *
+CreateSingleSlotDestReceiver(void)
+{
+ SingleSlotDestReceiver *self = (SingleSlotDestReceiver *) palloc(sizeof(SingleSlotDestReceiver));
+
+ self->pub.receiveSlot = ssdr_receive;
+ self->pub.rStartup = ssdr_startup;
+ self->pub.rShutdown = ssdr_shutdown;
+ self->pub.rDestroy = ssdr_destroy;
+ self->pub.mydest = -1;
+
+ /* private fields will be set by ssdr_startup */
+
+ return (DestReceiver *) self;
+}
+
+/*
+ * ROWS_IN (REFCURSOR)
+ */
+Datum
+rows_in_refcursor(PG_FUNCTION_ARGS)
+{
+ typedef struct
+ {
+ Portal portal;
+ SingleSlotDestReceiver *dest;
+ TupleDesc tupdesc;
+ } rows_in_refcursor_fctx;
+
+ ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+ FuncCallContext *funcctx;
+ rows_in_refcursor_fctx *fctx;
+ FetchDirection direction;
+ uint64 howMany;
+ uint64 nfetched;
+
+ /* stuff done only on the first call of the function */
+ bool first_call = SRF_IS_FIRSTCALL();
+ if (first_call)
+ {
+ /* create a function context for cross-call persistence */
+ funcctx = SRF_FIRSTCALL_INIT();
+
+ /* Check to see if caller supports us returning a set */
+ if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("set-valued function called in context that cannot accept a set")));
+ if (!(rsinfo->allowedModes & SFRM_ValuePerCall))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("value per call mode required, but it is not " \
+ "allowed in this context")));
+
+ /*
+ * switch to memory context appropriate for multiple function calls
+ */
+ MemoryContext oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ /* allocate memory for user context */
+ fctx = (rows_in_refcursor_fctx *) palloc(sizeof(rows_in_refcursor_fctx));
+
+ fctx->dest = (SingleSlotDestReceiver *) CreateSingleSlotDestReceiver();
+
+ MemoryContextSwitchTo(oldcontext);
+
+ char *portal_name = text_to_cstring(PG_GETARG_TEXT_PP(0));
+
+ fctx->portal = GetPortalByName(portal_name);
+
+ /* Check that the portal exists */
+ if (!PortalIsValid(fctx->portal))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_CURSOR),
+ errmsg("cursor \"%s\" does not exist", portal_name)));
+
+ /* ensure the Portal is ready (has already been OPEN'ed) */
+ if (! (fctx->portal->status == PORTAL_DEFINED ||
+ fctx->portal->status == PORTAL_READY))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_CURSOR_STATE),
+ errmsg("cursor \"%s\" is not OPEN", portal_name)));
+
+ /*
+ * Ensure the Portal returns some results (so is not a utility
+ * command, or set of multiple statements.
+ */
+ if (! (fctx->portal->strategy == PORTAL_ONE_SELECT ||
+ fctx->portal->strategy == PORTAL_ONE_RETURNING ||
+ fctx->portal->strategy == PORTAL_ONE_MOD_WITH))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_CURSOR_DEFINITION),
+ errmsg("cursor \"%s\" does not return result set", portal_name)));
+
+ pfree(portal_name);
+
+ funcctx->user_fctx = fctx;
+ }
+
+ /* stuff done on every call of the function */
+ funcctx = SRF_PERCALL_SETUP();
+ fctx = funcctx->user_fctx;
+
+ rsinfo->returnMode = SFRM_ValuePerCall;
+
+ if (first_call)
+ {
+ direction = FETCH_ABSOLUTE; /* start from absolute position */
+ howMany = 1; /* position = 1 (top); also reads one row */
+ }
+ else
+ {
+ direction = FETCH_FORWARD; /* othrewise advance forward */
+ howMany = 1; /* count = 1 (read one row) */
+ }
+
+ /* Run the cursor... */
+ nfetched = PortalRunFetch (fctx->portal, direction, howMany,
+ (DestReceiver *) fctx->dest);
+
+ /*
+ * Initialise the Tuple Desriptor. (This can't be done until
+ * we have done our first fetch.)
+ */
+ if (first_call)
+ {
+ MemoryContext per_query_ctx = rsinfo->econtext->ecxt_per_query_memory;
+ MemoryContext oldcontext = MemoryContextSwitchTo(per_query_ctx);
+
+ fctx->tupdesc = CreateTupleDescCopy (fctx->dest->tupdesc);
+
+ /* For RECORD results, make sure a typmod has been assigned */
+ if (fctx->tupdesc->tdtypeid == RECORDOID &&
+ fctx->tupdesc->tdtypmod < 0)
+ assign_record_type_typmod(fctx->tupdesc);
+
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ rsinfo->setDesc = fctx->tupdesc;
+
+ Assert (nfetched <= 1);
+
+ if (nfetched == 1)
+ {
+ /*
+ * Convert the TableTupleSlot to a HeapTuple (doesn't
+ * materialise and doesn't copy unless unavoidable).
+ */
+ HeapTuple tuple = ExecFetchSlotHeapTuple (fctx->dest->received_slot,
+ /* materialise */ false,
+ NULL);
+
+ /*
+ * Avoid making a copy if the HeapTuple is already
+ * fully in memory and marked with correct typeid/typmod.
+ */
+ Datum datum = PointerGetDatum (tuple->t_data);
+ if (HeapTupleHasExternal(tuple) ||
+ HeapTupleHeaderGetTypeId(tuple->t_data) != fctx->tupdesc->tdtypeid ||
+ HeapTupleHeaderGetTypMod(tuple->t_data) != fctx->tupdesc->tdtypmod)
+ {
+ /*
+ * Copy the tuple as a Datum, ensuring it is
+ * fully in memory in the process.
+ */
+ datum = heap_copy_tuple_as_datum (tuple, fctx->tupdesc);
+ }
+
+ /*
+ * Obtain HeapTupleHeader for the Datum, which is in
+ * memory, so should not require a copy.
+ */
+ HeapTupleHeader result = DatumGetHeapTupleHeader (datum);
+
+ SRF_RETURN_NEXT (funcctx, PointerGetDatum (result));
+ }
+ else /* no rows retrieved */
+ {
+ /* it will have been pfree()'ed by ssdr_destroy() */
+ fctx->dest = NULL;
+
+ /* fctx itself will be released when multi_call_memory_ctx goes. */
+
+ SRF_RETURN_DONE(funcctx);
+ }
+}
+
+
+/*
+ * Planner support function for ROWS_IN (REFCURSOR)
+ */
+Datum
+rows_in_refcursor_support(PG_FUNCTION_ARGS)
+{
+ Node *rawreq = (Node *) PG_GETARG_POINTER(0);
+ Node *ret = NULL;
+
+ if (!IsA(rawreq, SupportRequestRows)
+ && !IsA (rawreq, SupportRequestCost))
+ PG_RETURN_POINTER(NULL);
+
+ Node *req_node = NULL;
+ if (IsA(rawreq, SupportRequestRows))
+ {
+ SupportRequestRows *req = (SupportRequestRows *) rawreq;
+
+ req_node = req->node;
+ }
+ else if (IsA (rawreq, SupportRequestCost))
+ {
+ SupportRequestCost *req = (SupportRequestCost *) rawreq;
+
+ req_node = req->node;
+ }
+
+ /* The call to ROWS_IN should be in a FuncExpr node. */
+ if (!is_funcclause(req_node))
+ PG_RETURN_POINTER(NULL);
+
+ List *args = ((FuncExpr *) req_node)->args;
+ if (args == NULL)
+ PG_RETURN_POINTER(NULL);
+
+ Node *arg1 = linitial(args);
+
+ /*
+ * We can only estimate the cost if the REFCURSOR is
+ * already simplified to a Const.
+ */
+ if (!IsA (arg1, Const))
+ PG_RETURN_POINTER(NULL);
+
+ Const *cexpr = (Const *) arg1;
+
+ if (cexpr->constisnull)
+ PG_RETURN_POINTER(NULL);
+
+ if (cexpr->consttype != REFCURSOROID)
+ PG_RETURN_POINTER(NULL);
+
+ /*
+ * We can ignore a check on the collation because we are not
+ * interested in sorting, and typemod because REFCURSOR has
+ * no modifyable attributes.
+ */
+ Oid typoutput;
+ bool typIsVarlena;
+ getTypeOutputInfo(cexpr->consttype, &typoutput, &typIsVarlena);
+
+ char *portal_name = OidOutputFunctionCall(typoutput, cexpr->constvalue);
+
+ Portal portal = GetPortalByName(portal_name);
+
+ /* Check that the portal exists */
+ if (!PortalIsValid(portal))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_CURSOR),
+ errmsg("cursor \"%s\" does not exist", portal_name)));
+
+ /* ensure the Portal is ready (has already been OPEN'ed) */
+ if (! (portal->status == PORTAL_DEFINED ||
+ portal->status == PORTAL_READY))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_CURSOR_STATE),
+ errmsg("cursor \"%s\" is not OPEN", portal_name)));
+
+ /*
+ * Ensure the Portal returns some results (so is
+ * not a utility command, or set of multiple statements.
+ */
+ if (! (portal->strategy == PORTAL_ONE_SELECT ||
+ portal->strategy == PORTAL_ONE_RETURNING ||
+ portal->strategy == PORTAL_ONE_MOD_WITH))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_CURSOR_DEFINITION),
+ errmsg("cursor \"%s\" does not return result set", portal_name)));
+
+ QueryDesc *qdesc = portal->queryDesc;
+ if (qdesc == NULL)
+ PG_RETURN_POINTER(NULL);
+
+ PlanState *planstate = qdesc->planstate;
+ if (planstate == NULL)
+ PG_RETURN_POINTER(NULL);
+
+ if (IsA(rawreq, SupportRequestRows))
+ {
+ SupportRequestRows *req = (SupportRequestRows *) rawreq;
+
+ req->rows = planstate->plan->plan_rows;
+ }
+ else if (IsA (rawreq, SupportRequestCost))
+ {
+ SupportRequestCost *req = (SupportRequestCost *) rawreq;
+
+ req->startup = planstate->plan->startup_cost;
+ req->per_tuple = (planstate->plan->total_cost - planstate->plan->startup_cost);
+ if (planstate->plan->plan_rows != 0.0)
+ req->per_tuple /= planstate->plan->plan_rows;
+ }
+
+ ret = (Node *) rawreq;
+
+ PG_RETURN_POINTER(ret);
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index fcf2a12..20db161 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -1575,6 +1575,13 @@
{ oid => '3996', descr => 'planner support for array_unnest',
proname => 'array_unnest_support', prorettype => 'internal',
proargtypes => 'internal', prosrc => 'array_unnest_support' },
+{ oid => '8629', descr => 'expand refcursor to set of rows',
+ proname => 'rows_in', prorows => '100', prosupport => 'rows_in_refcursor_support',
+ proretset => 't', prorettype => 'record', proargtypes => 'refcursor',
+ prosrc => 'rows_in_refcursor' },
+{ oid => '8630', descr => 'planner support for rows_in',
+ proname => 'rows_in_refcursor_support', prorettype => 'internal',
+ proargtypes => 'internal', prosrc => 'rows_in_refcursor_support' },
{ oid => '3167',
descr => 'remove any occurrences of an element from an array',
proname => 'array_remove', proisstrict => 'f', prorettype => 'anyarray',
diff --git a/src/include/utils/refcursor.h b/src/include/utils/refcursor.h
new file mode 100644
index 0000000..e636193
--- /dev/null
+++ b/src/include/utils/refcursor.h
@@ -0,0 +1,15 @@
+/*-------------------------------------------------------------------------
+*
+* refcursor.c
+*
+* IDENTIFICATION
+* src/include/utils/refcursor.c
+*
+*-------------------------------------------------------------------------
+*/
+
+#ifndef refcursor_h
+#define refcursor_h
+
+
+#endif /* refcursor_h */
diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out
index cd2c79f..f8d40eb 100644
--- a/src/test/regress/expected/plpgsql.out
+++ b/src/test/regress/expected/plpgsql.out
@@ -2078,6 +2078,358 @@ select refcursor_test2(20000, 20000) as "Should be false",
f | t
(1 row)
+-- Check ability to consume from REFCURSOR in SELECT
+explain (verbose, costs off) select * from rows_in(return_unnamed_refcursor()) as (a int);
+ QUERY PLAN
+------------------------------------------------------
+ Function Scan on pg_catalog.rows_in
+ Output: a
+ Function Call: rows_in(return_unnamed_refcursor())
+(3 rows)
+
+select * from rows_in(return_unnamed_refcursor()) as (a int);
+ a
+-----
+ 5
+ 50
+ 500
+(3 rows)
+
+explain (verbose, costs off) select * from rows_in(return_unnamed_refcursor()) as (a int) where a >= 50;
+ QUERY PLAN
+------------------------------------------------------
+ Function Scan on pg_catalog.rows_in
+ Output: a
+ Function Call: rows_in(return_unnamed_refcursor())
+ Filter: (rows_in.a >= 50)
+(4 rows)
+
+select * from rows_in(return_unnamed_refcursor()) as (a int) where a >= 50;
+ a
+-----
+ 50
+ 500
+(2 rows)
+
+explain (verbose, costs off) select * from rows_in(return_unnamed_refcursor()) as (a int) order by a desc;
+ QUERY PLAN
+------------------------------------------------------------
+ Sort
+ Output: a
+ Sort Key: rows_in.a DESC
+ -> Function Scan on pg_catalog.rows_in
+ Output: a
+ Function Call: rows_in(return_unnamed_refcursor())
+(6 rows)
+
+select * from rows_in(return_unnamed_refcursor()) as (a int) order by a desc;
+ a
+-----
+ 500
+ 50
+ 5
+(3 rows)
+
+-- Check multiple scan of REFCURSOR
+explain (verbose, costs off) select * from (select 1 nr union all select 2) as scan, rows_in(return_unnamed_refcursor()) as (a int) order by nr;
+ QUERY PLAN
+------------------------------------------------------------------
+ Sort
+ Output: (1), rows_in.a
+ Sort Key: (1)
+ -> Nested Loop
+ Output: (1), rows_in.a
+ -> Function Scan on pg_catalog.rows_in
+ Output: rows_in.a
+ Function Call: rows_in(return_unnamed_refcursor())
+ -> Materialize
+ Output: (1)
+ -> Append
+ -> Result
+ Output: 1
+ -> Result
+ Output: 2
+(15 rows)
+
+select * from (select 1 nr union all select 2) as scan, rows_in(return_unnamed_refcursor()) as (a int) order by nr;
+ nr | a
+----+-----
+ 1 | 5
+ 1 | 50
+ 1 | 500
+ 2 | 5
+ 2 | 50
+ 2 | 500
+(6 rows)
+
+-- Check multiple reference to single REFCURSOR
+explain (verbose, costs off)
+ select * from
+ rows_in(return_unnamed_refcursor()) as a(n int),
+ rows_in(return_unnamed_refcursor()) as b(n int)
+ order by a.n, b.n;
+ QUERY PLAN
+------------------------------------------------------------------
+ Sort
+ Output: a.n, b.n
+ Sort Key: a.n, b.n
+ -> Nested Loop
+ Output: a.n, b.n
+ -> Function Scan on pg_catalog.rows_in a
+ Output: a.n
+ Function Call: rows_in(return_unnamed_refcursor())
+ -> Function Scan on pg_catalog.rows_in b
+ Output: b.n
+ Function Call: rows_in(return_unnamed_refcursor())
+(11 rows)
+
+select * from
+ rows_in(return_unnamed_refcursor()) as a(n int),
+ rows_in(return_unnamed_refcursor()) as b(n int)
+ order by a.n, b.n;
+ n | n
+-----+-----
+ 5 | 5
+ 5 | 50
+ 5 | 500
+ 50 | 5
+ 50 | 50
+ 50 | 500
+ 500 | 5
+ 500 | 50
+ 500 | 500
+(9 rows)
+
+explain (verbose, costs off)
+ select r.cur::text, a.n, b.n from
+ (select return_unnamed_refcursor() cur) r,
+ rows_in (r.cur) as a(n int),
+ rows_in (r.cur) as b(n int)
+ order by r.cur::text, a.n, b.n;
+ QUERY PLAN
+--------------------------------------------------------------------------
+ Sort
+ Output: (((return_unnamed_refcursor()))::text), a.n, b.n
+ Sort Key: (((return_unnamed_refcursor()))::text), a.n, b.n
+ -> Nested Loop
+ Output: ((return_unnamed_refcursor()))::text, a.n, b.n
+ -> Nested Loop
+ Output: (return_unnamed_refcursor()), a.n
+ -> Result
+ Output: return_unnamed_refcursor()
+ -> Function Scan on pg_catalog.rows_in a
+ Output: a.n
+ Function Call: rows_in((return_unnamed_refcursor()))
+ -> Function Scan on pg_catalog.rows_in b
+ Output: b.n
+ Function Call: rows_in((return_unnamed_refcursor()))
+(15 rows)
+
+select r.cur::text, a.n, b.n from
+ (select return_unnamed_refcursor() cur) r,
+ rows_in (r.cur) as a(n int),
+ rows_in (r.cur) as b(n int)
+ order by r.cur::text, a.n, b.n;
+ cur | n | n
+---------------------+-----+-----
+ <unnamed portal 11> | 5 | 5
+ <unnamed portal 11> | 5 | 50
+ <unnamed portal 11> | 5 | 500
+ <unnamed portal 11> | 50 | 5
+ <unnamed portal 11> | 50 | 50
+ <unnamed portal 11> | 50 | 500
+ <unnamed portal 11> | 500 | 5
+ <unnamed portal 11> | 500 | 50
+ <unnamed portal 11> | 500 | 500
+(9 rows)
+
+-- Check use of REFCURSOR in WITH
+with rcq as (
+ select * from rows_in(return_unnamed_refcursor()) as (a int)
+)
+select * from rcq where a <= 50;
+ a
+----
+ 5
+ 50
+(2 rows)
+
+-- Check attempt to UPDATE/DELETE REFCURSOR fails
+update rows_in(return_unnamed_refcursor()) set a = 2;
+ERROR: syntax error at or near "("
+LINE 1: update rows_in(return_unnamed_refcursor()) set a = 2;
+ ^
+delete from rows_in(return_unnamed_refcursor());
+ERROR: syntax error at or near "("
+LINE 1: delete from rows_in(return_unnamed_refcursor());
+ ^
+-- Check type consistency
+select * from rows_in(return_unnamed_refcursor()) as (a int, b int);
+ERROR: function return row and query-specified return row do not match
+DETAIL: Returned row contains 1 attribute, but query expects 2.
+select * from rows_in(return_unnamed_refcursor()) as (a text);
+ERROR: function return row and query-specified return row do not match
+DETAIL: Returned type integer at ordinal position 1, but query expects text.
+select * from rows_in(return_unnamed_refcursor()) as (a jsonb);
+ERROR: function return row and query-specified return row do not match
+DETAIL: Returned type integer at ordinal position 1, but query expects jsonb.
+-- Check consumption from REFCURSOR inside plpgsql FUNCTION
+create function refcursor_test3(input refcursor) returns refcursor as $$
+declare
+ rc refcursor;
+begin
+ open rc for select a, a+1 as a_plus_1 from rows_in(input) as (a int);
+ return rc;
+end
+$$ language plpgsql;
+explain (verbose, costs off) select * from rows_in(refcursor_test3(return_unnamed_refcursor())) as (a int, ap1 int) where a >= 50;
+ QUERY PLAN
+-----------------------------------------------------------------------
+ Function Scan on pg_catalog.rows_in
+ Output: a, ap1
+ Function Call: rows_in(refcursor_test3(return_unnamed_refcursor()))
+ Filter: (rows_in.a >= 50)
+(4 rows)
+
+select * from rows_in(refcursor_test3(return_unnamed_refcursor())) as (a int, ap1 int) where a >= 50;
+ a | ap1
+-----+-----
+ 50 | 51
+ 500 | 501
+(2 rows)
+
+drop function refcursor_test3;
+-- Check consumption from REFCURSOR defined with USING
+create function refcursor_test4(minimum int) returns refcursor as $$
+declare
+ rc refcursor;
+begin
+ OPEN rc FOR EXECUTE 'select a from rc_test where a >= $1'
+ USING (minimum);
+ return rc;
+end
+$$ language plpgsql;
+explain (verbose, costs off)
+ select * from rows_in(refcursor_test4(50)) as (a int);
+ QUERY PLAN
+-----------------------------------------------
+ Function Scan on pg_catalog.rows_in
+ Output: a
+ Function Call: rows_in(refcursor_test4(50))
+(3 rows)
+
+select * from rows_in(refcursor_test4(50)) as (a int);
+ a
+-----
+ 50
+ 500
+(2 rows)
+
+drop function refcursor_test4;
+-- Check consumption from REFCURSOR defined in immediate mode
+begin;
+declare c cursor for
+ select 1 as i, 'one' v
+ union all
+ select 2, 'two';
+select * from rows_in('c'::refcursor) as (i int, v text);
+ i | v
+---+-----
+ 1 | one
+ 2 | two
+(2 rows)
+
+rollback;
+-- Check consumption from REFCURSOR directly in target list (tSRF)
+-- (This isn't particularly useful, but it should work.)
+begin;
+declare c cursor for
+ select 1 as i, 'one' v
+ union all
+ select 2, 'two';
+select rows_in('c'::refcursor);
+ rows_in
+---------
+ (1,one)
+ (2,two)
+(2 rows)
+
+rollback;
+begin;
+declare c cursor for
+ select 1 as i, 'one' v
+ union all
+ select 2, 'two';
+select rows_in('c'::refcursor), * from rows_in('c'::refcursor) as (i int, v text);
+ rows_in | i | v
+---------+---+-----
+ (1,one) | 1 | one
+ (2,two) | 1 | one
+ (1,one) | 2 | two
+ (2,two) | 2 | two
+(4 rows)
+
+rollback;
+-- Check use of ROWS_IN with row_to_json both in the target list
+-- and in the FROM.
+begin;
+declare c cursor for
+ with recursive t as (
+ select 1 i
+ union all
+ select i + 1
+ from t
+ where i + 1 <= 10
+ )
+ select i, 'number ' || i t from t;
+select row_to_json(rows_in('c'::refcursor));
+ row_to_json
+--------------------------
+ {"i":1,"t":"number 1"}
+ {"i":2,"t":"number 2"}
+ {"i":3,"t":"number 3"}
+ {"i":4,"t":"number 4"}
+ {"i":5,"t":"number 5"}
+ {"i":6,"t":"number 6"}
+ {"i":7,"t":"number 7"}
+ {"i":8,"t":"number 8"}
+ {"i":9,"t":"number 9"}
+ {"i":10,"t":"number 10"}
+(10 rows)
+
+rollback;
+begin;
+declare c cursor for
+ with recursive t as (
+ select 1 i
+ union all
+ select i + 1
+ from t
+ where i + 1 <= 1000
+ )
+ select i, 'number ' || i t from t;
+select j.r,
+ (j.r->'i')::int i, (j.r->'t')::text t
+ from (select row_to_json(rows_in('c'::refcursor))::jsonb as r) j
+ where
+ (j.r->'i')::int >= 50
+ and (j.r->'i')::int <= 60;
+ r | i | t
+-----------------------------+----+-------------
+ {"i": 50, "t": "number 50"} | 50 | "number 50"
+ {"i": 51, "t": "number 51"} | 51 | "number 51"
+ {"i": 52, "t": "number 52"} | 52 | "number 52"
+ {"i": 53, "t": "number 53"} | 53 | "number 53"
+ {"i": 54, "t": "number 54"} | 54 | "number 54"
+ {"i": 55, "t": "number 55"} | 55 | "number 55"
+ {"i": 56, "t": "number 56"} | 56 | "number 56"
+ {"i": 57, "t": "number 57"} | 57 | "number 57"
+ {"i": 58, "t": "number 58"} | 58 | "number 58"
+ {"i": 59, "t": "number 59"} | 59 | "number 59"
+ {"i": 60, "t": "number 60"} | 60 | "number 60"
+(11 rows)
+
+rollback;
--
-- tests for cursors with named parameter arguments
--
diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql
index d841d8c..2fc76a9 100644
--- a/src/test/regress/sql/plpgsql.sql
+++ b/src/test/regress/sql/plpgsql.sql
@@ -1805,6 +1805,143 @@ $$ language plpgsql;
select refcursor_test2(20000, 20000) as "Should be false",
refcursor_test2(20, 20) as "Should be true";
+-- Check ability to consume from REFCURSOR in SELECT
+explain (verbose, costs off) select * from rows_in(return_unnamed_refcursor()) as (a int);
+select * from rows_in(return_unnamed_refcursor()) as (a int);
+explain (verbose, costs off) select * from rows_in(return_unnamed_refcursor()) as (a int) where a >= 50;
+select * from rows_in(return_unnamed_refcursor()) as (a int) where a >= 50;
+explain (verbose, costs off) select * from rows_in(return_unnamed_refcursor()) as (a int) order by a desc;
+select * from rows_in(return_unnamed_refcursor()) as (a int) order by a desc;
+
+-- Check multiple scan of REFCURSOR
+explain (verbose, costs off) select * from (select 1 nr union all select 2) as scan, rows_in(return_unnamed_refcursor()) as (a int) order by nr;
+select * from (select 1 nr union all select 2) as scan, rows_in(return_unnamed_refcursor()) as (a int) order by nr;
+
+-- Check multiple reference to single REFCURSOR
+explain (verbose, costs off)
+ select * from
+ rows_in(return_unnamed_refcursor()) as a(n int),
+ rows_in(return_unnamed_refcursor()) as b(n int)
+ order by a.n, b.n;
+select * from
+ rows_in(return_unnamed_refcursor()) as a(n int),
+ rows_in(return_unnamed_refcursor()) as b(n int)
+ order by a.n, b.n;
+explain (verbose, costs off)
+ select r.cur::text, a.n, b.n from
+ (select return_unnamed_refcursor() cur) r,
+ rows_in (r.cur) as a(n int),
+ rows_in (r.cur) as b(n int)
+ order by r.cur::text, a.n, b.n;
+select r.cur::text, a.n, b.n from
+ (select return_unnamed_refcursor() cur) r,
+ rows_in (r.cur) as a(n int),
+ rows_in (r.cur) as b(n int)
+ order by r.cur::text, a.n, b.n;
+
+-- Check use of REFCURSOR in WITH
+with rcq as (
+ select * from rows_in(return_unnamed_refcursor()) as (a int)
+)
+select * from rcq where a <= 50;
+
+-- Check attempt to UPDATE/DELETE REFCURSOR fails
+update rows_in(return_unnamed_refcursor()) set a = 2;
+delete from rows_in(return_unnamed_refcursor());
+
+-- Check type consistency
+select * from rows_in(return_unnamed_refcursor()) as (a int, b int);
+select * from rows_in(return_unnamed_refcursor()) as (a text);
+select * from rows_in(return_unnamed_refcursor()) as (a jsonb);
+
+-- Check consumption from REFCURSOR inside plpgsql FUNCTION
+create function refcursor_test3(input refcursor) returns refcursor as $$
+declare
+ rc refcursor;
+begin
+ open rc for select a, a+1 as a_plus_1 from rows_in(input) as (a int);
+ return rc;
+end
+$$ language plpgsql;
+explain (verbose, costs off) select * from rows_in(refcursor_test3(return_unnamed_refcursor())) as (a int, ap1 int) where a >= 50;
+select * from rows_in(refcursor_test3(return_unnamed_refcursor())) as (a int, ap1 int) where a >= 50;
+
+drop function refcursor_test3;
+
+-- Check consumption from REFCURSOR defined with USING
+create function refcursor_test4(minimum int) returns refcursor as $$
+declare
+ rc refcursor;
+begin
+ OPEN rc FOR EXECUTE 'select a from rc_test where a >= $1'
+ USING (minimum);
+ return rc;
+end
+$$ language plpgsql;
+explain (verbose, costs off)
+ select * from rows_in(refcursor_test4(50)) as (a int);
+select * from rows_in(refcursor_test4(50)) as (a int);
+
+drop function refcursor_test4;
+
+-- Check consumption from REFCURSOR defined in immediate mode
+begin;
+declare c cursor for
+ select 1 as i, 'one' v
+ union all
+ select 2, 'two';
+select * from rows_in('c'::refcursor) as (i int, v text);
+rollback;
+
+-- Check consumption from REFCURSOR directly in target list (tSRF)
+-- (This isn't particularly useful, but it should work.)
+begin;
+declare c cursor for
+ select 1 as i, 'one' v
+ union all
+ select 2, 'two';
+select rows_in('c'::refcursor);
+rollback;
+begin;
+declare c cursor for
+ select 1 as i, 'one' v
+ union all
+ select 2, 'two';
+select rows_in('c'::refcursor), * from rows_in('c'::refcursor) as (i int, v text);
+rollback;
+
+-- Check use of ROWS_IN with row_to_json both in the target list
+-- and in the FROM.
+begin;
+declare c cursor for
+ with recursive t as (
+ select 1 i
+ union all
+ select i + 1
+ from t
+ where i + 1 <= 10
+ )
+ select i, 'number ' || i t from t;
+select row_to_json(rows_in('c'::refcursor));
+rollback;
+begin;
+declare c cursor for
+ with recursive t as (
+ select 1 i
+ union all
+ select i + 1
+ from t
+ where i + 1 <= 1000
+ )
+ select i, 'number ' || i t from t;
+select j.r,
+ (j.r->'i')::int i, (j.r->'t')::text t
+ from (select row_to_json(rows_in('c'::refcursor))::jsonb as r) j
+ where
+ (j.r->'i')::int >= 50
+ and (j.r->'i')::int <= 60;
+rollback;
+
--
-- tests for cursors with named parameter arguments
--
Re: [WIP] UNNEST(REFCURSOR): allowing SELECT to consume data from a REFCURSOR
On Sat, Jan 25, 2020 at 11:59 PM Dent John <denty@qqdd.eu> wrote:
Attached patch addresses these points, so should now apply cleanly agains dev.
From the trivialities department, I see a bunch of warnings about
local declaration placement (we're still using C90 rules for those by
project policy):
refcursor.c:138:3: error: ISO C90 forbids mixed declarations and code
[-Werror=declaration-after-statement]
MemoryContext oldcontext =
MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
^
Re: [WIP] UNNEST(REFCURSOR): allowing SELECT to consume data from a REFCURSOR
On 18 Feb 2020, at 03:03, Thomas Munro <thomas.munro@gmail.com> wrote:
From the trivialities department, I see a bunch of warnings about
local declaration placement (we're still using C90 rules for those by
project policy):refcursor.c:138:3: error: ISO C90 forbids mixed declarations and code
[-Werror=declaration-after-statement]
MemoryContext oldcontext =
MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
^
Thanks for pointing that out.
I have updated the patch.
denty.
Attachments:
unnest-refcursor-v6a.patchapplication/octet-stream; name=unnest-refcursor-v6a.patch; x-unix-mode=0644Download
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 6c4359d..c9b0d28 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -16803,6 +16803,113 @@ SELECT * FROM pg_ls_dir('.') WITH ORDINALITY AS t(ls,n);
</sect1>
+ <sect1 id="functions-cursor">
+ <title>Cursor Manipulation Functions</title>
+
+ <para>
+ This section describes functions that cursors to be manipulated
+ in normal <command>SELECT</command> queries.
+ </para>
+
+ <para>
+ The creation of cursors using <xref linkend="sql-declare"/>
+ or within a procedural language such as
+ <application>PL/pgSQL</application>
+ (see <xref linkend="plpgsql-cursor-declarations"/>), and other
+ manipulaton outside of a <command>SELECT</command> query is
+ described elsewhere in this manual.
+ </para>
+
+ <sect2>
+ <title><literal><function>ROWS_IN(<parameter>cursor</parameter>)</function></literal></title>
+
+ <indexterm zone="functions-cursor">
+ <primary>cursor manipulation functions</primary>
+ <secondary>functions</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>rows_in</primary>
+ </indexterm>
+
+<synopsis>
+<function>ROWS_IN(<parameter>cursor</parameter> <type>REFCURSOR</type>)
+ RETURNS <type>SETOF RECORD</type></function>
+</synopsis>
+
+ <para>
+ <function>ROWS_IN</function> retrieves rows from a previously-created
+ cursor, or a <type>REFCURSOR</type> returned by a function. It may be
+ considered as equivalent to <command>FETCH ALL</command>, but able
+ to be integrated into a general query.
+ </para>
+
+ <para>
+ <function>ROWS_IN</function> would typically be placed in the
+ FROM clause. Since the columns specified in the
+ <type>REFCURSOR</type> may not be determinable at query plan
+ time, it is required to use a column definition list. For example:
+ </para>
+
+<programlisting>
+SELECT cur.i, cur.t
+ FROM
+ ROWS_IN(fn(...)) AS cur(i int, t text);
+</programlisting>
+
+ <para>
+ The <type>REFCURSOR</type> must be open, and the query must be a
+ <command>SELECT</command> statement. If the <type>REFCURSOR</type>’s
+ output does not
+ correspond to that declared in the query, an error is raised at
+ query execution time. <command>UPDATE</command> and other DML
+ statements are not permitted, even if they return a set of rows.
+ Even if the <type>REFCURSOR</type> returns rows from a physical table, they may
+ not be <command>SELECT</command>ed <literal>FOR UPDATE</literal>.
+ </para>
+
+ <para>
+ It is also possible to place <function>ROWS_IN</function> in the
+ <command>SELECT</command> list. Tools to manipulate the results from an untyped
+ <type>RECORD</type> are relatively few, making its use limited in that context.
+ One exception is <function>row_to_json</function> (see
+ <xref linkend="functions-json-creation-table"/>).
+ </para>
+
+ <note>
+ <para>
+ It is acceptable to use multiple <function>ROWS_IN</function> constructs
+ referencing multiple <type>REFCURSOR</type>s in the same
+ query. If several references
+ are made to the same <type>REFCURSOR</type>, place the construction
+ inside a <literal>WITH</literal> subquery
+ marked <literal>MATERIALIZE ALWAYS</literal>.
+ </para>
+ </note>
+
+ <note>
+ <para>
+ When placed in the <literal>FROM</literal> clause,
+ <function>ROWS_IN</function> results are staged
+ before being processed by the executor. Do not rely upon this
+ behaviour as it may be changed in future. When placed in the
+ <command>SELECT</command> list, rows are always released one at a time.
+ </para>
+ </note>
+
+ <note>
+ <para>
+ When the <type>REFCURSOR</type> is returned from a <literal>VOLATILE</literal>
+ function, the query associated with the <type>REFCURSOR</type> is
+ always executed as is. When the <type>REFCURSOR</type> is returned
+ from a <literal>STABLE</literal> or <literal>IMMUTABLE</literal> function, in
+ future, the planner may be able to inline the <type>REFCURSOR</type>
+ query into the outer query.
+ </para>
+ </note>
+ </sect2>
+ </sect1>
+
<sect1 id="functions-info">
<title>System Information Functions and Operators</title>
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index 2987a55..db1a6bd 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -3427,6 +3427,16 @@ COMMIT;
</programlisting>
</para>
</sect3>
+ <sect3>
+ <title>Querying cursors</title>
+
+ <para>
+ Section <xref linkend="functions-cursor"/> discusses the use of
+ <function>ROWS_IN</function> which allows a returned
+ <type>REFCURSOR</type> to be queried in a normal
+ <command>SELECT</command> command.
+ </para>
+ </sect3>
</sect2>
<sect2 id="plpgsql-cursor-for-loop">
diff --git a/src/backend/utils/adt/Makefile b/src/backend/utils/adt/Makefile
index 790d7a2..ae5d6d3 100644
--- a/src/backend/utils/adt/Makefile
+++ b/src/backend/utils/adt/Makefile
@@ -81,6 +81,7 @@ OBJS = \
rangetypes_selfuncs.o \
rangetypes_spgist.o \
rangetypes_typanalyze.o \
+ refcursor.o \
regexp.o \
regproc.o \
ri_triggers.o \
diff --git a/src/backend/utils/adt/refcursor.c b/src/backend/utils/adt/refcursor.c
new file mode 100644
index 0000000..068f235
--- /dev/null
+++ b/src/backend/utils/adt/refcursor.c
@@ -0,0 +1,393 @@
+/*-------------------------------------------------------------------------
+ *
+ * refcursor.c
+ *
+ * IDENTIFICATION
+ * src/backend/utils/adt/refcursor.c
+ *
+ *-------------------------------------------------------------------------
+ */
+
+#include "postgres.h"
+
+#include "access/htup_details.h"
+#include "catalog/pg_type.h"
+#include "funcapi.h"
+#include "nodes/nodeFuncs.h"
+#include "nodes/supportnodes.h"
+#include "optimizer/optimizer.h"
+#include "tcop/pquery.h"
+#include "utils/builtins.h"
+#include "utils/datum.h"
+#include "utils/lsyscache.h"
+#include "utils/refcursor.h"
+#include "utils/typcache.h"
+
+
+typedef struct SingleSlotDestReceiver
+{
+ DestReceiver pub;
+ TupleTableSlot *received_slot;
+ TupleDesc tupdesc;
+} SingleSlotDestReceiver;
+
+/*
+ * sqlfunction_startup --- executor startup
+ */
+static void
+ssdr_startup(DestReceiver *self, int operation, TupleDesc typeinfo)
+{
+ SingleSlotDestReceiver *myState = (SingleSlotDestReceiver *) self;
+
+ myState->tupdesc = typeinfo;
+}
+
+/*
+ * sqlfunction_receive --- receive one tuple
+ */
+static bool
+ssdr_receive(TupleTableSlot *slot, DestReceiver *self)
+{
+ SingleSlotDestReceiver *myState = (SingleSlotDestReceiver *) self;
+
+ myState->received_slot = slot;
+
+ return true;
+}
+
+/*
+ * sqlfunction_shutdown --- executor end
+ */
+static void
+ssdr_shutdown(DestReceiver *self)
+{
+ /* no-op */
+}
+
+/*
+ * sqlfunction_destroy --- release DestReceiver object
+ */
+static void
+ssdr_destroy(DestReceiver *self)
+{
+ pfree(self);
+}
+
+/*
+ * CreateSingleSlotDestReceiver -- create a DestReceiver
+ * that acquires a single tupleslot
+ */
+static DestReceiver *
+CreateSingleSlotDestReceiver(void)
+{
+ SingleSlotDestReceiver *self = (SingleSlotDestReceiver *) palloc(sizeof(SingleSlotDestReceiver));
+
+ self->pub.receiveSlot = ssdr_receive;
+ self->pub.rStartup = ssdr_startup;
+ self->pub.rShutdown = ssdr_shutdown;
+ self->pub.rDestroy = ssdr_destroy;
+ self->pub.mydest = -1;
+
+ /* private fields will be set by ssdr_startup */
+
+ return (DestReceiver *) self;
+}
+
+/*
+ * ROWS_IN (REFCURSOR)
+ */
+Datum
+rows_in_refcursor(PG_FUNCTION_ARGS)
+{
+ typedef struct
+ {
+ Portal portal;
+ SingleSlotDestReceiver *dest;
+ TupleDesc tupdesc;
+ } rows_in_refcursor_fctx;
+
+ ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+ FuncCallContext *funcctx;
+ rows_in_refcursor_fctx *fctx;
+ FetchDirection direction;
+ uint64 howMany;
+ uint64 nfetched;
+ bool first_call;
+ MemoryContext oldcontext;
+ HeapTuple tuple;
+ Datum datum;
+ HeapTupleHeader result;
+
+ /* stuff done only on the first call of the function */
+ first_call = SRF_IS_FIRSTCALL();
+ if (first_call)
+ {
+ /* create a function context for cross-call persistence */
+ funcctx = SRF_FIRSTCALL_INIT();
+
+ /* Check to see if caller supports us returning a set */
+ if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("set-valued function called in context that cannot accept a set")));
+ if (!(rsinfo->allowedModes & SFRM_ValuePerCall))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("value per call mode required, but it is not " \
+ "allowed in this context")));
+
+ /*
+ * switch to memory context appropriate for multiple function calls
+ */
+ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ /* allocate memory for user context */
+ fctx = (rows_in_refcursor_fctx *) palloc(sizeof(rows_in_refcursor_fctx));
+
+ fctx->dest = (SingleSlotDestReceiver *) CreateSingleSlotDestReceiver();
+
+ MemoryContextSwitchTo(oldcontext);
+
+ char *portal_name = text_to_cstring(PG_GETARG_TEXT_PP(0));
+
+ fctx->portal = GetPortalByName(portal_name);
+
+ /* Check that the portal exists */
+ if (!PortalIsValid(fctx->portal))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_CURSOR),
+ errmsg("cursor \"%s\" does not exist", portal_name)));
+
+ /* ensure the Portal is ready (has already been OPEN'ed) */
+ if (! (fctx->portal->status == PORTAL_DEFINED ||
+ fctx->portal->status == PORTAL_READY))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_CURSOR_STATE),
+ errmsg("cursor \"%s\" is not OPEN", portal_name)));
+
+ /*
+ * Ensure the Portal returns some results (so is not a utility
+ * command, or set of multiple statements.
+ */
+ if (! (fctx->portal->strategy == PORTAL_ONE_SELECT ||
+ fctx->portal->strategy == PORTAL_ONE_RETURNING ||
+ fctx->portal->strategy == PORTAL_ONE_MOD_WITH))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_CURSOR_DEFINITION),
+ errmsg("cursor \"%s\" does not return result set", portal_name)));
+
+ pfree(portal_name);
+
+ funcctx->user_fctx = fctx;
+ }
+
+ /* stuff done on every call of the function */
+ funcctx = SRF_PERCALL_SETUP();
+ fctx = funcctx->user_fctx;
+
+ rsinfo->returnMode = SFRM_ValuePerCall;
+
+ if (first_call)
+ {
+ direction = FETCH_ABSOLUTE; /* start from absolute position */
+ howMany = 1; /* position = 1 (top); also reads one row */
+ }
+ else
+ {
+ direction = FETCH_FORWARD; /* othrewise advance forward */
+ howMany = 1; /* count = 1 (read one row) */
+ }
+
+ /* Run the cursor... */
+ nfetched = PortalRunFetch (fctx->portal, direction, howMany,
+ (DestReceiver *) fctx->dest);
+
+ /*
+ * Initialise the Tuple Desriptor. (This can't be done until
+ * we have done our first fetch.)
+ */
+ if (first_call)
+ {
+ MemoryContext per_query_ctx = rsinfo->econtext->ecxt_per_query_memory;
+ MemoryContext oldcontext = MemoryContextSwitchTo(per_query_ctx);
+
+ fctx->tupdesc = CreateTupleDescCopy (fctx->dest->tupdesc);
+
+ /* For RECORD results, make sure a typmod has been assigned */
+ if (fctx->tupdesc->tdtypeid == RECORDOID &&
+ fctx->tupdesc->tdtypmod < 0)
+ assign_record_type_typmod(fctx->tupdesc);
+
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ rsinfo->setDesc = fctx->tupdesc;
+
+ Assert (nfetched <= 1);
+
+ if (nfetched == 1)
+ {
+ /*
+ * Convert the TableTupleSlot to a HeapTuple (doesn't
+ * materialise and doesn't copy unless unavoidable).
+ */
+ tuple = ExecFetchSlotHeapTuple (fctx->dest->received_slot,
+ /* materialise */ false,
+ NULL);
+
+ /*
+ * Avoid making a copy if the HeapTuple is already
+ * fully in memory and marked with correct typeid/typmod.
+ */
+ datum = PointerGetDatum (tuple->t_data);
+ if (HeapTupleHasExternal(tuple) ||
+ HeapTupleHeaderGetTypeId(tuple->t_data) != fctx->tupdesc->tdtypeid ||
+ HeapTupleHeaderGetTypMod(tuple->t_data) != fctx->tupdesc->tdtypmod)
+ {
+ /*
+ * Copy the tuple as a Datum, ensuring it is
+ * fully in memory in the process.
+ */
+ datum = heap_copy_tuple_as_datum (tuple, fctx->tupdesc);
+ }
+
+ /*
+ * Obtain HeapTupleHeader for the Datum, which is in
+ * memory, so should not require a copy.
+ */
+ result = DatumGetHeapTupleHeader (datum);
+
+ SRF_RETURN_NEXT (funcctx, PointerGetDatum (result));
+ }
+ else /* no rows retrieved */
+ {
+ /* it will have been pfree()'ed by ssdr_destroy() */
+ fctx->dest = NULL;
+
+ /* fctx itself will be released when multi_call_memory_ctx goes. */
+
+ SRF_RETURN_DONE(funcctx);
+ }
+}
+
+
+/*
+ * Planner support function for ROWS_IN (REFCURSOR)
+ */
+Datum
+rows_in_refcursor_support(PG_FUNCTION_ARGS)
+{
+ Node *rawreq = (Node *) PG_GETARG_POINTER(0);
+ Node *ret;
+ Node *req_node;
+ SupportRequestRows *rows_req = NULL; /* keep compiler happy */
+ SupportRequestCost *cost_req = NULL; /* keep compiler happy */
+ List *args;
+ Node *arg1;
+ Const *cexpr;
+ char *portal_name;
+ Portal portal;
+ QueryDesc *qdesc;
+ PlanState *planstate;
+ Oid typoutput;
+ bool typIsVarlena;
+
+ if (IsA(rawreq, SupportRequestRows))
+ {
+ rows_req = (SupportRequestRows *) rawreq;
+
+ req_node = rows_req->node;
+ }
+ else if (IsA (rawreq, SupportRequestCost))
+ {
+ cost_req = (SupportRequestCost *) rawreq;
+
+ req_node = cost_req->node;
+ }
+ else
+ PG_RETURN_POINTER(NULL);
+
+ /* The call to ROWS_IN should be in a FuncExpr node. */
+ if (!is_funcclause(req_node))
+ PG_RETURN_POINTER(NULL);
+
+ args = ((FuncExpr *) req_node)->args;
+ if (args == NULL)
+ PG_RETURN_POINTER(NULL);
+
+ arg1 = linitial(args);
+
+ /*
+ * We can only estimate the cost if the REFCURSOR is
+ * already simplified to a Const.
+ */
+ if (!IsA (arg1, Const))
+ PG_RETURN_POINTER(NULL);
+
+ cexpr = (Const *) arg1;
+
+ if (cexpr->constisnull)
+ PG_RETURN_POINTER(NULL);
+
+ if (cexpr->consttype != REFCURSOROID)
+ PG_RETURN_POINTER(NULL);
+
+ /*
+ * We can ignore a check on the collation because we are not
+ * interested in sorting, and typemod because REFCURSOR has
+ * no modifyable attributes.
+ */
+ getTypeOutputInfo(cexpr->consttype, &typoutput, &typIsVarlena);
+
+ portal_name = OidOutputFunctionCall(typoutput, cexpr->constvalue);
+
+ portal = GetPortalByName(portal_name);
+
+ /* Check that the portal exists */
+ if (!PortalIsValid(portal))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_CURSOR),
+ errmsg("cursor \"%s\" does not exist", portal_name)));
+
+ /* ensure the Portal is ready (has already been OPEN'ed) */
+ if (! (portal->status == PORTAL_DEFINED ||
+ portal->status == PORTAL_READY))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_CURSOR_STATE),
+ errmsg("cursor \"%s\" is not OPEN", portal_name)));
+
+ /*
+ * Ensure the Portal returns some results (so is
+ * not a utility command, or set of multiple statements.
+ */
+ if (! (portal->strategy == PORTAL_ONE_SELECT ||
+ portal->strategy == PORTAL_ONE_RETURNING ||
+ portal->strategy == PORTAL_ONE_MOD_WITH))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_CURSOR_DEFINITION),
+ errmsg("cursor \"%s\" does not return result set", portal_name)));
+
+ qdesc = portal->queryDesc;
+ if (qdesc == NULL)
+ PG_RETURN_POINTER(NULL);
+
+ planstate = qdesc->planstate;
+ if (planstate == NULL)
+ PG_RETURN_POINTER(NULL);
+
+ if (rows_req)
+ {
+ rows_req->rows = planstate->plan->plan_rows;
+ }
+ else if (cost_req)
+ {
+ cost_req->startup = planstate->plan->startup_cost;
+ cost_req->per_tuple = (planstate->plan->total_cost - planstate->plan->startup_cost);
+ if (planstate->plan->plan_rows != 0.0)
+ cost_req->per_tuple /= planstate->plan->plan_rows;
+ }
+
+ ret = (Node *) rawreq;
+
+ PG_RETURN_POINTER(ret);
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index fcf2a12..64fabf2 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -1575,6 +1575,13 @@
{ oid => '3996', descr => 'planner support for array_unnest',
proname => 'array_unnest_support', prorettype => 'internal',
proargtypes => 'internal', prosrc => 'array_unnest_support' },
+{ oid => '12921', descr => 'expand refcursor to set of rows',
+ proname => 'rows_in', prorows => '100', prosupport => 'rows_in_refcursor_support',
+ proretset => 't', prorettype => 'record', proargtypes => 'refcursor',
+ prosrc => 'rows_in_refcursor' },
+{ oid => '12923', descr => 'planner support for rows_in',
+ proname => 'rows_in_refcursor_support', prorettype => 'internal',
+ proargtypes => 'internal', prosrc => 'rows_in_refcursor_support' },
{ oid => '3167',
descr => 'remove any occurrences of an element from an array',
proname => 'array_remove', proisstrict => 'f', prorettype => 'anyarray',
diff --git a/src/include/utils/refcursor.h b/src/include/utils/refcursor.h
new file mode 100644
index 0000000..e636193
--- /dev/null
+++ b/src/include/utils/refcursor.h
@@ -0,0 +1,15 @@
+/*-------------------------------------------------------------------------
+*
+* refcursor.c
+*
+* IDENTIFICATION
+* src/include/utils/refcursor.c
+*
+*-------------------------------------------------------------------------
+*/
+
+#ifndef refcursor_h
+#define refcursor_h
+
+
+#endif /* refcursor_h */
diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out
index cd2c79f..f8d40eb 100644
--- a/src/test/regress/expected/plpgsql.out
+++ b/src/test/regress/expected/plpgsql.out
@@ -2078,6 +2078,358 @@ select refcursor_test2(20000, 20000) as "Should be false",
f | t
(1 row)
+-- Check ability to consume from REFCURSOR in SELECT
+explain (verbose, costs off) select * from rows_in(return_unnamed_refcursor()) as (a int);
+ QUERY PLAN
+------------------------------------------------------
+ Function Scan on pg_catalog.rows_in
+ Output: a
+ Function Call: rows_in(return_unnamed_refcursor())
+(3 rows)
+
+select * from rows_in(return_unnamed_refcursor()) as (a int);
+ a
+-----
+ 5
+ 50
+ 500
+(3 rows)
+
+explain (verbose, costs off) select * from rows_in(return_unnamed_refcursor()) as (a int) where a >= 50;
+ QUERY PLAN
+------------------------------------------------------
+ Function Scan on pg_catalog.rows_in
+ Output: a
+ Function Call: rows_in(return_unnamed_refcursor())
+ Filter: (rows_in.a >= 50)
+(4 rows)
+
+select * from rows_in(return_unnamed_refcursor()) as (a int) where a >= 50;
+ a
+-----
+ 50
+ 500
+(2 rows)
+
+explain (verbose, costs off) select * from rows_in(return_unnamed_refcursor()) as (a int) order by a desc;
+ QUERY PLAN
+------------------------------------------------------------
+ Sort
+ Output: a
+ Sort Key: rows_in.a DESC
+ -> Function Scan on pg_catalog.rows_in
+ Output: a
+ Function Call: rows_in(return_unnamed_refcursor())
+(6 rows)
+
+select * from rows_in(return_unnamed_refcursor()) as (a int) order by a desc;
+ a
+-----
+ 500
+ 50
+ 5
+(3 rows)
+
+-- Check multiple scan of REFCURSOR
+explain (verbose, costs off) select * from (select 1 nr union all select 2) as scan, rows_in(return_unnamed_refcursor()) as (a int) order by nr;
+ QUERY PLAN
+------------------------------------------------------------------
+ Sort
+ Output: (1), rows_in.a
+ Sort Key: (1)
+ -> Nested Loop
+ Output: (1), rows_in.a
+ -> Function Scan on pg_catalog.rows_in
+ Output: rows_in.a
+ Function Call: rows_in(return_unnamed_refcursor())
+ -> Materialize
+ Output: (1)
+ -> Append
+ -> Result
+ Output: 1
+ -> Result
+ Output: 2
+(15 rows)
+
+select * from (select 1 nr union all select 2) as scan, rows_in(return_unnamed_refcursor()) as (a int) order by nr;
+ nr | a
+----+-----
+ 1 | 5
+ 1 | 50
+ 1 | 500
+ 2 | 5
+ 2 | 50
+ 2 | 500
+(6 rows)
+
+-- Check multiple reference to single REFCURSOR
+explain (verbose, costs off)
+ select * from
+ rows_in(return_unnamed_refcursor()) as a(n int),
+ rows_in(return_unnamed_refcursor()) as b(n int)
+ order by a.n, b.n;
+ QUERY PLAN
+------------------------------------------------------------------
+ Sort
+ Output: a.n, b.n
+ Sort Key: a.n, b.n
+ -> Nested Loop
+ Output: a.n, b.n
+ -> Function Scan on pg_catalog.rows_in a
+ Output: a.n
+ Function Call: rows_in(return_unnamed_refcursor())
+ -> Function Scan on pg_catalog.rows_in b
+ Output: b.n
+ Function Call: rows_in(return_unnamed_refcursor())
+(11 rows)
+
+select * from
+ rows_in(return_unnamed_refcursor()) as a(n int),
+ rows_in(return_unnamed_refcursor()) as b(n int)
+ order by a.n, b.n;
+ n | n
+-----+-----
+ 5 | 5
+ 5 | 50
+ 5 | 500
+ 50 | 5
+ 50 | 50
+ 50 | 500
+ 500 | 5
+ 500 | 50
+ 500 | 500
+(9 rows)
+
+explain (verbose, costs off)
+ select r.cur::text, a.n, b.n from
+ (select return_unnamed_refcursor() cur) r,
+ rows_in (r.cur) as a(n int),
+ rows_in (r.cur) as b(n int)
+ order by r.cur::text, a.n, b.n;
+ QUERY PLAN
+--------------------------------------------------------------------------
+ Sort
+ Output: (((return_unnamed_refcursor()))::text), a.n, b.n
+ Sort Key: (((return_unnamed_refcursor()))::text), a.n, b.n
+ -> Nested Loop
+ Output: ((return_unnamed_refcursor()))::text, a.n, b.n
+ -> Nested Loop
+ Output: (return_unnamed_refcursor()), a.n
+ -> Result
+ Output: return_unnamed_refcursor()
+ -> Function Scan on pg_catalog.rows_in a
+ Output: a.n
+ Function Call: rows_in((return_unnamed_refcursor()))
+ -> Function Scan on pg_catalog.rows_in b
+ Output: b.n
+ Function Call: rows_in((return_unnamed_refcursor()))
+(15 rows)
+
+select r.cur::text, a.n, b.n from
+ (select return_unnamed_refcursor() cur) r,
+ rows_in (r.cur) as a(n int),
+ rows_in (r.cur) as b(n int)
+ order by r.cur::text, a.n, b.n;
+ cur | n | n
+---------------------+-----+-----
+ <unnamed portal 11> | 5 | 5
+ <unnamed portal 11> | 5 | 50
+ <unnamed portal 11> | 5 | 500
+ <unnamed portal 11> | 50 | 5
+ <unnamed portal 11> | 50 | 50
+ <unnamed portal 11> | 50 | 500
+ <unnamed portal 11> | 500 | 5
+ <unnamed portal 11> | 500 | 50
+ <unnamed portal 11> | 500 | 500
+(9 rows)
+
+-- Check use of REFCURSOR in WITH
+with rcq as (
+ select * from rows_in(return_unnamed_refcursor()) as (a int)
+)
+select * from rcq where a <= 50;
+ a
+----
+ 5
+ 50
+(2 rows)
+
+-- Check attempt to UPDATE/DELETE REFCURSOR fails
+update rows_in(return_unnamed_refcursor()) set a = 2;
+ERROR: syntax error at or near "("
+LINE 1: update rows_in(return_unnamed_refcursor()) set a = 2;
+ ^
+delete from rows_in(return_unnamed_refcursor());
+ERROR: syntax error at or near "("
+LINE 1: delete from rows_in(return_unnamed_refcursor());
+ ^
+-- Check type consistency
+select * from rows_in(return_unnamed_refcursor()) as (a int, b int);
+ERROR: function return row and query-specified return row do not match
+DETAIL: Returned row contains 1 attribute, but query expects 2.
+select * from rows_in(return_unnamed_refcursor()) as (a text);
+ERROR: function return row and query-specified return row do not match
+DETAIL: Returned type integer at ordinal position 1, but query expects text.
+select * from rows_in(return_unnamed_refcursor()) as (a jsonb);
+ERROR: function return row and query-specified return row do not match
+DETAIL: Returned type integer at ordinal position 1, but query expects jsonb.
+-- Check consumption from REFCURSOR inside plpgsql FUNCTION
+create function refcursor_test3(input refcursor) returns refcursor as $$
+declare
+ rc refcursor;
+begin
+ open rc for select a, a+1 as a_plus_1 from rows_in(input) as (a int);
+ return rc;
+end
+$$ language plpgsql;
+explain (verbose, costs off) select * from rows_in(refcursor_test3(return_unnamed_refcursor())) as (a int, ap1 int) where a >= 50;
+ QUERY PLAN
+-----------------------------------------------------------------------
+ Function Scan on pg_catalog.rows_in
+ Output: a, ap1
+ Function Call: rows_in(refcursor_test3(return_unnamed_refcursor()))
+ Filter: (rows_in.a >= 50)
+(4 rows)
+
+select * from rows_in(refcursor_test3(return_unnamed_refcursor())) as (a int, ap1 int) where a >= 50;
+ a | ap1
+-----+-----
+ 50 | 51
+ 500 | 501
+(2 rows)
+
+drop function refcursor_test3;
+-- Check consumption from REFCURSOR defined with USING
+create function refcursor_test4(minimum int) returns refcursor as $$
+declare
+ rc refcursor;
+begin
+ OPEN rc FOR EXECUTE 'select a from rc_test where a >= $1'
+ USING (minimum);
+ return rc;
+end
+$$ language plpgsql;
+explain (verbose, costs off)
+ select * from rows_in(refcursor_test4(50)) as (a int);
+ QUERY PLAN
+-----------------------------------------------
+ Function Scan on pg_catalog.rows_in
+ Output: a
+ Function Call: rows_in(refcursor_test4(50))
+(3 rows)
+
+select * from rows_in(refcursor_test4(50)) as (a int);
+ a
+-----
+ 50
+ 500
+(2 rows)
+
+drop function refcursor_test4;
+-- Check consumption from REFCURSOR defined in immediate mode
+begin;
+declare c cursor for
+ select 1 as i, 'one' v
+ union all
+ select 2, 'two';
+select * from rows_in('c'::refcursor) as (i int, v text);
+ i | v
+---+-----
+ 1 | one
+ 2 | two
+(2 rows)
+
+rollback;
+-- Check consumption from REFCURSOR directly in target list (tSRF)
+-- (This isn't particularly useful, but it should work.)
+begin;
+declare c cursor for
+ select 1 as i, 'one' v
+ union all
+ select 2, 'two';
+select rows_in('c'::refcursor);
+ rows_in
+---------
+ (1,one)
+ (2,two)
+(2 rows)
+
+rollback;
+begin;
+declare c cursor for
+ select 1 as i, 'one' v
+ union all
+ select 2, 'two';
+select rows_in('c'::refcursor), * from rows_in('c'::refcursor) as (i int, v text);
+ rows_in | i | v
+---------+---+-----
+ (1,one) | 1 | one
+ (2,two) | 1 | one
+ (1,one) | 2 | two
+ (2,two) | 2 | two
+(4 rows)
+
+rollback;
+-- Check use of ROWS_IN with row_to_json both in the target list
+-- and in the FROM.
+begin;
+declare c cursor for
+ with recursive t as (
+ select 1 i
+ union all
+ select i + 1
+ from t
+ where i + 1 <= 10
+ )
+ select i, 'number ' || i t from t;
+select row_to_json(rows_in('c'::refcursor));
+ row_to_json
+--------------------------
+ {"i":1,"t":"number 1"}
+ {"i":2,"t":"number 2"}
+ {"i":3,"t":"number 3"}
+ {"i":4,"t":"number 4"}
+ {"i":5,"t":"number 5"}
+ {"i":6,"t":"number 6"}
+ {"i":7,"t":"number 7"}
+ {"i":8,"t":"number 8"}
+ {"i":9,"t":"number 9"}
+ {"i":10,"t":"number 10"}
+(10 rows)
+
+rollback;
+begin;
+declare c cursor for
+ with recursive t as (
+ select 1 i
+ union all
+ select i + 1
+ from t
+ where i + 1 <= 1000
+ )
+ select i, 'number ' || i t from t;
+select j.r,
+ (j.r->'i')::int i, (j.r->'t')::text t
+ from (select row_to_json(rows_in('c'::refcursor))::jsonb as r) j
+ where
+ (j.r->'i')::int >= 50
+ and (j.r->'i')::int <= 60;
+ r | i | t
+-----------------------------+----+-------------
+ {"i": 50, "t": "number 50"} | 50 | "number 50"
+ {"i": 51, "t": "number 51"} | 51 | "number 51"
+ {"i": 52, "t": "number 52"} | 52 | "number 52"
+ {"i": 53, "t": "number 53"} | 53 | "number 53"
+ {"i": 54, "t": "number 54"} | 54 | "number 54"
+ {"i": 55, "t": "number 55"} | 55 | "number 55"
+ {"i": 56, "t": "number 56"} | 56 | "number 56"
+ {"i": 57, "t": "number 57"} | 57 | "number 57"
+ {"i": 58, "t": "number 58"} | 58 | "number 58"
+ {"i": 59, "t": "number 59"} | 59 | "number 59"
+ {"i": 60, "t": "number 60"} | 60 | "number 60"
+(11 rows)
+
+rollback;
--
-- tests for cursors with named parameter arguments
--
diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql
index d841d8c..2fc76a9 100644
--- a/src/test/regress/sql/plpgsql.sql
+++ b/src/test/regress/sql/plpgsql.sql
@@ -1805,6 +1805,143 @@ $$ language plpgsql;
select refcursor_test2(20000, 20000) as "Should be false",
refcursor_test2(20, 20) as "Should be true";
+-- Check ability to consume from REFCURSOR in SELECT
+explain (verbose, costs off) select * from rows_in(return_unnamed_refcursor()) as (a int);
+select * from rows_in(return_unnamed_refcursor()) as (a int);
+explain (verbose, costs off) select * from rows_in(return_unnamed_refcursor()) as (a int) where a >= 50;
+select * from rows_in(return_unnamed_refcursor()) as (a int) where a >= 50;
+explain (verbose, costs off) select * from rows_in(return_unnamed_refcursor()) as (a int) order by a desc;
+select * from rows_in(return_unnamed_refcursor()) as (a int) order by a desc;
+
+-- Check multiple scan of REFCURSOR
+explain (verbose, costs off) select * from (select 1 nr union all select 2) as scan, rows_in(return_unnamed_refcursor()) as (a int) order by nr;
+select * from (select 1 nr union all select 2) as scan, rows_in(return_unnamed_refcursor()) as (a int) order by nr;
+
+-- Check multiple reference to single REFCURSOR
+explain (verbose, costs off)
+ select * from
+ rows_in(return_unnamed_refcursor()) as a(n int),
+ rows_in(return_unnamed_refcursor()) as b(n int)
+ order by a.n, b.n;
+select * from
+ rows_in(return_unnamed_refcursor()) as a(n int),
+ rows_in(return_unnamed_refcursor()) as b(n int)
+ order by a.n, b.n;
+explain (verbose, costs off)
+ select r.cur::text, a.n, b.n from
+ (select return_unnamed_refcursor() cur) r,
+ rows_in (r.cur) as a(n int),
+ rows_in (r.cur) as b(n int)
+ order by r.cur::text, a.n, b.n;
+select r.cur::text, a.n, b.n from
+ (select return_unnamed_refcursor() cur) r,
+ rows_in (r.cur) as a(n int),
+ rows_in (r.cur) as b(n int)
+ order by r.cur::text, a.n, b.n;
+
+-- Check use of REFCURSOR in WITH
+with rcq as (
+ select * from rows_in(return_unnamed_refcursor()) as (a int)
+)
+select * from rcq where a <= 50;
+
+-- Check attempt to UPDATE/DELETE REFCURSOR fails
+update rows_in(return_unnamed_refcursor()) set a = 2;
+delete from rows_in(return_unnamed_refcursor());
+
+-- Check type consistency
+select * from rows_in(return_unnamed_refcursor()) as (a int, b int);
+select * from rows_in(return_unnamed_refcursor()) as (a text);
+select * from rows_in(return_unnamed_refcursor()) as (a jsonb);
+
+-- Check consumption from REFCURSOR inside plpgsql FUNCTION
+create function refcursor_test3(input refcursor) returns refcursor as $$
+declare
+ rc refcursor;
+begin
+ open rc for select a, a+1 as a_plus_1 from rows_in(input) as (a int);
+ return rc;
+end
+$$ language plpgsql;
+explain (verbose, costs off) select * from rows_in(refcursor_test3(return_unnamed_refcursor())) as (a int, ap1 int) where a >= 50;
+select * from rows_in(refcursor_test3(return_unnamed_refcursor())) as (a int, ap1 int) where a >= 50;
+
+drop function refcursor_test3;
+
+-- Check consumption from REFCURSOR defined with USING
+create function refcursor_test4(minimum int) returns refcursor as $$
+declare
+ rc refcursor;
+begin
+ OPEN rc FOR EXECUTE 'select a from rc_test where a >= $1'
+ USING (minimum);
+ return rc;
+end
+$$ language plpgsql;
+explain (verbose, costs off)
+ select * from rows_in(refcursor_test4(50)) as (a int);
+select * from rows_in(refcursor_test4(50)) as (a int);
+
+drop function refcursor_test4;
+
+-- Check consumption from REFCURSOR defined in immediate mode
+begin;
+declare c cursor for
+ select 1 as i, 'one' v
+ union all
+ select 2, 'two';
+select * from rows_in('c'::refcursor) as (i int, v text);
+rollback;
+
+-- Check consumption from REFCURSOR directly in target list (tSRF)
+-- (This isn't particularly useful, but it should work.)
+begin;
+declare c cursor for
+ select 1 as i, 'one' v
+ union all
+ select 2, 'two';
+select rows_in('c'::refcursor);
+rollback;
+begin;
+declare c cursor for
+ select 1 as i, 'one' v
+ union all
+ select 2, 'two';
+select rows_in('c'::refcursor), * from rows_in('c'::refcursor) as (i int, v text);
+rollback;
+
+-- Check use of ROWS_IN with row_to_json both in the target list
+-- and in the FROM.
+begin;
+declare c cursor for
+ with recursive t as (
+ select 1 i
+ union all
+ select i + 1
+ from t
+ where i + 1 <= 10
+ )
+ select i, 'number ' || i t from t;
+select row_to_json(rows_in('c'::refcursor));
+rollback;
+begin;
+declare c cursor for
+ with recursive t as (
+ select 1 i
+ union all
+ select i + 1
+ from t
+ where i + 1 <= 1000
+ )
+ select i, 'number ' || i t from t;
+select j.r,
+ (j.r->'i')::int i, (j.r->'t')::text t
+ from (select row_to_json(rows_in('c'::refcursor))::jsonb as r) j
+ where
+ (j.r->'i')::int >= 50
+ and (j.r->'i')::int <= 60;
+rollback;
+
--
-- tests for cursors with named parameter arguments
--
Re: [WIP] UNNEST(REFCURSOR): allowing SELECT to consume data from a REFCURSOR
On 22 Feb 2020, at 10:38, Dent John <denty@QQdd.eu> wrote:
On 18 Feb 2020, at 03:03, Thomas Munro <thomas.munro@gmail.com> wrote:
From the trivialities department, I see a bunch of warnings about
local declaration placement (we're still using C90 rules for those by
project policy):[…]
[…]
My bad. I missed on declaration.
Another patch attached.
d.
Attachments:
unnest-refcursor-v7a.patchapplication/octet-stream; name=unnest-refcursor-v7a.patch; x-unix-mode=0644Download
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 6c4359d..c9b0d28 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -16803,6 +16803,113 @@ SELECT * FROM pg_ls_dir('.') WITH ORDINALITY AS t(ls,n);
</sect1>
+ <sect1 id="functions-cursor">
+ <title>Cursor Manipulation Functions</title>
+
+ <para>
+ This section describes functions that cursors to be manipulated
+ in normal <command>SELECT</command> queries.
+ </para>
+
+ <para>
+ The creation of cursors using <xref linkend="sql-declare"/>
+ or within a procedural language such as
+ <application>PL/pgSQL</application>
+ (see <xref linkend="plpgsql-cursor-declarations"/>), and other
+ manipulaton outside of a <command>SELECT</command> query is
+ described elsewhere in this manual.
+ </para>
+
+ <sect2>
+ <title><literal><function>ROWS_IN(<parameter>cursor</parameter>)</function></literal></title>
+
+ <indexterm zone="functions-cursor">
+ <primary>cursor manipulation functions</primary>
+ <secondary>functions</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>rows_in</primary>
+ </indexterm>
+
+<synopsis>
+<function>ROWS_IN(<parameter>cursor</parameter> <type>REFCURSOR</type>)
+ RETURNS <type>SETOF RECORD</type></function>
+</synopsis>
+
+ <para>
+ <function>ROWS_IN</function> retrieves rows from a previously-created
+ cursor, or a <type>REFCURSOR</type> returned by a function. It may be
+ considered as equivalent to <command>FETCH ALL</command>, but able
+ to be integrated into a general query.
+ </para>
+
+ <para>
+ <function>ROWS_IN</function> would typically be placed in the
+ FROM clause. Since the columns specified in the
+ <type>REFCURSOR</type> may not be determinable at query plan
+ time, it is required to use a column definition list. For example:
+ </para>
+
+<programlisting>
+SELECT cur.i, cur.t
+ FROM
+ ROWS_IN(fn(...)) AS cur(i int, t text);
+</programlisting>
+
+ <para>
+ The <type>REFCURSOR</type> must be open, and the query must be a
+ <command>SELECT</command> statement. If the <type>REFCURSOR</type>’s
+ output does not
+ correspond to that declared in the query, an error is raised at
+ query execution time. <command>UPDATE</command> and other DML
+ statements are not permitted, even if they return a set of rows.
+ Even if the <type>REFCURSOR</type> returns rows from a physical table, they may
+ not be <command>SELECT</command>ed <literal>FOR UPDATE</literal>.
+ </para>
+
+ <para>
+ It is also possible to place <function>ROWS_IN</function> in the
+ <command>SELECT</command> list. Tools to manipulate the results from an untyped
+ <type>RECORD</type> are relatively few, making its use limited in that context.
+ One exception is <function>row_to_json</function> (see
+ <xref linkend="functions-json-creation-table"/>).
+ </para>
+
+ <note>
+ <para>
+ It is acceptable to use multiple <function>ROWS_IN</function> constructs
+ referencing multiple <type>REFCURSOR</type>s in the same
+ query. If several references
+ are made to the same <type>REFCURSOR</type>, place the construction
+ inside a <literal>WITH</literal> subquery
+ marked <literal>MATERIALIZE ALWAYS</literal>.
+ </para>
+ </note>
+
+ <note>
+ <para>
+ When placed in the <literal>FROM</literal> clause,
+ <function>ROWS_IN</function> results are staged
+ before being processed by the executor. Do not rely upon this
+ behaviour as it may be changed in future. When placed in the
+ <command>SELECT</command> list, rows are always released one at a time.
+ </para>
+ </note>
+
+ <note>
+ <para>
+ When the <type>REFCURSOR</type> is returned from a <literal>VOLATILE</literal>
+ function, the query associated with the <type>REFCURSOR</type> is
+ always executed as is. When the <type>REFCURSOR</type> is returned
+ from a <literal>STABLE</literal> or <literal>IMMUTABLE</literal> function, in
+ future, the planner may be able to inline the <type>REFCURSOR</type>
+ query into the outer query.
+ </para>
+ </note>
+ </sect2>
+ </sect1>
+
<sect1 id="functions-info">
<title>System Information Functions and Operators</title>
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index 2987a55..db1a6bd 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -3427,6 +3427,16 @@ COMMIT;
</programlisting>
</para>
</sect3>
+ <sect3>
+ <title>Querying cursors</title>
+
+ <para>
+ Section <xref linkend="functions-cursor"/> discusses the use of
+ <function>ROWS_IN</function> which allows a returned
+ <type>REFCURSOR</type> to be queried in a normal
+ <command>SELECT</command> command.
+ </para>
+ </sect3>
</sect2>
<sect2 id="plpgsql-cursor-for-loop">
diff --git a/src/backend/utils/adt/Makefile b/src/backend/utils/adt/Makefile
index 790d7a2..ae5d6d3 100644
--- a/src/backend/utils/adt/Makefile
+++ b/src/backend/utils/adt/Makefile
@@ -81,6 +81,7 @@ OBJS = \
rangetypes_selfuncs.o \
rangetypes_spgist.o \
rangetypes_typanalyze.o \
+ refcursor.o \
regexp.o \
regproc.o \
ri_triggers.o \
diff --git a/src/backend/utils/adt/refcursor.c b/src/backend/utils/adt/refcursor.c
new file mode 100644
index 0000000..d955c2f
--- /dev/null
+++ b/src/backend/utils/adt/refcursor.c
@@ -0,0 +1,394 @@
+/*-------------------------------------------------------------------------
+ *
+ * refcursor.c
+ *
+ * IDENTIFICATION
+ * src/backend/utils/adt/refcursor.c
+ *
+ *-------------------------------------------------------------------------
+ */
+
+#include "postgres.h"
+
+#include "access/htup_details.h"
+#include "catalog/pg_type.h"
+#include "funcapi.h"
+#include "nodes/nodeFuncs.h"
+#include "nodes/supportnodes.h"
+#include "optimizer/optimizer.h"
+#include "tcop/pquery.h"
+#include "utils/builtins.h"
+#include "utils/datum.h"
+#include "utils/lsyscache.h"
+#include "utils/refcursor.h"
+#include "utils/typcache.h"
+
+
+typedef struct SingleSlotDestReceiver
+{
+ DestReceiver pub;
+ TupleTableSlot *received_slot;
+ TupleDesc tupdesc;
+} SingleSlotDestReceiver;
+
+/*
+ * sqlfunction_startup --- executor startup
+ */
+static void
+ssdr_startup(DestReceiver *self, int operation, TupleDesc typeinfo)
+{
+ SingleSlotDestReceiver *myState = (SingleSlotDestReceiver *) self;
+
+ myState->tupdesc = typeinfo;
+}
+
+/*
+ * sqlfunction_receive --- receive one tuple
+ */
+static bool
+ssdr_receive(TupleTableSlot *slot, DestReceiver *self)
+{
+ SingleSlotDestReceiver *myState = (SingleSlotDestReceiver *) self;
+
+ myState->received_slot = slot;
+
+ return true;
+}
+
+/*
+ * sqlfunction_shutdown --- executor end
+ */
+static void
+ssdr_shutdown(DestReceiver *self)
+{
+ /* no-op */
+}
+
+/*
+ * sqlfunction_destroy --- release DestReceiver object
+ */
+static void
+ssdr_destroy(DestReceiver *self)
+{
+ pfree(self);
+}
+
+/*
+ * CreateSingleSlotDestReceiver -- create a DestReceiver
+ * that acquires a single tupleslot
+ */
+static DestReceiver *
+CreateSingleSlotDestReceiver(void)
+{
+ SingleSlotDestReceiver *self = (SingleSlotDestReceiver *) palloc(sizeof(SingleSlotDestReceiver));
+
+ self->pub.receiveSlot = ssdr_receive;
+ self->pub.rStartup = ssdr_startup;
+ self->pub.rShutdown = ssdr_shutdown;
+ self->pub.rDestroy = ssdr_destroy;
+ self->pub.mydest = -1;
+
+ /* private fields will be set by ssdr_startup */
+
+ return (DestReceiver *) self;
+}
+
+/*
+ * ROWS_IN (REFCURSOR)
+ */
+Datum
+rows_in_refcursor(PG_FUNCTION_ARGS)
+{
+ typedef struct
+ {
+ Portal portal;
+ SingleSlotDestReceiver *dest;
+ TupleDesc tupdesc;
+ } rows_in_refcursor_fctx;
+
+ ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+ FuncCallContext *funcctx;
+ rows_in_refcursor_fctx *fctx;
+ char *portal_name;
+ FetchDirection direction;
+ uint64 howMany;
+ uint64 nfetched;
+ bool first_call;
+ MemoryContext oldcontext;
+ HeapTuple tuple;
+ Datum datum;
+ HeapTupleHeader result;
+
+ /* stuff done only on the first call of the function */
+ first_call = SRF_IS_FIRSTCALL();
+ if (first_call)
+ {
+ /* create a function context for cross-call persistence */
+ funcctx = SRF_FIRSTCALL_INIT();
+
+ /* Check to see if caller supports us returning a set */
+ if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("set-valued function called in context that cannot accept a set")));
+ if (!(rsinfo->allowedModes & SFRM_ValuePerCall))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("value per call mode required, but it is not " \
+ "allowed in this context")));
+
+ /*
+ * switch to memory context appropriate for multiple function calls
+ */
+ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ /* allocate memory for user context */
+ fctx = (rows_in_refcursor_fctx *) palloc(sizeof(rows_in_refcursor_fctx));
+
+ fctx->dest = (SingleSlotDestReceiver *) CreateSingleSlotDestReceiver();
+
+ MemoryContextSwitchTo(oldcontext);
+
+ portal_name = text_to_cstring(PG_GETARG_TEXT_PP(0));
+
+ fctx->portal = GetPortalByName(portal_name);
+
+ /* Check that the portal exists */
+ if (!PortalIsValid(fctx->portal))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_CURSOR),
+ errmsg("cursor \"%s\" does not exist", portal_name)));
+
+ /* ensure the Portal is ready (has already been OPEN'ed) */
+ if (! (fctx->portal->status == PORTAL_DEFINED ||
+ fctx->portal->status == PORTAL_READY))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_CURSOR_STATE),
+ errmsg("cursor \"%s\" is not OPEN", portal_name)));
+
+ /*
+ * Ensure the Portal returns some results (so is not a utility
+ * command, or set of multiple statements.
+ */
+ if (! (fctx->portal->strategy == PORTAL_ONE_SELECT ||
+ fctx->portal->strategy == PORTAL_ONE_RETURNING ||
+ fctx->portal->strategy == PORTAL_ONE_MOD_WITH))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_CURSOR_DEFINITION),
+ errmsg("cursor \"%s\" does not return result set", portal_name)));
+
+ pfree(portal_name);
+
+ funcctx->user_fctx = fctx;
+ }
+
+ /* stuff done on every call of the function */
+ funcctx = SRF_PERCALL_SETUP();
+ fctx = funcctx->user_fctx;
+
+ rsinfo->returnMode = SFRM_ValuePerCall;
+
+ if (first_call)
+ {
+ direction = FETCH_ABSOLUTE; /* start from absolute position */
+ howMany = 1; /* position = 1 (top); also reads one row */
+ }
+ else
+ {
+ direction = FETCH_FORWARD; /* othrewise advance forward */
+ howMany = 1; /* count = 1 (read one row) */
+ }
+
+ /* Run the cursor... */
+ nfetched = PortalRunFetch (fctx->portal, direction, howMany,
+ (DestReceiver *) fctx->dest);
+
+ /*
+ * Initialise the Tuple Desriptor. (This can't be done until
+ * we have done our first fetch.)
+ */
+ if (first_call)
+ {
+ MemoryContext per_query_ctx = rsinfo->econtext->ecxt_per_query_memory;
+ MemoryContext oldcontext = MemoryContextSwitchTo(per_query_ctx);
+
+ fctx->tupdesc = CreateTupleDescCopy (fctx->dest->tupdesc);
+
+ /* For RECORD results, make sure a typmod has been assigned */
+ if (fctx->tupdesc->tdtypeid == RECORDOID &&
+ fctx->tupdesc->tdtypmod < 0)
+ assign_record_type_typmod(fctx->tupdesc);
+
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ rsinfo->setDesc = fctx->tupdesc;
+
+ Assert (nfetched <= 1);
+
+ if (nfetched == 1)
+ {
+ /*
+ * Convert the TableTupleSlot to a HeapTuple (doesn't
+ * materialise and doesn't copy unless unavoidable).
+ */
+ tuple = ExecFetchSlotHeapTuple (fctx->dest->received_slot,
+ /* materialise */ false,
+ NULL);
+
+ /*
+ * Avoid making a copy if the HeapTuple is already
+ * fully in memory and marked with correct typeid/typmod.
+ */
+ datum = PointerGetDatum (tuple->t_data);
+ if (HeapTupleHasExternal(tuple) ||
+ HeapTupleHeaderGetTypeId(tuple->t_data) != fctx->tupdesc->tdtypeid ||
+ HeapTupleHeaderGetTypMod(tuple->t_data) != fctx->tupdesc->tdtypmod)
+ {
+ /*
+ * Copy the tuple as a Datum, ensuring it is
+ * fully in memory in the process.
+ */
+ datum = heap_copy_tuple_as_datum (tuple, fctx->tupdesc);
+ }
+
+ /*
+ * Obtain HeapTupleHeader for the Datum, which is in
+ * memory, so should not require a copy.
+ */
+ result = DatumGetHeapTupleHeader (datum);
+
+ SRF_RETURN_NEXT (funcctx, PointerGetDatum (result));
+ }
+ else /* no rows retrieved */
+ {
+ /* it will have been pfree()'ed by ssdr_destroy() */
+ fctx->dest = NULL;
+
+ /* fctx itself will be released when multi_call_memory_ctx goes. */
+
+ SRF_RETURN_DONE(funcctx);
+ }
+}
+
+
+/*
+ * Planner support function for ROWS_IN (REFCURSOR)
+ */
+Datum
+rows_in_refcursor_support(PG_FUNCTION_ARGS)
+{
+ Node *rawreq = (Node *) PG_GETARG_POINTER(0);
+ Node *ret;
+ Node *req_node;
+ SupportRequestRows *rows_req = NULL; /* keep compiler happy */
+ SupportRequestCost *cost_req = NULL; /* keep compiler happy */
+ List *args;
+ Node *arg1;
+ Const *cexpr;
+ char *portal_name;
+ Portal portal;
+ QueryDesc *qdesc;
+ PlanState *planstate;
+ Oid typoutput;
+ bool typIsVarlena;
+
+ if (IsA(rawreq, SupportRequestRows))
+ {
+ rows_req = (SupportRequestRows *) rawreq;
+
+ req_node = rows_req->node;
+ }
+ else if (IsA (rawreq, SupportRequestCost))
+ {
+ cost_req = (SupportRequestCost *) rawreq;
+
+ req_node = cost_req->node;
+ }
+ else
+ PG_RETURN_POINTER(NULL);
+
+ /* The call to ROWS_IN should be in a FuncExpr node. */
+ if (!is_funcclause(req_node))
+ PG_RETURN_POINTER(NULL);
+
+ args = ((FuncExpr *) req_node)->args;
+ if (args == NULL)
+ PG_RETURN_POINTER(NULL);
+
+ arg1 = linitial(args);
+
+ /*
+ * We can only estimate the cost if the REFCURSOR is
+ * already simplified to a Const.
+ */
+ if (!IsA (arg1, Const))
+ PG_RETURN_POINTER(NULL);
+
+ cexpr = (Const *) arg1;
+
+ if (cexpr->constisnull)
+ PG_RETURN_POINTER(NULL);
+
+ if (cexpr->consttype != REFCURSOROID)
+ PG_RETURN_POINTER(NULL);
+
+ /*
+ * We can ignore a check on the collation because we are not
+ * interested in sorting, and typemod because REFCURSOR has
+ * no modifyable attributes.
+ */
+ getTypeOutputInfo(cexpr->consttype, &typoutput, &typIsVarlena);
+
+ portal_name = OidOutputFunctionCall(typoutput, cexpr->constvalue);
+
+ portal = GetPortalByName(portal_name);
+
+ /* Check that the portal exists */
+ if (!PortalIsValid(portal))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_CURSOR),
+ errmsg("cursor \"%s\" does not exist", portal_name)));
+
+ /* ensure the Portal is ready (has already been OPEN'ed) */
+ if (! (portal->status == PORTAL_DEFINED ||
+ portal->status == PORTAL_READY))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_CURSOR_STATE),
+ errmsg("cursor \"%s\" is not OPEN", portal_name)));
+
+ /*
+ * Ensure the Portal returns some results (so is
+ * not a utility command, or set of multiple statements.
+ */
+ if (! (portal->strategy == PORTAL_ONE_SELECT ||
+ portal->strategy == PORTAL_ONE_RETURNING ||
+ portal->strategy == PORTAL_ONE_MOD_WITH))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_CURSOR_DEFINITION),
+ errmsg("cursor \"%s\" does not return result set", portal_name)));
+
+ qdesc = portal->queryDesc;
+ if (qdesc == NULL)
+ PG_RETURN_POINTER(NULL);
+
+ planstate = qdesc->planstate;
+ if (planstate == NULL)
+ PG_RETURN_POINTER(NULL);
+
+ if (rows_req)
+ {
+ rows_req->rows = planstate->plan->plan_rows;
+ }
+ else if (cost_req)
+ {
+ cost_req->startup = planstate->plan->startup_cost;
+ cost_req->per_tuple = (planstate->plan->total_cost - planstate->plan->startup_cost);
+ if (planstate->plan->plan_rows != 0.0)
+ cost_req->per_tuple /= planstate->plan->plan_rows;
+ }
+
+ ret = (Node *) rawreq;
+
+ PG_RETURN_POINTER(ret);
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index fcf2a12..64fabf2 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -1575,6 +1575,13 @@
{ oid => '3996', descr => 'planner support for array_unnest',
proname => 'array_unnest_support', prorettype => 'internal',
proargtypes => 'internal', prosrc => 'array_unnest_support' },
+{ oid => '12921', descr => 'expand refcursor to set of rows',
+ proname => 'rows_in', prorows => '100', prosupport => 'rows_in_refcursor_support',
+ proretset => 't', prorettype => 'record', proargtypes => 'refcursor',
+ prosrc => 'rows_in_refcursor' },
+{ oid => '12923', descr => 'planner support for rows_in',
+ proname => 'rows_in_refcursor_support', prorettype => 'internal',
+ proargtypes => 'internal', prosrc => 'rows_in_refcursor_support' },
{ oid => '3167',
descr => 'remove any occurrences of an element from an array',
proname => 'array_remove', proisstrict => 'f', prorettype => 'anyarray',
diff --git a/src/include/utils/refcursor.h b/src/include/utils/refcursor.h
new file mode 100644
index 0000000..e636193
--- /dev/null
+++ b/src/include/utils/refcursor.h
@@ -0,0 +1,15 @@
+/*-------------------------------------------------------------------------
+*
+* refcursor.c
+*
+* IDENTIFICATION
+* src/include/utils/refcursor.c
+*
+*-------------------------------------------------------------------------
+*/
+
+#ifndef refcursor_h
+#define refcursor_h
+
+
+#endif /* refcursor_h */
diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out
index cd2c79f..f8d40eb 100644
--- a/src/test/regress/expected/plpgsql.out
+++ b/src/test/regress/expected/plpgsql.out
@@ -2078,6 +2078,358 @@ select refcursor_test2(20000, 20000) as "Should be false",
f | t
(1 row)
+-- Check ability to consume from REFCURSOR in SELECT
+explain (verbose, costs off) select * from rows_in(return_unnamed_refcursor()) as (a int);
+ QUERY PLAN
+------------------------------------------------------
+ Function Scan on pg_catalog.rows_in
+ Output: a
+ Function Call: rows_in(return_unnamed_refcursor())
+(3 rows)
+
+select * from rows_in(return_unnamed_refcursor()) as (a int);
+ a
+-----
+ 5
+ 50
+ 500
+(3 rows)
+
+explain (verbose, costs off) select * from rows_in(return_unnamed_refcursor()) as (a int) where a >= 50;
+ QUERY PLAN
+------------------------------------------------------
+ Function Scan on pg_catalog.rows_in
+ Output: a
+ Function Call: rows_in(return_unnamed_refcursor())
+ Filter: (rows_in.a >= 50)
+(4 rows)
+
+select * from rows_in(return_unnamed_refcursor()) as (a int) where a >= 50;
+ a
+-----
+ 50
+ 500
+(2 rows)
+
+explain (verbose, costs off) select * from rows_in(return_unnamed_refcursor()) as (a int) order by a desc;
+ QUERY PLAN
+------------------------------------------------------------
+ Sort
+ Output: a
+ Sort Key: rows_in.a DESC
+ -> Function Scan on pg_catalog.rows_in
+ Output: a
+ Function Call: rows_in(return_unnamed_refcursor())
+(6 rows)
+
+select * from rows_in(return_unnamed_refcursor()) as (a int) order by a desc;
+ a
+-----
+ 500
+ 50
+ 5
+(3 rows)
+
+-- Check multiple scan of REFCURSOR
+explain (verbose, costs off) select * from (select 1 nr union all select 2) as scan, rows_in(return_unnamed_refcursor()) as (a int) order by nr;
+ QUERY PLAN
+------------------------------------------------------------------
+ Sort
+ Output: (1), rows_in.a
+ Sort Key: (1)
+ -> Nested Loop
+ Output: (1), rows_in.a
+ -> Function Scan on pg_catalog.rows_in
+ Output: rows_in.a
+ Function Call: rows_in(return_unnamed_refcursor())
+ -> Materialize
+ Output: (1)
+ -> Append
+ -> Result
+ Output: 1
+ -> Result
+ Output: 2
+(15 rows)
+
+select * from (select 1 nr union all select 2) as scan, rows_in(return_unnamed_refcursor()) as (a int) order by nr;
+ nr | a
+----+-----
+ 1 | 5
+ 1 | 50
+ 1 | 500
+ 2 | 5
+ 2 | 50
+ 2 | 500
+(6 rows)
+
+-- Check multiple reference to single REFCURSOR
+explain (verbose, costs off)
+ select * from
+ rows_in(return_unnamed_refcursor()) as a(n int),
+ rows_in(return_unnamed_refcursor()) as b(n int)
+ order by a.n, b.n;
+ QUERY PLAN
+------------------------------------------------------------------
+ Sort
+ Output: a.n, b.n
+ Sort Key: a.n, b.n
+ -> Nested Loop
+ Output: a.n, b.n
+ -> Function Scan on pg_catalog.rows_in a
+ Output: a.n
+ Function Call: rows_in(return_unnamed_refcursor())
+ -> Function Scan on pg_catalog.rows_in b
+ Output: b.n
+ Function Call: rows_in(return_unnamed_refcursor())
+(11 rows)
+
+select * from
+ rows_in(return_unnamed_refcursor()) as a(n int),
+ rows_in(return_unnamed_refcursor()) as b(n int)
+ order by a.n, b.n;
+ n | n
+-----+-----
+ 5 | 5
+ 5 | 50
+ 5 | 500
+ 50 | 5
+ 50 | 50
+ 50 | 500
+ 500 | 5
+ 500 | 50
+ 500 | 500
+(9 rows)
+
+explain (verbose, costs off)
+ select r.cur::text, a.n, b.n from
+ (select return_unnamed_refcursor() cur) r,
+ rows_in (r.cur) as a(n int),
+ rows_in (r.cur) as b(n int)
+ order by r.cur::text, a.n, b.n;
+ QUERY PLAN
+--------------------------------------------------------------------------
+ Sort
+ Output: (((return_unnamed_refcursor()))::text), a.n, b.n
+ Sort Key: (((return_unnamed_refcursor()))::text), a.n, b.n
+ -> Nested Loop
+ Output: ((return_unnamed_refcursor()))::text, a.n, b.n
+ -> Nested Loop
+ Output: (return_unnamed_refcursor()), a.n
+ -> Result
+ Output: return_unnamed_refcursor()
+ -> Function Scan on pg_catalog.rows_in a
+ Output: a.n
+ Function Call: rows_in((return_unnamed_refcursor()))
+ -> Function Scan on pg_catalog.rows_in b
+ Output: b.n
+ Function Call: rows_in((return_unnamed_refcursor()))
+(15 rows)
+
+select r.cur::text, a.n, b.n from
+ (select return_unnamed_refcursor() cur) r,
+ rows_in (r.cur) as a(n int),
+ rows_in (r.cur) as b(n int)
+ order by r.cur::text, a.n, b.n;
+ cur | n | n
+---------------------+-----+-----
+ <unnamed portal 11> | 5 | 5
+ <unnamed portal 11> | 5 | 50
+ <unnamed portal 11> | 5 | 500
+ <unnamed portal 11> | 50 | 5
+ <unnamed portal 11> | 50 | 50
+ <unnamed portal 11> | 50 | 500
+ <unnamed portal 11> | 500 | 5
+ <unnamed portal 11> | 500 | 50
+ <unnamed portal 11> | 500 | 500
+(9 rows)
+
+-- Check use of REFCURSOR in WITH
+with rcq as (
+ select * from rows_in(return_unnamed_refcursor()) as (a int)
+)
+select * from rcq where a <= 50;
+ a
+----
+ 5
+ 50
+(2 rows)
+
+-- Check attempt to UPDATE/DELETE REFCURSOR fails
+update rows_in(return_unnamed_refcursor()) set a = 2;
+ERROR: syntax error at or near "("
+LINE 1: update rows_in(return_unnamed_refcursor()) set a = 2;
+ ^
+delete from rows_in(return_unnamed_refcursor());
+ERROR: syntax error at or near "("
+LINE 1: delete from rows_in(return_unnamed_refcursor());
+ ^
+-- Check type consistency
+select * from rows_in(return_unnamed_refcursor()) as (a int, b int);
+ERROR: function return row and query-specified return row do not match
+DETAIL: Returned row contains 1 attribute, but query expects 2.
+select * from rows_in(return_unnamed_refcursor()) as (a text);
+ERROR: function return row and query-specified return row do not match
+DETAIL: Returned type integer at ordinal position 1, but query expects text.
+select * from rows_in(return_unnamed_refcursor()) as (a jsonb);
+ERROR: function return row and query-specified return row do not match
+DETAIL: Returned type integer at ordinal position 1, but query expects jsonb.
+-- Check consumption from REFCURSOR inside plpgsql FUNCTION
+create function refcursor_test3(input refcursor) returns refcursor as $$
+declare
+ rc refcursor;
+begin
+ open rc for select a, a+1 as a_plus_1 from rows_in(input) as (a int);
+ return rc;
+end
+$$ language plpgsql;
+explain (verbose, costs off) select * from rows_in(refcursor_test3(return_unnamed_refcursor())) as (a int, ap1 int) where a >= 50;
+ QUERY PLAN
+-----------------------------------------------------------------------
+ Function Scan on pg_catalog.rows_in
+ Output: a, ap1
+ Function Call: rows_in(refcursor_test3(return_unnamed_refcursor()))
+ Filter: (rows_in.a >= 50)
+(4 rows)
+
+select * from rows_in(refcursor_test3(return_unnamed_refcursor())) as (a int, ap1 int) where a >= 50;
+ a | ap1
+-----+-----
+ 50 | 51
+ 500 | 501
+(2 rows)
+
+drop function refcursor_test3;
+-- Check consumption from REFCURSOR defined with USING
+create function refcursor_test4(minimum int) returns refcursor as $$
+declare
+ rc refcursor;
+begin
+ OPEN rc FOR EXECUTE 'select a from rc_test where a >= $1'
+ USING (minimum);
+ return rc;
+end
+$$ language plpgsql;
+explain (verbose, costs off)
+ select * from rows_in(refcursor_test4(50)) as (a int);
+ QUERY PLAN
+-----------------------------------------------
+ Function Scan on pg_catalog.rows_in
+ Output: a
+ Function Call: rows_in(refcursor_test4(50))
+(3 rows)
+
+select * from rows_in(refcursor_test4(50)) as (a int);
+ a
+-----
+ 50
+ 500
+(2 rows)
+
+drop function refcursor_test4;
+-- Check consumption from REFCURSOR defined in immediate mode
+begin;
+declare c cursor for
+ select 1 as i, 'one' v
+ union all
+ select 2, 'two';
+select * from rows_in('c'::refcursor) as (i int, v text);
+ i | v
+---+-----
+ 1 | one
+ 2 | two
+(2 rows)
+
+rollback;
+-- Check consumption from REFCURSOR directly in target list (tSRF)
+-- (This isn't particularly useful, but it should work.)
+begin;
+declare c cursor for
+ select 1 as i, 'one' v
+ union all
+ select 2, 'two';
+select rows_in('c'::refcursor);
+ rows_in
+---------
+ (1,one)
+ (2,two)
+(2 rows)
+
+rollback;
+begin;
+declare c cursor for
+ select 1 as i, 'one' v
+ union all
+ select 2, 'two';
+select rows_in('c'::refcursor), * from rows_in('c'::refcursor) as (i int, v text);
+ rows_in | i | v
+---------+---+-----
+ (1,one) | 1 | one
+ (2,two) | 1 | one
+ (1,one) | 2 | two
+ (2,two) | 2 | two
+(4 rows)
+
+rollback;
+-- Check use of ROWS_IN with row_to_json both in the target list
+-- and in the FROM.
+begin;
+declare c cursor for
+ with recursive t as (
+ select 1 i
+ union all
+ select i + 1
+ from t
+ where i + 1 <= 10
+ )
+ select i, 'number ' || i t from t;
+select row_to_json(rows_in('c'::refcursor));
+ row_to_json
+--------------------------
+ {"i":1,"t":"number 1"}
+ {"i":2,"t":"number 2"}
+ {"i":3,"t":"number 3"}
+ {"i":4,"t":"number 4"}
+ {"i":5,"t":"number 5"}
+ {"i":6,"t":"number 6"}
+ {"i":7,"t":"number 7"}
+ {"i":8,"t":"number 8"}
+ {"i":9,"t":"number 9"}
+ {"i":10,"t":"number 10"}
+(10 rows)
+
+rollback;
+begin;
+declare c cursor for
+ with recursive t as (
+ select 1 i
+ union all
+ select i + 1
+ from t
+ where i + 1 <= 1000
+ )
+ select i, 'number ' || i t from t;
+select j.r,
+ (j.r->'i')::int i, (j.r->'t')::text t
+ from (select row_to_json(rows_in('c'::refcursor))::jsonb as r) j
+ where
+ (j.r->'i')::int >= 50
+ and (j.r->'i')::int <= 60;
+ r | i | t
+-----------------------------+----+-------------
+ {"i": 50, "t": "number 50"} | 50 | "number 50"
+ {"i": 51, "t": "number 51"} | 51 | "number 51"
+ {"i": 52, "t": "number 52"} | 52 | "number 52"
+ {"i": 53, "t": "number 53"} | 53 | "number 53"
+ {"i": 54, "t": "number 54"} | 54 | "number 54"
+ {"i": 55, "t": "number 55"} | 55 | "number 55"
+ {"i": 56, "t": "number 56"} | 56 | "number 56"
+ {"i": 57, "t": "number 57"} | 57 | "number 57"
+ {"i": 58, "t": "number 58"} | 58 | "number 58"
+ {"i": 59, "t": "number 59"} | 59 | "number 59"
+ {"i": 60, "t": "number 60"} | 60 | "number 60"
+(11 rows)
+
+rollback;
--
-- tests for cursors with named parameter arguments
--
diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql
index d841d8c..2fc76a9 100644
--- a/src/test/regress/sql/plpgsql.sql
+++ b/src/test/regress/sql/plpgsql.sql
@@ -1805,6 +1805,143 @@ $$ language plpgsql;
select refcursor_test2(20000, 20000) as "Should be false",
refcursor_test2(20, 20) as "Should be true";
+-- Check ability to consume from REFCURSOR in SELECT
+explain (verbose, costs off) select * from rows_in(return_unnamed_refcursor()) as (a int);
+select * from rows_in(return_unnamed_refcursor()) as (a int);
+explain (verbose, costs off) select * from rows_in(return_unnamed_refcursor()) as (a int) where a >= 50;
+select * from rows_in(return_unnamed_refcursor()) as (a int) where a >= 50;
+explain (verbose, costs off) select * from rows_in(return_unnamed_refcursor()) as (a int) order by a desc;
+select * from rows_in(return_unnamed_refcursor()) as (a int) order by a desc;
+
+-- Check multiple scan of REFCURSOR
+explain (verbose, costs off) select * from (select 1 nr union all select 2) as scan, rows_in(return_unnamed_refcursor()) as (a int) order by nr;
+select * from (select 1 nr union all select 2) as scan, rows_in(return_unnamed_refcursor()) as (a int) order by nr;
+
+-- Check multiple reference to single REFCURSOR
+explain (verbose, costs off)
+ select * from
+ rows_in(return_unnamed_refcursor()) as a(n int),
+ rows_in(return_unnamed_refcursor()) as b(n int)
+ order by a.n, b.n;
+select * from
+ rows_in(return_unnamed_refcursor()) as a(n int),
+ rows_in(return_unnamed_refcursor()) as b(n int)
+ order by a.n, b.n;
+explain (verbose, costs off)
+ select r.cur::text, a.n, b.n from
+ (select return_unnamed_refcursor() cur) r,
+ rows_in (r.cur) as a(n int),
+ rows_in (r.cur) as b(n int)
+ order by r.cur::text, a.n, b.n;
+select r.cur::text, a.n, b.n from
+ (select return_unnamed_refcursor() cur) r,
+ rows_in (r.cur) as a(n int),
+ rows_in (r.cur) as b(n int)
+ order by r.cur::text, a.n, b.n;
+
+-- Check use of REFCURSOR in WITH
+with rcq as (
+ select * from rows_in(return_unnamed_refcursor()) as (a int)
+)
+select * from rcq where a <= 50;
+
+-- Check attempt to UPDATE/DELETE REFCURSOR fails
+update rows_in(return_unnamed_refcursor()) set a = 2;
+delete from rows_in(return_unnamed_refcursor());
+
+-- Check type consistency
+select * from rows_in(return_unnamed_refcursor()) as (a int, b int);
+select * from rows_in(return_unnamed_refcursor()) as (a text);
+select * from rows_in(return_unnamed_refcursor()) as (a jsonb);
+
+-- Check consumption from REFCURSOR inside plpgsql FUNCTION
+create function refcursor_test3(input refcursor) returns refcursor as $$
+declare
+ rc refcursor;
+begin
+ open rc for select a, a+1 as a_plus_1 from rows_in(input) as (a int);
+ return rc;
+end
+$$ language plpgsql;
+explain (verbose, costs off) select * from rows_in(refcursor_test3(return_unnamed_refcursor())) as (a int, ap1 int) where a >= 50;
+select * from rows_in(refcursor_test3(return_unnamed_refcursor())) as (a int, ap1 int) where a >= 50;
+
+drop function refcursor_test3;
+
+-- Check consumption from REFCURSOR defined with USING
+create function refcursor_test4(minimum int) returns refcursor as $$
+declare
+ rc refcursor;
+begin
+ OPEN rc FOR EXECUTE 'select a from rc_test where a >= $1'
+ USING (minimum);
+ return rc;
+end
+$$ language plpgsql;
+explain (verbose, costs off)
+ select * from rows_in(refcursor_test4(50)) as (a int);
+select * from rows_in(refcursor_test4(50)) as (a int);
+
+drop function refcursor_test4;
+
+-- Check consumption from REFCURSOR defined in immediate mode
+begin;
+declare c cursor for
+ select 1 as i, 'one' v
+ union all
+ select 2, 'two';
+select * from rows_in('c'::refcursor) as (i int, v text);
+rollback;
+
+-- Check consumption from REFCURSOR directly in target list (tSRF)
+-- (This isn't particularly useful, but it should work.)
+begin;
+declare c cursor for
+ select 1 as i, 'one' v
+ union all
+ select 2, 'two';
+select rows_in('c'::refcursor);
+rollback;
+begin;
+declare c cursor for
+ select 1 as i, 'one' v
+ union all
+ select 2, 'two';
+select rows_in('c'::refcursor), * from rows_in('c'::refcursor) as (i int, v text);
+rollback;
+
+-- Check use of ROWS_IN with row_to_json both in the target list
+-- and in the FROM.
+begin;
+declare c cursor for
+ with recursive t as (
+ select 1 i
+ union all
+ select i + 1
+ from t
+ where i + 1 <= 10
+ )
+ select i, 'number ' || i t from t;
+select row_to_json(rows_in('c'::refcursor));
+rollback;
+begin;
+declare c cursor for
+ with recursive t as (
+ select 1 i
+ union all
+ select i + 1
+ from t
+ where i + 1 <= 1000
+ )
+ select i, 'number ' || i t from t;
+select j.r,
+ (j.r->'i')::int i, (j.r->'t')::text t
+ from (select row_to_json(rows_in('c'::refcursor))::jsonb as r) j
+ where
+ (j.r->'i')::int >= 50
+ and (j.r->'i')::int <= 60;
+rollback;
+
--
-- tests for cursors with named parameter arguments
--
Re: [WIP] UNNEST(REFCURSOR): allowing SELECT to consume data from a REFCURSOR
This is an interesting feature, but it seems that the author has abandoned
development, what happens now? Will this be postponed from commitfest to
commitfest and never be taken over by anyone?
Massimo.
Il giorno ven 6 mar 2020 alle ore 22:36 Dent John <denty@qqdd.eu> ha
scritto:
Show quoted text
On 22 Feb 2020, at 10:38, Dent John <denty@QQdd.eu> wrote:
On 18 Feb 2020, at 03:03, Thomas Munro <thomas.munro@gmail.com> wrote:
From the trivialities department, I see a bunch of warnings about
local declaration placement (we're still using C90 rules for those by
project policy):[…]
[…]
My bad. I missed on declaration.
Another patch attached.
d.
Re: [WIP] UNNEST(REFCURSOR): allowing SELECT to consume data from a REFCURSOR
Hi Massimo,
Thanks for the interest, and my apologies for the late reply.
I’m not particularly abandoning it, but I don’t have particular reason to make further changes at the moment. Far as I’m concerned it works, and the main question is whether it is acceptable and useful.
I’d be happy if you have feedback that evolves it or might push it up the queue for commitfest review.
d.
Show quoted text
On 18 Jan 2021, at 23:09, Massimo Fidanza <malix0@gmail.com> wrote:
This is an interesting feature, but it seems that the author has abandoned development, what happens now? Will this be postponed from commitfest to commitfest and never be taken over by anyone?
Massimo.
Il giorno ven 6 mar 2020 alle ore 22:36 Dent John <denty@qqdd.eu <mailto:denty@qqdd.eu>> ha scritto:
On 22 Feb 2020, at 10:38, Dent John <denty@QQdd.eu> wrote:
On 18 Feb 2020, at 03:03, Thomas Munro <thomas.munro@gmail.com <mailto:thomas.munro@gmail.com>> wrote:
From the trivialities department, I see a bunch of warnings about
local declaration placement (we're still using C90 rules for those by
project policy):[…]
[…]
My bad. I missed on declaration.
Another patch attached.
d.
Re: [WIP] UNNEST(REFCURSOR): allowing SELECT to consume data from a REFCURSOR
Hi John,
I never build postgresql from source, so I must get some information on how
to apply your patch and do some test. I can't review your code because I
know nothing about Postgresql internals and just basic C. I am mainly a
PL/SQL programmer, with experience with PHP, Python and Javascript. If I
can give some contribution I will be happy, but I need some help.
Massimo
Il giorno dom 7 feb 2021 alle ore 22:35 Dent John <denty@qqdd.co.uk> ha
scritto:
Show quoted text
Hi Massimo,
Thanks for the interest, and my apologies for the late reply.
I’m not particularly abandoning it, but I don’t have particular reason to
make further changes at the moment. Far as I’m concerned it works, and the
main question is whether it is acceptable and useful.I’d be happy if you have feedback that evolves it or might push it up the
queue for commitfest review.d.
On 18 Jan 2021, at 23:09, Massimo Fidanza <malix0@gmail.com> wrote:
This is an interesting feature, but it seems that the author has abandoned
development, what happens now? Will this be postponed from commitfest to
commitfest and never be taken over by anyone?Massimo.
Il giorno ven 6 mar 2020 alle ore 22:36 Dent John <denty@qqdd.eu> ha
scritto:On 22 Feb 2020, at 10:38, Dent John <denty@QQdd.eu> wrote:
On 18 Feb 2020, at 03:03, Thomas Munro <thomas.munro@gmail.com> wrote:
From the trivialities department, I see a bunch of warnings about
local declaration placement (we're still using C90 rules for those by
project policy):[…]
[…]
My bad. I missed on declaration.
Another patch attached.
d.
Re: [WIP] UNNEST(REFCURSOR): allowing SELECT to consume data from a REFCURSOR
Hi Massimo,
Happy to help. And actually, end user (i.e., developer) feedback on the feature’s usefulness is probably one of the more important contributions.
d.
Show quoted text
On 10 Feb 2021, at 08:57, Massimo Fidanza <malix0@gmail.com> wrote:
Hi John,
I never build postgresql from source, so I must get some information on how to apply your patch and do some test. I can't review your code because I know nothing about Postgresql internals and just basic C. I am mainly a PL/SQL programmer, with experience with PHP, Python and Javascript. If I can give some contribution I will be happy, but I need some help.
Massimo
Il giorno dom 7 feb 2021 alle ore 22:35 Dent John <denty@qqdd.co.uk <mailto:denty@qqdd.co.uk>> ha scritto:
Hi Massimo,Thanks for the interest, and my apologies for the late reply.
I’m not particularly abandoning it, but I don’t have particular reason to make further changes at the moment. Far as I’m concerned it works, and the main question is whether it is acceptable and useful.
I’d be happy if you have feedback that evolves it or might push it up the queue for commitfest review.
d.
On 18 Jan 2021, at 23:09, Massimo Fidanza <malix0@gmail.com <mailto:malix0@gmail.com>> wrote:
This is an interesting feature, but it seems that the author has abandoned development, what happens now? Will this be postponed from commitfest to commitfest and never be taken over by anyone?
Massimo.
Il giorno ven 6 mar 2020 alle ore 22:36 Dent John <denty@qqdd.eu <mailto:denty@qqdd.eu>> ha scritto:
On 22 Feb 2020, at 10:38, Dent John <denty@QQdd.eu <mailto:denty@QQdd.eu>> wrote:
On 18 Feb 2020, at 03:03, Thomas Munro <thomas.munro@gmail.com <mailto:thomas.munro@gmail.com>> wrote:
From the trivialities department, I see a bunch of warnings about
local declaration placement (we're still using C90 rules for those by
project policy):[…]
[…]
My bad. I missed on declaration.
Another patch attached.
d.
Re: [WIP] UNNEST(REFCURSOR): allowing SELECT to consume data from a REFCURSOR
Hi,
Trying the v7a patch, here are a few comments:
* SIGSEGV with ON HOLD cursors.
Reproducer:
declare c cursor with hold for select oid,relname
from pg_class order by 1 limit 10;
select * from rows_in('c') as x(f1 oid,f2 name);
consumes a bit of time, then crashes and generates a 13 GB core file
without a usable stacktrace:
Core was generated by `postgres: daniel postgres [local] SELECT '.
Program terminated with signal SIGSEGV, Segmentation fault.
#0 0x00007f4c5b2f3dc9 in ?? ()
(gdb) bt
#0 0x00007f4c5b2f3dc9 in ?? ()
#1 0x0000564567efc505 in ?? ()
#2 0x0000000000000001 in ?? ()
#3 0x000056456a4b28f8 in ?? ()
#4 0x000056456a4b2908 in ?? ()
#5 0x000056456a4b2774 in ?? ()
#6 0x000056456a4ad218 in ?? ()
#7 0x000056456a4b1590 in ?? ()
#8 0x0000000000000010 in ?? ()
#9 0x0000000000000000 in ?? ()
* rows_in() does not fetch from the current position of the cursor,
but from the start. For instance, I would expect that if doing
FETCH FROM cursor followed by SELECT * FROM rows_in('cursor'), the first
row would be ignored by rows_in(). That seems more convenient and more
principled.
*
+ <para>
+ This section describes functions that cursors to be manipulated
+ in normal <command>SELECT</command> queries.
+ </para>A verb seems to be missing.
It should be "function that *allow* cursors to be..." or something
like that?
*
+ The <type>REFCURSOR</type> must be open, and the query must be a
+ <command>SELECT</command> statement. If the <type>REFCURSOR</type>’s
+ output does notAfter </type> there is a fancy quote (codepoint U+2019). There is
currently no codepoint outside of US-ASCII in *.sgml ref/*.sgml, so
they're probably not welcome.
* Also: does the community wants it as a built-in function in core?
As mentioned in a previous round of review, a function like this in
plpgsql comes close:
create function rows_in(x refcursor) returns setof record as $$
declare
r record;
begin
loop
fetch x into r;
exit when not found;
return next r;
end loop;
end $$ language plpgsql;
Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: https://www.manitou-mail.org
Twitter: @DanielVerite
Re: [WIP] UNNEST(REFCURSOR): allowing SELECT to consume data from a REFCURSOR
On 29 Jul 2021, at 16:45, Daniel Verite <daniel@manitou-mail.org> wrote:
Trying the v7a patch, here are a few comments:
This thread has stalled with no update or response to the above, and the patch
errors out on make check for the plpgsql suite. I'm marking this Returned with
Feedback, please resubmit an updated patch if you would like to pursue this
further.
--
Daniel Gustafsson https://vmware.com/