Oracle转PostgreSQL：如何实现关联数组元素的类似访问方式？

Oracle中你使用的tab_type_name是字符串索引的关联数组（Associative Array），PostgreSQL没有直接对应的内置类型，但可以通过jsonb类型简便模拟这种键值对式的结构，同时保留对复合类型元素的访问和修改能力。

步骤1：创建对应Oracle RECORD的复合类型 #

先在PostgreSQL中定义与Oracle typ_type_name等价的复合类型：

CREATE TYPE typ_type_name AS (
    typ_elem1 VARCHAR,
    typ_elem2 BOOLEAN DEFAULT FALSE
);

步骤2：用jsonb模拟关联数组并实现存储过程 #

PostgreSQL的jsonb支持字符串键，且能与自定义复合类型互相转换，完美替代Oracle的关联数组。以下是等效的存储过程实现：

CREATE OR REPLACE PROCEDURE proc_name(par_name INOUT jsonb)
LANGUAGE plpgsql
AS $$
DECLARE
    v_name VARCHAR(32) := 'example_text';
    v_rec typ_type_name;
BEGIN
    -- 若指定键不存在，初始化默认值的记录
    IF NOT par_name ? v_name THEN
        v_rec := ROW(NULL, FALSE)::typ_type_name;
        par_name := par_name || jsonb_build_object(v_name, v_rec);
    END IF;

    -- 取出对应键的记录，修改指定字段
    v_rec := (par_name ->> v_name)::typ_type_name;
    v_rec.typ_elem1 := 'more_text';

    -- 将修改后的记录写回jsonb变量
    par_name := jsonb_set(par_name, ARRAY[v_name], to_jsonb(v_rec));
END;
$$;

调用示例 #

可以通过匿名块测试存储过程：

DO $$
DECLARE
    v_tab jsonb := '{}'::jsonb;
BEGIN
    CALL proc_name(v_tab);
    RAISE NOTICE '修改后的关联数组：%', v_tab;
END;
$$;

简化访问的可选方案 #

如果想要更贴近Oracle的语法风格，可以封装一个辅助函数来简化元素修改：

CREATE OR REPLACE FUNCTION set_typ_element(tab jsonb, key VARCHAR(32), new_elem1 VARCHAR)
RETURNS jsonb
LANGUAGE plpgsql
AS $$
DECLARE
    v_rec typ_type_name;
BEGIN
    v_rec := COALESCE((tab ->> key)::typ_type_name, ROW(NULL, FALSE)::typ_type_name);
    v_rec.typ_elem1 := new_elem1;
    RETURN jsonb_set(tab, ARRAY[key], to_jsonb(v_rec));
END;
$$;

此时存储过程可以简化为：

CREATE OR REPLACE PROCEDURE proc_name(par_name INOUT jsonb)
LANGUAGE plpgsql
AS $$
DECLARE
    v_name VARCHAR(32) := 'example_text';
BEGIN
    par_name := set_typ_element(par_name, v_name, 'more_text');
END;
$$;

关键差异说明 #

• Oracle的关联数组是纯内存结构，PostgreSQL中用jsonb变量操作时同样在内存中处理，性能满足PL/pgSQL场景需求。
• 若需要持久化存储这种结构，直接使用jsonb类型的列即可，无需额外处理。

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