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 по дате отправления:

Предыдущее
От: Rodney McDuff
Дата:
Сообщение: Array function documentation
Следующее
От: hoelc
Дата:
Сообщение: delete data