In postgresql, returns the result of a function in a rule when executing INSERT INTO RETURNING

Question

I have a view that has several fields.

When I have an INSERT INTO view, I run a function based on the INSERT parametrs. The function returns a value.

How can I get the value from the rule?

INSERT RETURNING Gives me:

ERROR: Cannot perform INSERT RETURNING with respect to "full_subntes"
TIP: You need an unconditional IN 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 the error described above.

Answer 1

Here is an example.

First, 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, an 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 inserted tuples:

 SELECT * FROM test_view; 

Answer 2

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