On Mon, Aug 29, 2016 at 6:28 AM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > Pavel Stehule <pavel.stehule@xxxxxxxxx> writes: >> 2016-08-29 1:59 GMT+02:00 Jim Nasby <Jim.Nasby@xxxxxxxxxxxxxx>: >>> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
