PostgreSQL: Question about rules

Поиск
Список
Период
Сортировка
От Jeremy Smith
Тема PostgreSQL: Question about rules
Дата
Msg-id 7353a39e0611161134m508c7a5eg5947d6d3b587d728@mail.gmail.com
обсуждение исходный текст
Ответы Re: PostgreSQL: Question about rules
Список 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

В списке pgsql-general по дате отправления:

Предыдущее
От: Eric Rousse
Дата:
Сообщение: Re: Strange Postgresql crash
Следующее
От: "beer"
Дата:
Сообщение: Accessing postgres in perl app using ssl authentication