Postgres Pro
Downloads
Home   >   mailing lists

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

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

From
Joan Picanyol
Date:
Hi,

I have a schema which simplifies to the following:

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).

What's the 'canonical' way to do this?

tks
--
pica

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

From
Joan Picanyol
Date:
After some experimenting and FAQ/docs re-reading...

* Joan Picanyol <lists-pgsql@biaix.org> [20040316 22:27]:
> 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).

I found out that I can't

create rule my_rule as on insert to vw instead do (begin; ...; commit;);

Together with FAQ 4.15.3 this makes me think that simultaneous inserts
are not an issue, however FAQ 4.15.4 makes me doubt again. In Chapter 34
it is said that

"The rule system is located between the parser and the planner."

however, this doesn't mean much to me. Can I assume that an insert to a
view is atomic regardless how many inserts it get rewritten onto?

tks
--
pica

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

From
Enrico Weigelt
Date:
* Joan Picanyol <lists-pgsql@biaix.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@metux.de
---------------------------------------------------------------------
  Realtime Forex/Stock Exchange trading powered by postgresSQL :))
                                            http://www.fxignal.net/
---------------------------------------------------------------------