postgresql - PLPGSQL 函数返回触发器和值

Question

我已经编写了一个返回触发器的 PL/PGSQL 函数，因此我可以在每行插入之前调用它。我现在意识到我还希望该函数返回新插入行的 ID。我不太确定如何继续，因为我的函数必须返回触发器。这是一些代码:

CREATE OR REPLACE FUNCTION f_insert_album() RETURNS TRIGGER AS $$
DECLARE
    subj_album_id INTEGER;
BEGIN
    -- ... some parts where left out
    INSERT INTO t_albums_subjective (user_id, album_id, format_id, location_id, rating) 
    VALUES (NEW.user_id, obj_album_id, NEW.format_id, NEW.location_id, NEW.rating) 
    RETURNING id INTO subj_album_id;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Bind insert function to trigger
DROP TRIGGER IF EXISTS tr_v_albums_insert ON v_albums; 
CREATE TRIGGER tr_v_albums_insert INSTEAD OF INSERT ON v_albums
    FOR EACH ROW EXECUTE PROCEDURE f_insert_album();

我必须将函数 f_insert_album() 的返回类型保持为 TRIGGER，但我真的很想在 subj_album_id 中返回值>，对应新插入行的id。

有没有办法做到这一点？有可能吗？显然更改返回类型不适用于 Postgres。如果有的话，你能推荐一种替代方法吗？

Answer 1

关键问题:将 ID 返回到哪里？

假设您想直接从 INSERT 语句返回它，那么您就快完成了。您已经将新生成的 ID 分配给函数参数:

...
RETURNING id INTO subj_album_id;

相反，将其分配给触发触发器的行的列。 special variable NEW将这一行保存在触发器函数中:

...
RETURNING id INTO NEW.album_id;  -- use actual column name in view

然后使用 INSERT 语句的 RETURNING 子句:

INSERT INTO v_albums (user_id, format_id, location_id, rating) 
VALUES ( ... ) 
RETURNING album_id;

显然，这只有在 View 中 可见列时才有可能。不过，它不必在 INSERT 命令中指定。 NEW 行变量的类型由 View 的定义定义，而不是由手头的 INSERT 定义。

密切相关:

• RETURNING data from updatable view not working?

---

对于您似乎正在做的事情(向特定角色授予对表的特定行的访问权限)，Postgres 9.5 或更高版本中的行级安全性 (RLS) 可能是一个更方便的选择:

• CREATE POLICY in the manual
• The Postgres Wiki: "What's new in PostgreSQL 9.5"
• Review by Depesz