Postgres插入带有返回子句的视图规则 [英] Postgres Insert Into View Rule with Returning Clause

问题描述

我试图在Postgres v9.4中允许带有returning子句的insert语句进入视图，但是在语法上苦苦挣扎。这就是我要调用插入语句的方式：

I am attempting to allow insert statements with a returning clause into a view in Postgres v9.4, but am struggling with the syntax. This is how I want to call the insert statement:

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（）。到目前为止，这是我所拥有的：

Note that the default for time is NOW(). This is what I have so far:

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);
);

上面的方法可以插入值，但是我很努力地尝试找出如何添加返回声明。我已经尝试了以下操作，但没有成功：

The above works to insert the value, but I am struggling to try and figure out how to add the returning statement. I have tried the following without success:

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

Argh中引用NEW！有谁知道添加返回语句的方法，该语句将主键（SERIAL）和时间（TIMESTAMP WITH TIME ZONE）添加到第一次插入中的数据库中，并将someCol的值添加到第二次插入中？谢谢！

Argh! Does anyone know of a way to add a returning statement that gets the primary key (SERIAL) and time (TIMESTAMP WITH TIME ZONE) added to the database in the first insert, along with the value of someCol in the second insert? Thanks!

推荐答案

使用 INSTEAD OF INSERT ，您的情况要好得多在这里触发：

You are much better off using an INSTEAD OF INSERT trigger here:

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();

检查序列的当前值以查看在另一个表中插入了什么不好错误的做法。即使在这里进行单笔交易，也不要这样做。

Checking the current value of a sequence to see what was inserted in another table is bad bad bad practice. Even while you are here in a single transaction, don't do it.

您对返回信息，因为在阅读您的问题时我也感到困惑。在函数内部，使用 INTO 子句填充本地声明的变量以保存记录值，然后可在后续语句中使用该记录值。在函数之外，像在最上面的代码片段中一样，使用 RETURNING 子句。

You are confused about the issue of RETURNING information, because I am confused too when I read your question. Inside of a function use the INTO clause to populate locally declared variables to hold record values which you can then use in subsequent statements. Outside of a function, use the RETURNING clause as you do in your top-most code snippet.

这篇关于Postgres插入带有返回子句的视图规则的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！