PostgreSQL: Question about rules

From: "Jeremy Smith" <postgres(at)duckwizard(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: PostgreSQL: Question about rules
Date: 2006-11-16 19:34:13
Message-ID: 7353a39e0611161134m508c7a5eg5947d6d3b587d728@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

A question about rules:

I am trying to create a rule which handles inserts on a view, and that
rule should insert rows into multiple tables, each one of which
contains some of the columns that the view contains.

What if one of these tables has, for example, a serial primary key?
If I explicitly deal with that column in one of the insert statements
inside of the rule, then the column will either always either be
explicit, or an error will be thrown if it was not specified in the
insert to the view. If I don't explicitly deal with that column in
one of the insert statements (or if I use nextval() to deal with it),
then it will be impossible to use explicit values for this column when
inserting (which is a bad idea anyway, but this is just an example).

Example:

------------------------ Begin example SQL
create table parent (
id serial primary key,
foo integer,
);

create table child (
id integer references parent(id) on delete cascade,
bar integer
)

create view child_with_parent_explicit as
select parent.id, parent.foo, child.bar from parent join child using(id);

-- this next one is just a copy of the first, to differentiate the two scenarios
create view child_with_parent_implicit as
select parent.id, parent.foo, child.bar from parent join child using(id);

create rule "child_with_parent_explicit_insert" as
on insert to child_with_parent_explicit do instead (
insert into parent(id, foo) values(new.id, new.foo);
insert into child(id, bar) values(new.id, new.bar);
);

create rule "child_with_parent_implicit_insert" as
on insert to child_with_parent_implicit do instead (
insert into parent(id, foo) values(nextval('parent_id_seq'), new.foo);
insert into child(id, bar) values(currval('parent_id_seq'), new.bar);
);

-- OK
insert into child_with_parent_implicit(foo, bar) values(1, 2);
insert into child_with_parent_explicit(id, foo, bar) values(42, 3, 4);

-- Bad - explicit id is ignored
insert into child_with_parent_implicit(id, foo, bar) values(42, 3, 4);

-- Bad - error occurs as explicit null is attempting to be inserted
into not null column
insert into child_with_parent_explicit(foo, bar) values(1, 2);

------------------------------------ End example SQL

Now, I realize I could apply both an implicit and explicit rule to the
same view by qualifying the rules with a ' where id is null ' and a '
where id is not null '. However, what if I have other not null
columns or columns with default values? Must I create a qualified
rule for each possible combination of columns?

Is there any way to do an insert inside a rule, and somehow specify
that a column in NEW may or may not be specified, and if it's not then
don't specify it in the insert?

Wow, that was pretty long. I hope it was clear enough. Thanks for
any advice you can give me - I am just now starting to get into the
more advanced SQL stuff and I apologize if my question is dumb. FWIW,
I am trying to use this method to implement a Class-Table Inheritance
scheme.

Thanks,
Jeremy

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Eric Rousse 2006-11-16 19:34:41 Re: Strange Postgresql crash
Previous Message Eric Rousse 2006-11-16 19:28:59 Re: Strange Postgresql crash