编写带doc_id参数的PL/pgSQL序列生成函数时遇报错求助

错误原因分析 #

1. [42601] 错误：PL/pgSQL中执行INSERT ... RETURNING时，必须通过INTO子句指定接收返回值的变量，直接写RETURNING _result = sequence属于无效语法，导致查询结果没有接收目标。
2. [42703] 错误：在VALUES子句中引用sequence列是非法的——新插入的行尚未存在，无法引用该列的值。新条目应固定插入1，仅在冲突（条目已存在）时对现有行的sequence执行自增操作。

正确函数实现 #

确保_generated_sequences表的id列已创建唯一约束（这是ON CONFLICT生效的前提），然后使用以下函数：

CREATE OR REPLACE FUNCTION create_sequence_number(doc_id TEXT, OUT _result BIGINT) 
RETURNS BIGINT AS
$$
BEGIN
    INSERT INTO _generated_sequences AS t (id, sequence)
    VALUES (CONCAT('document_sequence_', doc_id), 1)
    ON CONFLICT (id) DO UPDATE
        SET sequence = t.sequence + 1
    RETURNING sequence INTO _result;
END;
$$ LANGUAGE plpgsql;

关键修正点 #

• 使用RETURNING sequence INTO _result将操作后的sequence值赋值给输出变量，解决无结果目标的报错。
• 新条目插入时固定sequence=1，仅在冲突时对现有行的sequence自增，符合需求逻辑。
• 利用表别名t引用现有行的sequence值，确保自增操作正确指向目标行。

验证逻辑 #

调用函数时：

• 传入未存在的doc_id：自动插入id=document_sequence_xxx、sequence=1的行，返回1。
• 传入已存在的doc_id：将对应行的sequence自增1，返回更新后的数值。

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