PostgreSQL函数中验证自定义类型输入并批量插入的问题

先确认基础对象定义 #

首先确保你的表和自定义类型正确创建（如果尚未完成）：

-- 创建holiday表，添加date+region的唯一约束
CREATE TABLE IF NOT EXISTS holiday (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    date DATE NOT NULL,
    region VARCHAR(50) NOT NULL,
    UNIQUE(date, region)
);

-- 创建自定义表类型t_holiday
CREATE TYPE IF NOT EXISTS t_holiday AS (
    name VARCHAR(100),
    date DATE,
    region VARCHAR(50)
);

推荐实现：批量校验+插入函数 #

这个函数会先检查输入数组内部的重复，再校验与数据库已有数据的重复，最后批量插入，一次性抛出所有重复问题：

CREATE OR REPLACE FUNCTION upsert_holidays(p_holidays t_holiday[])
RETURNS VARCHAR AS $$
DECLARE
    input_dups TEXT[];
    existing_dups TEXT[];
BEGIN
    -- 检查输入自身是否有重复的(date, region)组合
    SELECT array_agg((date, region)::TEXT)
    INTO input_dups
    FROM UNNEST(p_holidays) h
    GROUP BY h.date, h.region
    HAVING COUNT(*) > 1;

    IF input_dups IS NOT NULL THEN
        RAISE EXCEPTION '输入存在重复记录：%', input_dups;
    END IF;

    -- 检查输入是否和数据库现有记录重复
    SELECT array_agg((h.date, h.region)::TEXT)
    INTO existing_dups
    FROM UNNEST(p_holidays) h
    JOIN holiday ON h.date = holiday.date AND h.region = holiday.region;

    IF existing_dups IS NOT NULL THEN
        RAISE EXCEPTION '数据库已存在重复记录：%', existing_dups;
    END IF;

    -- 批量插入合法数据
    INSERT INTO holiday (name, date, region)
    SELECT name, date, region
    FROM UNNEST(p_holidays);

    RETURN '成功插入' || (SELECT COUNT(*) FROM UNNEST(p_holidays)) || '条记录';
END;
$$ LANGUAGE plpgsql;

函数调用示例 #

-- 传入合法的t_holiday数组
SELECT upsert_holidays(
    ARRAY[
        ('元旦', '2024-01-01', 'CN')::t_holiday,
        ('春节', '2024-02-10', 'CN')::t_holiday
    ]
);

你之前遇到的错误原因 #

1. MERGE语句报错：PostgreSQL 15版本才正式支持MERGE语法，且语法和SQL Server差异很大。如果使用低于15的版本，或者MERGE的USING子句未正确指定数据源（比如别名错误、CTE定义问题），就会报对象不存在的错误。
2. UNNEST数组格式错误：你大概率是在传递参数时未正确构造t_holiday数组，或者在UNNEST时错误处理了单个字段（比如直接把date字段当数组用）。正确的数组构造必须用ARRAY[(字段值)::t_holiday, ...]的格式，确保每个元素都是t_holiday类型。

可选：PostgreSQL 15+ MERGE实现（逐行校验） #

如果一定要用MERGE（适合PostgreSQL 15及以上版本），可以这样写，但它只会抛出第一个遇到的重复，无法批量返回所有重复：

CREATE OR REPLACE FUNCTION merge_holidays(p_holidays t_holiday[])
RETURNS VARCHAR AS $$
DECLARE
    insert_count INT;
BEGIN
    MERGE INTO holiday h
    USING UNNEST(p_holidays) input
    ON h.date = input.date AND h.region = input.region
    WHEN MATCHED THEN
        RAISE EXCEPTION '重复记录：日期%，地区%', input.date, input.region
    WHEN NOT MATCHED THEN
        INSERT (name, date, region) VALUES (input.name, input.date, input.region);

    GET DIAGNOSTICS insert_count = ROW_COUNT;
    RETURN '成功插入' || insert_count || '条记录';
END;
$$ LANGUAGE plpgsql;

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