current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

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 *;
share|improve this question
    
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
    
Didn't know that - thanks. –  hbn Sep 21 '14 at 20:26

1 Answer 1

up vote 0 down vote accepted

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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