1

I am failing to create a rule which will return the insert's data.

The table is:

CREATE TABLE foo(a int, b text);

My attempt for the rule is:

CREATE RULE return_data AS ON INSERT TO foo DO RETURN *;
2
  • Is there any particular reason you want to use a rule for this? You can just use the RETURNING clause to return all values for the row (e.g. INSERT INTO foo VALUES (1, 'test') RETURNING *;)
    – hbn
    Sep 21 '14 at 19:39
  • 1
    There is another rule and once there is a rule you can't use the returning clause.
    – Bloodcount
    Sep 21 '14 at 20:24
1

RETURN isn't a valid command in a rule definition; you can only use SELECT, INSERT, UPDATE, DELETE, and NOTIFY.

You can create an ALSO rule to return the value of the new row, something like this:

> CREATE RULE return_data AS ON INSERT TO FOO DO ALSO SELECT NEW.*;
CREATE RULE

> INSERT INTO foo VALUES (1, 'test');
 a |  b
---+------
 1 | test
(1 row)

INSERT 0 1

Note that you can do something identical by just adding the RETURNING clause to the INSERT statement:

> INSERT INTO foo VALUES (2, 'test 123') RETURNING *;
 a |    b
---+----------
 2 | test 123
(1 row)


INSERT 0 1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.