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(at)planwerk6(dot)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
>