SQL Server表类型与存储过程转PostgreSQL实现方案咨询

核心差异说明 #

PostgreSQL没有SQL Server原生的表值类型（Table-Valued Type），但可以通过以下两种方案实现等效功能，适配你的存储过程需求：

方案一：自定义复合类型+数组参数（兼容全版本PostgreSQL） #

这种方式通过自定义复合类型模拟表结构，再用数组传递批量数据，是低版本PostgreSQL（<11）的首选方案。

1. 创建对应复合类型 #

先定义与SQL Server表类型结构一致的复合类型：

-- 对应TT_IMG_OWD_OUTWARDCLEARING表类型
CREATE TYPE TT_IMG_OWD_OUTWARDCLEARING AS (
    ITEM_COUNT INT,
    PROCESS_DATE VARCHAR(10) NOT NULL,
    CLEARING_DATE VARCHAR(10),
    VALUE_DATE VARCHAR(10),
    CLEARING_CYCLE INT NOT NULL,
    UNIQUE_IDENTIFIER VARCHAR(25) NOT NULL
);

-- 同理创建TT_IMG_OWD_OUTWARDIMAGES复合类型（按实际结构补全字段）
CREATE TYPE TT_IMG_OWD_OUTWARDIMAGES AS (
    -- 示例字段，替换为实际结构
    IMAGE_ID VARCHAR(50),
    PROCESS_DATE VARCHAR(10) NOT NULL
    -- 其他字段...
);

2. 编写存储过程 #

使用数组作为输入参数，通过UNNEST()函数展开数组处理批量数据：

CREATE OR REPLACE PROCEDURE USP_IMAGO_OWD_OUTWARDCLEARING(
    IN INSERTCOLLECTION TT_IMG_OWD_OUTWARDCLEARING[],
    IN UPDATECOLLECTION TT_IMG_OWD_OUTWARDCLEARING[],
    IN DELETECOLLECTION TT_IMG_OWD_OUTWARDCLEARING[],
    IN INSERTCOLLECTIONIMG TT_IMG_OWD_OUTWARDIMAGES[],
    IN UPDATECOLLECTIONIMG TT_IMG_OWD_OUTWARDIMAGES[],
    IN PRAM_PROCESS_DATE VARCHAR(10),
    IN PRAM_BATCH_NO VARCHAR(14),
    IN PRAM_LAST_ITEM_SEQ INT,
    OUT STATUS INT,
    OUT ERR_MSG VARCHAR(500)
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- 初始化输出参数
    STATUS := 0;
    ERR_MSG := '';

    -- 处理插入集合：展开数组并验证唯一性（模拟原表类型主键约束）
    INSERT INTO IMG_OWD_OUTWARDCLEARING (ITEM_COUNT, PROCESS_DATE, CLEARING_DATE, VALUE_DATE, CLEARING_CYCLE, UNIQUE_IDENTIFIER)
    SELECT t.ITEM_COUNT, t.PROCESS_DATE, t.CLEARING_DATE, t.VALUE_DATE, t.CLEARING_CYCLE, t.UNIQUE_IDENTIFIER
    FROM UNNEST(INSERTCOLLECTION) AS t
    WHERE NOT EXISTS (
        SELECT 1 FROM IMG_OWD_OUTWARDCLEARING
        WHERE PROCESS_DATE = t.PROCESS_DATE
          AND CLEARING_CYCLE = t.CLEARING_CYCLE
          AND BATCH_NO = PRAM_BATCH_NO
          AND UNIQUE_IDENTIFIER = t.UNIQUE_IDENTIFIER
    );

    -- 处理更新集合
    UPDATE IMG_OWD_OUTWARDCLEARING
    SET ITEM_COUNT = t.ITEM_COUNT,
        CLEARING_DATE = t.CLEARING_DATE,
        VALUE_DATE = t.VALUE_DATE
    FROM UNNEST(UPDATECOLLECTION) AS t
    WHERE IMG_OWD_OUTWARDCLEARING.PROCESS_DATE = t.PROCESS_DATE
      AND IMG_OWD_OUTWARDCLEARING.CLEARING_CYCLE = t.CLEARING_CYCLE
      AND IMG_OWD_OUTWARDCLEARING.BATCH_NO = PRAM_BATCH_NO
      AND IMG_OWD_OUTWARDCLEARING.UNIQUE_IDENTIFIER = t.UNIQUE_IDENTIFIER;

    -- 处理删除集合
    DELETE FROM IMG_OWD_OUTWARDCLEARING
    USING UNNEST(DELETECOLLECTION) AS t
    WHERE IMG_OWD_OUTWARDCLEARING.PROCESS_DATE = t.PROCESS_DATE
      AND IMG_OWD_OUTWARDCLEARING.CLEARING_CYCLE = t.CLEARING_CYCLE
      AND IMG_OWD_OUTWARDCLEARING.BATCH_NO = PRAM_BATCH_NO
      AND IMG_OWD_OUTWARDCLEARING.UNIQUE_IDENTIFIER = t.UNIQUE_IDENTIFIER;

    -- 处理图片相关集合（逻辑与上述一致，省略具体代码）
    -- ...

EXCEPTION
    WHEN OTHERS THEN
        STATUS := 1;
        ERR_MSG := SQLERRM;
        ROLLBACK;
END;
$$;

方案二：TABLE类型参数（PostgreSQL 11+推荐） #

PostgreSQL 11及以上版本支持直接使用TABLE作为存储过程参数，写法更贴近SQL Server的表值类型，可读性更强。

编写存储过程 #

直接在参数中定义TABLE结构，无需提前创建复合类型：

CREATE OR REPLACE PROCEDURE USP_IMAGO_OWD_OUTWARDCLEARING(
    IN INSERTCOLLECTION TABLE(
        ITEM_COUNT INT,
        PROCESS_DATE VARCHAR(10) NOT NULL,
        CLEARING_DATE VARCHAR(10),
        VALUE_DATE VARCHAR(10),
        CLEARING_CYCLE INT NOT NULL,
        UNIQUE_IDENTIFIER VARCHAR(25) NOT NULL
    ),
    IN UPDATECOLLECTION TABLE(
        ITEM_COUNT INT,
        PROCESS_DATE VARCHAR(10) NOT NULL,
        CLEARING_DATE VARCHAR(10),
        VALUE_DATE VARCHAR(10),
        CLEARING_CYCLE INT NOT NULL,
        UNIQUE_IDENTIFIER VARCHAR(25) NOT NULL
    ),
    IN DELETECOLLECTION TABLE(
        ITEM_COUNT INT,
        PROCESS_DATE VARCHAR(10) NOT NULL,
        CLEARING_DATE VARCHAR(10),
        VALUE_DATE VARCHAR(10),
        CLEARING_CYCLE INT NOT NULL,
        UNIQUE_IDENTIFIER VARCHAR(25) NOT NULL
    ),
    IN INSERTCOLLECTIONIMG TABLE(
        -- 按TT_IMG_OWD_OUTWARDIMAGES实际结构定义字段
        IMAGE_ID VARCHAR(50),
        PROCESS_DATE VARCHAR(10) NOT NULL
        -- 其他字段...
    ),
    IN UPDATECOLLECTIONIMG TABLE(
        -- 同上
        IMAGE_ID VARCHAR(50),
        PROCESS_DATE VARCHAR(10) NOT NULL
        -- 其他字段...
    ),
    IN PRAM_PROCESS_DATE VARCHAR(10),
    IN PRAM_BATCH_NO VARCHAR(14),
    IN PRAM_LAST_ITEM_SEQ INT,
    OUT STATUS INT,
    OUT ERR_MSG VARCHAR(500)
)
LANGUAGE plpgsql
AS $$
BEGIN
    STATUS := 0;
    ERR_MSG := '';

    -- 处理插入集合，直接查询TABLE参数
    INSERT INTO IMG_OWD_OUTWARDCLEARING (ITEM_COUNT, PROCESS_DATE, CLEARING_DATE, VALUE_DATE, CLEARING_CYCLE, UNIQUE_IDENTIFIER)
    SELECT ITEM_COUNT, PROCESS_DATE, CLEARING_DATE, VALUE_DATE, CLEARING_CYCLE, UNIQUE_IDENTIFIER
    FROM INSERTCOLLECTION
    WHERE NOT EXISTS (
        SELECT 1 FROM IMG_OWD_OUTWARDCLEARING
        WHERE PROCESS_DATE = INSERTCOLLECTION.PROCESS_DATE
          AND CLEARING_CYCLE = INSERTCOLLECTION.CLEARING_CYCLE
          AND BATCH_NO = PRAM_BATCH_NO
          AND UNIQUE_IDENTIFIER = INSERTCOLLECTION.UNIQUE_IDENTIFIER
    );

    -- 处理更新集合
    UPDATE IMG_OWD_OUTWARDCLEARING
    SET ITEM_COUNT = uc.ITEM_COUNT,
        CLEARING_DATE = uc.CLEARING_DATE,
        VALUE_DATE = uc.VALUE_DATE
    FROM UPDATECOLLECTION uc
    WHERE IMG_OWD_OUTWARDCLEARING.PROCESS_DATE = uc.PROCESS_DATE
      AND IMG_OWD_OUTWARDCLEARING.CLEARING_CYCLE = uc.CLEARING_CYCLE
      AND IMG_OWD_OUTWARDCLEARING.BATCH_NO = PRAM_BATCH_NO
      AND IMG_OWD_OUTWARDCLEARING.UNIQUE_IDENTIFIER = uc.UNIQUE_IDENTIFIER;

    -- 处理删除集合
    DELETE FROM IMG_OWD_OUTWARDCLEARING
    USING DELETECOLLECTION dc
    WHERE IMG_OWD_OUTWARDCLEARING.PROCESS_DATE = dc.PROCESS_DATE
      AND IMG_OWD_OUTWARDCLEARING.CLEARING_CYCLE = dc.CLEARING_CYCLE
      AND IMG_OWD_OUTWARDCLEARING.BATCH_NO = PRAM_BATCH_NO
      AND IMG_OWD_OUTWARDCLEARING.UNIQUE_IDENTIFIER = dc.UNIQUE_IDENTIFIER;

    -- 处理图片相关集合（逻辑与上述一致，省略具体代码）
    -- ...

EXCEPTION
    WHEN OTHERS THEN
        STATUS := 1;
        ERR_MSG := SQLERRM;
        ROLLBACK;
END;
$$;

关键注意事项 #

1. 主键约束处理：PostgreSQL无法在复合类型或TABLE参数上直接定义主键，需通过WHERE NOT EXISTS语句在存储过程内部验证唯一性，或直接在目标业务表上创建主键约束。
2. 参数只读性：PostgreSQL的TABLE参数和数组参数默认都是只读的，无需额外声明READONLY。
3. 低版本兼容：如果使用PostgreSQL 10及以下版本，不支持PROCEDURE，需改用FUNCTION，通过返回复合类型传递输出参数。
4. 字段修正：原SQL Server表类型的主键包含BATCH_NO字段，但表类型字段列表中未定义该字段，迁移时需修正字段定义或主键逻辑，确保一致性。

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