function - 在执行 INSERT INTO RETURNING 时，在 postgresql 中返回规则中函数的结果

Question

我有一个包含多个字段的 View 。

当我INSERT INTO 一个 View 时，我运行一个基于INSERT 参数的函数。该函数返回一个值。

如何从规则中检索值？

INSERT RETURNING 给我:

ERROR: cannot perform INSERT RETURNING on relation "full_subntes"
HINT: You need an unconditional ON INSERT DO INSTEAD rule with a RETURNING clause.

例子:

CREATE TABLE test (
    a VARCAHR primary key,
    b VARCHAR,
);

CREATE VIEW test_v AS SELECT * FROM test;

CREATE OR REPLACE RULE Test_v_Insert AS ON INSERT TO Test_v
DO INSTEAD (
    SELECT myFunction('param');
);

INSERT INTO test_v(a, b) VALUES ('a', 'b') RETURNING a, b;

然后我收到上述错误。

Answer 1

这是一个例子。

首先，我们创建一个测试表:

CREATE TABLE test (a integer, b varchar, primary key (a));

然后，我们创建一个 View :

CREATE OR REPLACE VIEW test_view AS SELECT * FROM test;

接下来，创建更新规则:

CREATE OR REPLACE RULE rl_test_view_update AS
    ON UPDATE TO test_view DO INSTEAD
    UPDATE test SET a = NEW.a, b = NEW.b
    WHERE test.a = old.a AND test.b = old.b;

最后，这是插入规则:

CREATE OR REPLACE RULE rl_test_view_insert AS
    ON INSERT TO test_view DO INSTEAD
    INSERT INTO test VALUES (NEW.a, NEW.b)
    RETURNING test.*;

现在你可以插入一些测试数据了:

INSERT INTO test_view (a, b) VALUES (1,'John Doe') RETURNING a;

并检查插入的元组:

SELECT * FROM test_view;