postgresql - plpgsql:在触发器函数中扩展新行

Question

我有一个表“消息”。对于每个 INSERT'ed 行，我必须在同一个表“消息”(用于群发邮件)中插入该行的多个副本。我为它创建了一个触发器(例如一个副本)。

CREATE OR REPLACE FUNCTION some_trigger()
  RETURNS trigger AS
$BODY$
DECLARE
BEGIN
 INSERT INTO messages (some_field) VALUES (NEW.some_value_copy)
 RETURN NEW;
END
$BODY$
  LANGUAGE plpgsql;
CREATE TRIGGER some_tg BEFORE INSERT ON messages FOR EACH ROW EXECUTE PROCEDURE some_trigger(); 

我如何将 NEW 扩展到INSERT INTO messages (some_field) VALUES (NEW.some_value),(NEW.some_value_copy) ?谢谢

Answer 1

仍然不知道你的架构，所以很难提供帮助，但我认为你可以做一些接近于此的事情:

CREATE OR REPLACE FUNCTION ins_messages()
  RETURNS trigger AS
$BODY$
DECLARE
BEGIN
 if NEW.usr is not null then
    return NEW; -- making actual insert
 end if;
 if NEW.sender <> '-1' then
    insert into messages (message, usr, sender, receiver)
    values
     (NEW.message, NEW.sender, NEW.sender, NEW.receiver),
     (NEW.message, NEW.receiver, NEW.sender, NEW.receiver);
 else
    insert into messages (message, usr, sender, receiver)
    select NEW.message, name, NEW.sender, name
    from users;
 end if;
 return null;
END;
$BODY$
LANGUAGE plpgsql;

=> sql fiddle demo