PostgreSQL中Oracle自定义表类型转换后的赋值问题咨询

我用ora2Pg工具转换了一个包含Oracle自定义对象类型及对应表类型的PL/SQL存储过程。对象类型和存储过程转换完成后，在PostgreSQL 15.1中能成功安装/编译，但将对象类型实例赋值给对应表类型时出错。以下是简化测试案例：

1. Oracle原类型定义 #

CREATE TYPE t_myobj AS OBJECT (rid         NUMBER,
                               description VARCHAR2(100))
/

CREATE TYPE tab_myobjs AS TABLE OF t_myobj;
/

2. 转换后的PostgreSQL类型 #

CREATE TYPE t_myobj AS (rid          integer,
                        description  varchar(100));

CREATE TYPE tab_myobjs AS (tab_myobjs t_myobj[]);

3. 转换后的PostgreSQL存储过程 #

CREATE OR REPLACE PROCEDURE test_prc() LANGUAGE PLPGSQL AS $$
DECLARE
  v_myobj        t_myobj;
  v_myojbs_tab   tab_myobjs;
  v_error        VARCHAR(100);

BEGIN

  RAISE NOTICE '1: START: Assign v_myobj object type attributes';
  v_myobj.rid := 1;
  v_myobj.description := 'test';

  RAISE NOTICE '2: Assign v_myobj object type to v_myojbs_tab';
  v_myojbs_tab := tab_myobjs(v_myobj);

  RAISE NOTICE '3: END';

  EXCEPTION

    WHEN OTHERS THEN
      ROLLBACK;

      v_error := SUBSTR(SQLERRM,1,512);
      RAISE EXCEPTION '%', 'Unhandled Exception: '||v_error USING ERRCODE = '45000';

END; $$

4. 调用报错信息 #

NOTICE:  1: START: Assign v_myobj object type attributes
NOTICE:  2: Assign v_myobj object type to v_myojbs_tab
ERROR:  Unhandled Exception: function tab_myobjs(t_myobj) does not exist
CONTEXT:  PL/pgSQL function test_prc() line 24 at RAISE

核心问题：PostgreSQL是否支持tab_myobjs这类表类型的使用与赋值？如果支持，将t_myobj对象实例赋值给该表类型的正确语法是什么？

PostgreSQL支持这类复合类型结合数组的使用，但当前转换后的tab_myobjs定义和赋值方式存在问题，具体修正如下：

1. 修正类型定义（推荐） #

Oracle的TABLE OF t_myobj对应PostgreSQL中的数组类型，不需要把数组包装成复合类型。正确的类型定义应该是：

CREATE TYPE t_myobj AS (rid integer, description varchar(100));
-- 直接定义为t_myobj的数组类型，与Oracle表类型语义一致
CREATE TYPE tab_myobjs AS t_myobj[];

2. 对应赋值语法 #

如果使用修正后的类型定义，赋值语法更简洁直观：

CREATE OR REPLACE PROCEDURE test_prc() LANGUAGE PLPGSQL AS $$
DECLARE
  v_myobj        t_myobj;
  v_myojbs_tab   tab_myobjs; -- 此时tab_myobjs本质是t_myobj[]类型
BEGIN
  RAISE NOTICE '1: START: Assign v_myobj object type attributes';
  v_myobj.rid := 1;
  v_myobj.description := 'test';

  RAISE NOTICE '2: Assign v_myobj object type to v_myojbs_tab';
  v_myojbs_tab := ARRAY[v_myobj]; -- 直接构造数组赋值

  RAISE NOTICE '3: END';

EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    v_error := SUBSTR(SQLERRM,1,512);
    RAISE EXCEPTION '%', 'Unhandled Exception: '||v_error USING ERRCODE = '45000';
END; $$

3. 保留原转换类型的赋值方式 #

如果必须保留原转换的tab_myobjs（包含数组字段的复合类型），需要明确构造复合类型结构，将单个t_myobj包装成数组后赋值：

-- 方式1：直接构造复合类型
v_myojbs_tab := (ARRAY[v_myobj])::tab_myobjs;

-- 方式2：直接赋值给复合类型的数组字段（更清晰）
v_myojbs_tab.tab_myobjs := ARRAY[v_myobj];

4. 错误原因说明 #

原转换后的tab_myobjs是一个复合类型（内部包含名为tab_myobjs的数组字段），并非直接的数组类型。调用tab_myobjs(v_myobj)时，PostgreSQL会寻找同名构造函数，但默认不会为这种复合类型生成接受单个t_myobj参数的构造函数，因此触发“函数不存在”的错误。

内容的提问来源于stack exchange，提问作者andrewtindle