Re: a column definition list is required for functions returning "record"

On Mon, Aug 29, 2016 at 6:28 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
>> 2016-08-29 1:59 GMT+02:00 Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com>:
>>> It would be nice if there was a way to pass dynamically formed records
>>> around, similar to how you can pass the results of row() around. Someone
>>> else has actually be asking about this at https://github.com/decibel/pg_
>>> lambda/issues/1.
>
>> Probably there is a space to be PLpgSQL more flexible - but there are
>> limits - PLpgSQL is black box for SQL engine, and when output is any record
>> type, then SQL engine knows zero about returning data structure in
>> preprocessing time.
>
> Exactly. You can pass anonymous record types around today, as long as you
> don't do anything that requires knowing what their contents are, either in
> the function or in the calling query:
>
> regression=# create function foor(int,int) returns record language sql as $$ select row($1,$2); $$;
> CREATE FUNCTION
> regression=# select foor(23,45);
> foor
> ---------
> (23,45)
> (1 row)
>
> regression=# create function plr(int,int) returns record language plpgsql as $$begin return row($1,$2); end; $$;
> CREATE FUNCTION
> regression=# select plr(23,45);
> plr
> ---------
> (23,45)
> (1 row)
>
> What you can't do is, eg,
>
> regression=# select * from plr(23,45);
> ERROR: a column definition list is required for functions returning "record"
> LINE 1: select * from plr(23,45);

Another tactic is to use json in such cases:
create function fooj(int,int) returns json language sql as $$ select
to_json((select q from (select $1 as a, $2 as b) q)); $$;
CREATE FUNCTION
postgres=# select fooj(3,4);
fooj
───────────────
{"a":3,"b":4}
(1 row)

the advantage here is that you're not quite as boxed in: stuff like
postgres=# select * from json_each(fooj(3,4));
key │ value
─────┼───────
a │ 3
b │ 4

... in the json api (which is rich and getting richer) can be used to
work around the anonymous row limitations.

merlin