Re: rule on insert with default values for new instance

While it's kind of a hack, couldn't you simply use the following query in
the rule?

INSERT INTO account (id, name) SELECT last_value, new.name FROM
user_id_seq;


Admittedly, if you're in an environment where multiple inserts are
potentially happening simultaneously, you definitely run the risk of
getting the wrong ID in there (as a second insert may happen before you
query the sequence).

How would this get done with a trigger?  I'm in an environment where the
above hack works for the time being, but in the longer term, I would like
to move away from it.



On Tue, 19 Jun 2001, Tom Lane wrote:

> Janning Vygen <vygen@planwerk6.de> writes:
> > CREATE TABLE user (id SERIAL, name text);
>
> > CREATE RULE startaccount AS ON INSERT
> >     TO user
> >     DO INSERT INTO account (name) VALUES
> >     (new.id, new.name);
>
> > i get problems because it seems to me that new.id is not defined at the
> > moment i do the insert.
>
> The problem here is that NEW is basically a macro, not a variable.
> When you say
>     INSERT INTO user(name) VALUES ('Joe');
> the default expression for id gets inserted:
>     INSERT INTO user(id,name) VALUES (nextval('user_id_seq'), 'Joe');
> and then the rule gets expanded to:
>     INSERT INTO account VALUES (nextval('user_id_seq'), 'Joe');
>
> See the problem?  nextval() gets evaluated twice, so a different ID gets
> inserted into account.
>
> AFAIK you can't work around this with a rule.  You need to use a trigger
> instead.  The trigger is passed the already-formed tuple proposed for
> insertion into "user", so it can extract the correct value to insert
> into "account".
>
> The rule stuff is pretty powerful, but more often than not it's the
> wrong tool when you just want to examine single tuples being
> inserted/updated/deleted.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl
>