Re: [WIP] UNNEST(REFCURSOR): allowing SELECT to consume data from a REFCURSOR

> On 14 Jan 2020, at 14:53, Daniel Verite <daniel(at)manitou-mail(dot)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.