Re: rule on insert with default values for new instance

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