我尝试复制Oracle复杂类型的记录。
显然我必须创建数据库类型,因为我不能在pgsql声明中创建复杂类型。
create type my_type as (
.....
);
DO $$
declare
my_cursor cursor (key varchar)
for
select civility, name, firstname, email, telephone
from my_table
where sender = key;
a_type record;
b_type record;
c record;
contact my_type;
contacts my_type [];
i numeric;
begin
select name, road, zcode, town
into strict a_type
from a_table
i = 0;
open my_cursor (a_type.name);
loop
fetch my_cursor into contact;
exit when not found;
contacts[i] = contact;
i = i + 1;
end loop;
select a_type as infos, contacts into strict b_type;
select b_type as sender into strict c;
end $$ LANGUAGE 'plpgsql';
这不会导致任何错误。现在,我想显示变量c。在结束之前,我添加:
raise notice '%', c.sender.infos.name;
在这个上下文中有一个我不理解的错误:
cross-database references are not implemented: c.sender.infos.name
谢谢。
Postgres不支持记录类型的多重解引用。下面的代码可以工作:
do $$
declare r1 record; r2 record; r3 record; _r1 record; _r2 record;
begin
select 10 as x, 20 as y into r1;
select r1 as r1, 30 as z into r2;
select r2 as r2, 40 as w into r3;
raise notice '%', to_json(r3);
_r2 := r3.r2;
_r1 := _r2.r1;
raise notice '%', _r1.x;
end;
$$;
NOTICE: {"r2":{"r1":{"x":10,"y":20},"z":30},"w":40}
NOTICE: 10
DO