Thread: rule on insert with default values for new instance

Hi,

if i have a table and a rule like this

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 didnt check the syntax, hope you still get the point)

i get problems because it seems to me that new.id is not defined at the
moment i do the insert. the default value seems not to be calculated at this
time.

How can i get around this?

im quite new to sql and mabye there is a misunderstanding of the rule feature.

please give me a hint.
thanks in advance for any help.

janning

does anybody know a good book/website like an SQL cookbook with many many
examples to learn from?

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

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
>