使用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