CODEWITHSUNDEEP
functionpostgresqlinsertsql-returning
Dmitry
Dmitry

Reputation: 661

In postgresql return result of a function in a rule when performing INSERT INTO RETURNING

I have a View that has several fields.

When i INSERT INTO a view I run a function based on INSERT parametrs. The function returns a value.

How can I retrieve The value from rule?

INSERT RETURNING Gives me:

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

Example:

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;

Then I get an error described above.

Upvotes: 6

Views: 8130

Answers (2)

Paulo Centeno
Paulo Centeno

Reputation: 49

Here is an example.

First, we create a test table:

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

Then, we create a view:

CREATE OR REPLACE VIEW test_view AS SELECT * FROM test;

Next, the update rule is created:

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;

And finally, here is the insert rule:

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.*;

Now you can insert some test data:

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

and check the tuples inserted:

SELECT * FROM test_view;

Upvotes: 4

Scott Bailey
Scott Bailey

Reputation: 8306

In order to update a view in Postgres, you need to define a rule telling it to update the base table instead. It doesn't sound like you've created a rule yet. http://www.postgresql.org/docs/current/interactive/rules-update.html

Upvotes: -1

Related Questions