Re: SERIAL type in RULES

This gets asked alot. It's not precisely right, but
it's also not precisely wrong. The issue is that the
rule sees the thing that's being inserted, not it's
value (in this case the implicit nextval('seq')) and
so it gets called twice. The issue is that in this
case that's not what you want, but there could be rule
cases in which you would expect the side effects to
happen twice and there's no easy way to differentiate
these cases.

For now, I'd suggest using a plpgsql trigger on insert
to do the other insert.

Stephan Szabo
sszabo(at)bigpanda(dot)com

On Fri, 8 Sep 2000, Alex Guryanow wrote:

> HI,
>
> I use postgresql-7.0.2. One of my tables contains a field of type SERIAL:
>
> CREATE TABLE test2 (id serial, word varchar(100));
>
> another table contains similar fields:
>
> CREATE TABLE test3 (test2_id int4, wod varchar(100));
>
> I want to create a rule, that copies into test3 all what user inserts into test2:
>
> CREATE RULE rule_insert_test2 AS ON INSERT TO test2
> DO INSERT INTO test3 (test2_id, word) VALUES (new.id, new.word);
>
> But when I insert into test2 I receive strange results. Here is a dump:
>
> test_db=# insert into test2 (word) values ('alex');
> INSERT 12706507 1
> test_db=# select * from test3;
> test2_id | word
> ----------+------
> 1 | alex
> (1 row)
>
> test_db=# select * from test2;
> id | word
> ----+------
> 2 | alex
> (1 row)
>
> As you can see test3.test2_id = 1 while test2.id = 2. It seems that the rule receives the correct
> value of new.id but after that the sequence for test2 is changed once more. Why this happens? Is
> this a bug?
>
> Regards,
> Alex
>
>