如何编写带INOUT参数的PostgreSQL存储过程插入child表数据

以下是满足需求的存储过程实现，包含参数处理、数据校验和异常捕获：

CREATE OR REPLACE PROCEDURE insert_child(
    p_c_id CHAR(4),
    p_c_name CHAR(8),
    p_p_id CHAR(4),
    INOUT p_err_msg TEXT DEFAULT ''
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- 初始化错误信息
    p_err_msg := '';

    -- 校验p_id是否存在于parent表（适配原表外键未启用的场景）
    IF NOT EXISTS (SELECT 1 FROM parent WHERE p_id = p_p_id) THEN
        p_err_msg := '错误：指定的p_id不存在于parent表中';
        RETURN;
    END IF;

    -- 执行插入操作
    INSERT INTO child(c_id, c_name, p_id)
    VALUES (p_c_id, p_c_name, p_p_id);

EXCEPTION
    -- 捕获主键重复异常
    WHEN unique_violation THEN
        p_err_msg := '错误：c_id已存在，主键重复';
    -- 捕获其他未知异常
    WHEN OTHERS THEN
        p_err_msg := '未知错误：' || SQLERRM;
END;
$$;

关键说明： #

• 参数设计：前三个参数对应child表的c_id、c_name、p_id字段，第四个p_err_msg为INOUT类型，用于返回错误信息，默认值为空字符串。
• 前置校验：手动检查p_id是否存在于parent表，即使原表外键约束被注释，也能提前反馈明确的错误。
• 异常处理：捕获主键重复（unique_violation）和其他所有异常，将错误信息写入INOUT参数。

调用示例： #

-- 正常插入（成功后p_err_msg为空）
CALL insert_child('8', 'Zoe', '200', '');

-- 主键重复场景（返回错误信息）
CALL insert_child('1', 'Test', '100', '');

-- p_id不存在场景（返回错误信息）
CALL insert_child('9', 'Mike', '999', '');

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