SQL Server用户定义表类型在PostgreSQL中的等效实现方案

在PostgreSQL中没有和SQL Server用户定义表类型完全一致的特性，但可以通过以下两种方式实现等效的批量插入逻辑：

方式一：自定义复合类型 + 数组参数 #

这是PostgreSQL处理批量数据参数的传统方案，通过复合类型模拟行结构，再用数组传递批量数据。

1. 定义复合类型 #

对应SQL Server的Purchase_Type，创建匹配结构的复合类型：

CREATE TYPE purchase_type AS (
    month char(9),
    value int,
    buy int,
    sell int
);

注：PostgreSQL复合类型本身不支持NOT NULL约束，但可以通过目标表约束或存储过程内校验实现等效效果。

2. 创建存储过程 #

编写存储过程接收复合类型数组，展开后插入目标表：

CREATE PROCEDURE insert_purchase(purchase_table purchase_type[])
LANGUAGE plpgsql
AS $$
BEGIN
    -- 可选：在参数层面校验非空约束
    FOR rec IN SELECT * FROM unnest(purchase_table) LOOP
        IF rec.month IS NULL OR rec.value IS NULL THEN
            RAISE EXCEPTION 'month 和 value 不允许为空';
        END IF;
    END LOOP;

    INSERT INTO purchase (month, value, buy, sell)
    SELECT * FROM unnest(purchase_table);
END;
$$;

3. 调用示例 #

通过数组传递批量数据：

CALL insert_purchase(
    ARRAY[
        ('2024-01', 100, 50, 30)::purchase_type,
        ('2024-02', 200, 60, 40)::purchase_type
    ]
);

方式二：直接使用TABLE类型参数（PostgreSQL 10+） #

这种方式更贴近SQL Server的使用习惯，直接将表结构作为参数类型：

1. 创建存储过程 #

无需提前定义复合类型，直接指定TABLE类型的输入参数：

CREATE PROCEDURE insert_purchase(purchase_table TABLE(month char(9), value int, buy int, sell int))
LANGUAGE plpgsql
AS $$
BEGIN
    -- 依赖目标表的NOT NULL约束校验，或添加内部校验逻辑
    INSERT INTO purchase (month, value, buy, sell)
    SELECT * FROM purchase_table;
END;
$$;

2. 调用示例 #

通过TABLE(VALUES ...)传递批量数据：

CALL insert_purchase(
    TABLE(
        VALUES
            ('2024-01', 100, 50, 30),
            ('2024-02', 200, 60, 40)
    )
);

约束说明 #

• 如果目标表purchase已经定义了month和value的NOT NULL约束，即使不在存储过程内校验，插入时PostgreSQL也会自动报错，和SQL Server的约束行为一致。
• 若需要在参数接收阶段就校验非空，可在存储过程内添加循环校验逻辑，如方式一中的示例。

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