时间戳

首页 > 解决方案 > 使用 POSTGRESQL 的记录和数组

postgresql - 使用 POSTGRESQL 的记录和数组

问题描述

我尝试重现 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

谢谢你。

标签： postgresqlplpgsql

解决方案

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

推荐阅读