postgresql - 带有返回子句的 Postgres 插入 View 规则

Question

我试图允许将带有返回子句的语句插入到 Postgres v9.4 中的 View 中，但我在语法上苦苦挣扎。这就是我想要调用插入语句的方式:

CREATE VIEW MyView AS SELECT a.*, b.someCol1
    FROM tableA a JOIN tableB b USING(aPrimaryKey);
INSERT INTO MyView (time, someCol) VALUES (someTime, someValue) RETURNING *;
INSERT INTO MyView (someCol) VALUES (someValue) RETURNING *;

请注意，时间的默认值是 NOW()。这是我目前所拥有的:

CREATE RULE MyRuleName AS ON INSERT TO MyView DO INSTEAD (
    INSERT INTO tableA (time) VALUES COALESCE(NEW.time, NOW());
    INSERT INTO tableB (aPrimaryKey, someCol)
        VALUES (CURRVAL('tableA_aPrimaryKey_seq'), NEW.someValue);
);

上面的方法可以插入值，但我正在努力尝试找出如何添加返回语句。我尝试了以下但没有成功:

CREATE RULE MyRuleName AS ON INSERT TO MyView DO INSTEAD (
    INSERT INTO tableA (time) VALUES COALESCE(NEW.time, NOW())
        RETURNING *, NEW.someValue;
    INSERT INTO tableB (aPrimaryKey, someCol)
        VALUES (CURRVAL('tableA_aPrimaryKey_seq'), NEW.someValue);
);
-- ERROR:  invalid reference to FROM-clause entry for table "new"

CREATE RULE MyRuleName AS ON INSERT TO MyView DO INSTEAD (
    WITH a AS (INSERT INTO tableA (time)
        VALUES COALESCE(NEW.time, NOW()) RETURNING *)
    INSERT INTO tableB (aPrimaryKey, someCol)
        SELECT aPrimaryKey, NEW.someValue FROM a RETURNING *;
);
-- ERROR:  cannot refer to NEW within WITH query

啊!有谁知道添加返回语句的方法，该语句在第一次插入时将主键(SERIAL)和时间(TIMESTAMP WITH TIME ZONE)添加到数据库中，以及在第二次插入中添加 someCol 的值？谢谢!

Answer 1

最好在此处使用 INSTEAD OF INSERT 触发器:

CREATE FUNCTION MyFuncName() RETURNS trigger AS $$
DECLARE
  id integer;
BEGIN
  INSERT INTO tableA (time) VALUES COALESCE(NEW.time, NOW()) RETURNING aPrimaryKey INTO id;
  INSERT INTO tableB (aPrimaryKey, someCol1) VALUES (id, NEW.someValue);
  RETURN NEW;
END; $$ LANGUAGE PLPGSQL;

CREATE TRIGGER MyView_on_insert INSTEAD OF INSERT ON MyView
  FOR EACH ROW EXECUTE PROCEDURE MyFuncName();

检查序列的当前值以查看插入到另一个表中的内容是坏坏坏做法。即使您在这里进行单笔交易，也不要这样做。

你对RETURNING信息的问题感到困惑，因为我在阅读你的问题时也感到困惑。在函数内部使用 INTO 子句来填充局部声明的变量以保存记录值，然后您可以在后续语句中使用这些值。在函数外部，使用 RETURNING 子句，就像您在最顶层代码片段中所做的那样。