记录和数组与POSTGRESQL

Question

我尝试复制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

谢谢。

Answer 1

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