Re: rule as on insert to view with multiple fk referencing the same table

* Joan Picanyol <lists-pgsql(at)biaix(dot)org> wrote:

<snip>
> create table a (id serial not null primary key, data int);
> create table b (id serial not null primary key, data int\
> ref1 int references a not null, ref2 int references a not null,\
> ref3 int references a);
> create view vw (data_a, data_b1, data_b2, data_b3) as select \
> (b.data as data_b, a1.data as data_a1, a2.data as data_a2,\
> a3.data as data_a3) from b, a a1, a a2, a a3 where b.ref1=a1.id\
> and b.ref2=a2.id and b.ref3=a3.id;
>
> And now I want to create an insert rule into vw with all my foreign keys
> properly set. I can't think of anything better that doing first the
> three inserts into a and then use nextval()-1, nextval()-2 etc for the
> fk values, but it looks really ugly (even though I could wrap up within
> a BEGIN(...)COMMIT).

You want the same ID in both tables ?
This won't work, since nextval() isn't predictable in the way you want.
(even if you *could* put it into an own transaction, it wont help)
You probably could use nextval() only the first time and then currval().

If you've got your ID in your view, its probably easier (and more safe ?)
to declare this ID field as default nextval(...) and just catch up this
value in your rules.

BTW: if you have created an ON SELECT DO INSTEAD rule, postgres
treats the table as a view, and it wants you have all writing rules
to be also INSTEAD. (bacause writing to a shadow table is useless ?).

cu
--
---------------------------------------------------------------------
Enrico Weigelt == metux IT service
phone: +49 36207 519931 www: http://www.metux.de/
fax: +49 36207 519932 email: contact(at)metux(dot)de
---------------------------------------------------------------------
Realtime Forex/Stock Exchange trading powered by postgresSQL :))
http://www.fxignal.net/
---------------------------------------------------------------------