Re: tables referenced from insert...returning

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Michael Shulman" <shulman(at)mathcamp(dot)org>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: tables referenced from insert...returning
Date: 2008-06-24 01:46:58
Message-ID: 6670.1214272018@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

"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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Michael Shulman 2008-06-24 02:42:40 Re: tables referenced from insert...returning
Previous Message Michael Shulman 2008-06-24 01:19:06 tables referenced from insert...returning