Re: tables referenced from insert...returning

"Michael Shulman" <shulman(at)mathcamp(dot)org> writes:
> CREATE RULE _insert AS ON INSERT TO tv DO INSTEAD
> INSERT INTO test (name) VALUES (NEW.name) RETURNING NEW.*;
> ERROR: invalid reference to FROM-clause entry for table "*NEW*"
> LINE 2: INSERT INTO test (name) VALUES (NEW.name) RETURNING NEW.*;
> ^
> HINT: There is an entry for table "*NEW*", but it cannot be
> referenced from this part of the query.

Hmm ... that might be a bug, but in any case, wouldn't it be wiser to do

CREATE RULE _insert AS ON INSERT TO tv DO INSTEAD
INSERT INTO test (name) VALUES (NEW.name) RETURNING test.*;

Multiple evaluations of NEW in the text of a rule are a great way
to cause yourself trouble --- consider what happens if there's
a volatile function such as nextval() involved. It's almost always
safest to base RETURNING expressions on the already-stored data.
In the example at hand, your approach would lie about the stored
value of "id" anyway, since whatever NEW.id might be, it's not
likely to match the sequence-assigned id.

regards, tom lane