PL and composite return types
От | Adriaan Joubert |
---|---|
Тема | PL and composite return types |
Дата | |
Msg-id | 36F0AFD5.158843A0@albourne.com обсуждение исходный текст |
Список | pgsql-general |
I have had no luck trying to make functions and composite return types work. From the docs I cannot figure out what I'm doing wrong or whether I've hit some limitations. Following example: create table T ( a int4, b int4 ); insert into T values (2,3); create function tf (int4) returns T as ' declare tr record; r T%rowtype; begin select * into tr where a = $1; r.a = $1; r.b = tr.b; return r; end; ' language 'plpgsql'; create function tfs (int4) returns T as 'select a as a, b as b from T where a = $1;' language 'sql'; tt=> select tf(2); ERROR: cannot expand: null p_rtable tt=> select tfs(2); tfs ---------- 1075737920 (1 row) I didn't expect either result. So what am I doing wrong? and is there a better way to copy a record into a T%rowtype structure? As this is the result of a fairly complex PL function, I would like to do this in PL and not SQL. I managed to get this working by returning the oid of the selected row and then doing another select using the oid, but this requires an additional select, and with the sizes of some of my tables I would like to avoid that. Any help appreciated! Adriaan
В списке pgsql-general по дате отправления: