如何将存储过程的parent_id参数传递给触发器？

可以实现，不过触发器无法直接接收存储过程的参数，需要借助中间载体来传递parent_id，下面提供两种常用可行的方案：

方案一：给comment表新增辅助字段暂存parent_id #

这种方法通过在comment表添加一个临时字段传递父评论ID，触发器读取该字段完成comment_tree的插入。

1. 先修改comment表，添加可选的parent_id字段：

ALTER TABLE comment ADD COLUMN parent_id integer REFERENCES comment(comment_id);

2. 创建触发器函数：

CREATE OR REPLACE FUNCTION insert_comment_tree()
RETURNS TRIGGER AS $$
BEGIN
    -- 当parent_id不为空时，插入评论树关联记录
    IF NEW.parent_id IS NOT NULL THEN
        INSERT INTO comment_tree (ancestor, descendant)
        VALUES (NEW.parent_id, NEW.comment_id);
        -- 可选：插入后清空该字段，避免影响后续查询（按需选择）
        -- UPDATE comment SET parent_id = NULL WHERE comment_id = NEW.comment_id;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

3. 创建AFTER INSERT触发器：

CREATE TRIGGER trigger_after_insert_comment
AFTER INSERT ON comment
FOR EACH ROW
EXECUTE FUNCTION insert_comment_tree();

4. 修改存储过程，插入评论时携带parent_id：

CREATE OR REPLACE PROCEDURE add_comment(content varchar, parent_id integer) AS $$
BEGIN
    INSERT INTO comment (content, parent_id) VALUES (content, parent_id);
END;
$$ LANGUAGE plpgsql;

方案二：使用PostgreSQL会话变量传递parent_id #

这种方法不需要修改comment表结构，通过会话级变量传递父评论ID，触发器读取变量完成插入。

1. 创建触发器函数：

CREATE OR REPLACE FUNCTION insert_comment_tree()
RETURNS TRIGGER AS $$
DECLARE
    parent_id integer;
BEGIN
    -- 读取会话变量中的父评论ID，第二个参数true表示变量不存在时返回NULL
    SELECT current_setting('comment.parent_id', true) INTO parent_id;
    IF parent_id IS NOT NULL THEN
        INSERT INTO comment_tree (ancestor, descendant)
        VALUES (parent_id::integer, NEW.comment_id);
        -- 清除会话变量，避免影响后续插入操作
        RESET comment.parent_id;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

2. 创建AFTER INSERT触发器：

CREATE TRIGGER trigger_after_insert_comment
AFTER INSERT ON comment
FOR EACH ROW
EXECUTE FUNCTION insert_comment_tree();

3. 修改存储过程，设置会话变量：

CREATE OR REPLACE PROCEDURE add_comment(content varchar, parent_id integer) AS $$
BEGIN
    -- 设置会话级变量，仅当前事务有效
    SET LOCAL comment.parent_id = parent_id::text;
    INSERT INTO comment (content) VALUES (content);
END;
$$ LANGUAGE plpgsql;

两种方案对比 #

• 方案一：逻辑直观，字段语义明确，但需要修改comment表结构，适合允许调整表结构的场景。
• 方案二：无需修改表结构，但依赖会话变量，需要注意及时清理变量，避免后续插入操作受到残留值影响。

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