PostgreSQL：向接收表类型数组的函数传递行集的正确方式

背景 #

现有items表定义如下：

CREATE TABLE items(
   id SERIAL PRIMARY KEY,
   group_id INT NOT NULL,
   item_id INT NOT NULL,
   name TEXT,
   .....
   .....
);

需要创建update_items函数，满足以下需求：

• 接收单个group_id对应的行集，若输入行包含多个group_id则报错；
• 与表中同group_id的记录进行对比；
• 更新同group_id下有变化的记录；
• 插入新记录；
• 删除表中同group_id下未出现在输入行中的记录（通过group_id和item_id匹配）。

函数初始定义如下：

CREATE OR REPLACE FUNCTION update_items(rows_input items[]) RETURNS boolean as $$
DECLARE
  rows items[];
  group_id_input integer;
BEGIN
  -- get single group_id from input rows, fail if multiple group_id's present in input
  -- read items of that group_id in table
  -- compare input rows and table rows (of the same group_id)
  -- create transaction
    -- delete absent rows
    -- upsert
  -- return success of transaction (boolean)
END;
$$ LANGUAGE plpgsql;

报错情况 #

调用函数时使用如下语句：

select update_items(
(38,1,1283,"Name1"),
(39,1,1471,"Name2"),
(40,1,1333,"Name3")
);

出现错误：Failed to run sql query: column "Name1" does not exist，即使移除id列值仍报相同错误。

疑问 #

向接收表类型数组的函数传递行值的正确方式是什么？

1. 行值传递的正确格式 #

报错核心是字符串用了双引号——PostgreSQL中双引号会被解析为标识符（比如列名），所以"Name1"会被当成列名导致报错，必须改用单引号包裹字符串。

同时，向表类型数组传递行数据有两种正确格式：

方式一：用ROW()构造器+数组语法

select update_items(
    ARRAY[
        ROW(38, 1, 1283, 'Name1')::items,
        ROW(39, 1, 1471, 'Name2')::items,
        ROW(40, 1, 1333, 'Name3')::items
    ]
);

• 先用ROW()构造行数据，再强制转换为items类型，最后用ARRAY[]包裹成数组。

方式二：直接使用表类型数组字面量

select update_items(
    '{
        "(38,1,1283,''Name1'')",
        "(39,1,1471,''Name2'')",
        "(40,1,1333,''Name3'')"
    }'::items[]
);

• 字符串中的单引号需要转义（用两个单引号），整体包裹成数组字面量后强制转换为items[]类型。

2. 函数逻辑完善参考 #

如果需要完成update_items的完整功能，这里给出核心实现代码：

CREATE OR REPLACE FUNCTION update_items(rows_input items[]) RETURNS boolean as $$
DECLARE
    group_id_input integer;
    input_group_ids integer[];
BEGIN
    -- 提取输入行的所有group_id，检查是否唯一
    input_group_ids := ARRAY(SELECT group_id FROM unnest(rows_input));
    IF array_length(array_remove(input_group_ids, input_group_ids[1]), 1) > 0 THEN
        RAISE EXCEPTION '输入行包含多个不同的group_id';
    END IF;
    group_id_input := input_group_ids[1];

    BEGIN
        -- 删除表中该group_id下未出现在输入中的记录
        DELETE FROM items
        WHERE group_id = group_id_input
          AND (group_id, item_id) NOT IN (SELECT group_id, item_id FROM unnest(rows_input));

        -- 批量插入/更新记录（UPSERT）
        INSERT INTO items (group_id, item_id, name)
        SELECT group_id, item_id, name FROM unnest(rows_input)
        ON CONFLICT (group_id, item_id) DO UPDATE
        SET name = EXCLUDED.name; -- 可扩展其他需要更新的字段

        RETURN TRUE;
    EXCEPTION
        WHEN OTHERS THEN
            RETURN FALSE;
    END;
END;
$$ LANGUAGE plpgsql;

• 用unnest()将数组拆分为行数据，方便后续操作；
• 先校验输入的group_id是否唯一，不符合则抛出异常；
• 先清理不在输入中的旧记录，再用ON CONFLICT实现UPSERT（插入新记录、更新已有变更的记录）；
• 通过异常捕获返回操作结果的布尔值。

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