Thread: PostgreSQL: Question about rules

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

On Thu, 2006-11-16 at 11:34 -0800, Jeremy Smith wrote:
> 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_explicit_insert" as
        on insert to child_with_parent_explicit do instead (
                insert into parent(id, foo) values(COALESCE
(new.id,NEXTVAL('parent_id_seq')), new.foo);
                insert into child(id, bar) values(COALESCE
(new.id,CURRVAL('parent_id_seq')), new.bar);
        );


I'm not sure if this is what you're looking for, but does this help?

Regards,
    Jeff Davis

On 11/16/06, Jeff Davis <pgsql@j-davis.com> wrote:
COALESCE is certainly something I had never heard of.  I think that will solve my problem quite effectively.  Thanks for your help!

Jeremy

On 11/16/06, Jeremy Smith <postgres@duckwizard.com> wrote:
>  On 11/16/06, Jeff Davis <pgsql@j-davis.com> wrote:
>
>
> >  create rule "child_with_parent_explicit_insert" as
> >         on insert to child_with_parent_explicit do instead (
> >                 insert into parent(id, foo) values(COALESCE
> > ( new.id ,NEXTVAL('parent_id_seq')), new.foo);
> >                 insert into child(id, bar) values(COALESCE
> > (new.id,CURRVAL('parent_id_seq')),  new.bar);
> >         );
> >
> >
> > I'm not sure if this is what you're looking for, but does this help?
> >
> > Regards,
> >         Jeff Davis
> >
> >
>
>

One more thing that would sweeten the deal even further! Not so much
for sequences, but for other columns with default values:

insert into foo(bar) values(COALESCE(new.bar, DEFAULT))

This doesn't work, because DEFAULT is a language construct that is
only defined within the immediate scope of the values(...) list.  Is
there any way I can use COALESCE to defer to the table for the default
value, rather than explicitly specifying it?

I could probably fake this by writing a function to look up the
default value in pg_attrdef and evaluate it - just want to see if
there is a built-in function for this (I can't find one).  Because it
would be a lot of work :-)

Thanks,
Jeremy

On Thu, 2006-11-16 at 16:58 -0800, Jeremy Smith wrote:

>
> One more thing that would sweeten the deal even further! Not so much
> for sequences, but for other columns with default values:
>
> insert into foo(bar) values(COALESCE(new.bar, DEFAULT))
>
> This doesn't work, because DEFAULT is a language construct that is
> only defined within the immediate scope of the values(...) list.  Is
> there any way I can use COALESCE to defer to the table for the default
> value, rather than explicitly specifying it?
>
> I could probably fake this by writing a function to look up the
> default value in pg_attrdef and evaluate it - just want to see if
> there is a built-in function for this (I can't find one).  Because it
> would be a lot of work :-)
>

Interesting question. It seems like you're trying to make a default
value for a view that's based on the underlying table's default value.

I think the normal way to do this is to _not_ have a default value on
the underlying table, and instead use the rule to define the default
value. Does that work for you?

Regards,
    Jeff Davis

Jeff Davis <pgsql@j-davis.com> writes:
> Interesting question. It seems like you're trying to make a default
> value for a view that's based on the underlying table's default value.

> I think the normal way to do this is to _not_ have a default value on
> the underlying table, and instead use the rule to define the default
> value. Does that work for you?

Actually, the best way to do that is to attach a default to the view
itself.

    CREATE VIEW v AS SELECT ... ;
    CREATE RULE ... for insert on v ... ;
    ALTER TABLE v ALTER COLUMN c DEFAULT whatever;

In this formulation the rule is not responsible for substituting any
default values, it just does what it's told.  This is better than the
COALESCE approach because the latter does the wrong thing if one is
explicitly inserting NULL.

I'm not sure this answers the OP's problem though, as it sounded like he
wanted a default dependent on other inserted values, which is something
you can't do with either a table or a view.

            regards, tom lane

On 11/16/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Actually, the best way to do that is to attach a default to the view
> itself.
>
>         CREATE VIEW v AS SELECT ... ;
>         CREATE RULE ... for insert on v ... ;
>         ALTER TABLE v ALTER COLUMN c DEFAULT whatever;
>
> In this formulation the rule is not responsible for substituting any
> default values, it just does what it's told.  This is better than the
> COALESCE approach because the latter does the wrong thing if one is
> explicitly inserting NULL.

Hey - this is great information.  I'm now very close to having a
general and robust solution to my problem.  One problem remains with
using this approach: sequences.

alter table my_view alter column set default nextval('my_table_id_seq');

create rule my_view_ins as on insert to my_view do instead(
       insert into my_table(id, foo) values(new.id, new.foo);
       insert into my_child_table(id, foo) values(new.id, new.bar);
);

-- Works
insert into my_view(id, foo, bar) values (42, 'a foo', 'a bar');

-- Error - key (id)=(3) is not present in table "my_table"
insert into my_view(foo, bar) values('another foo', 'another bar');


Now, the default value for new.id gets evaluated *each time* I
reference new.id - meaning the rule's first insert sees N for new.id
while the rule's second insert sees N+1.  That is kind of odd - I
would think that the default value would get evaluated and then
assigned to new.id (since it is a concrete row), but that appears not
to be the case.

My stopgap solution to this is to call a PSQL/PL function for the body
of the rule, which can store new.id in a variable so it is evaluated
only once.  If there is a way to do this inside the rule without
resorting to creating a view, 3 rules and 3 functions for every child
table, I would love to hear about it!

Thanks for all the tips, Jeff and Tom.

Jeremy (Reposted this to the list; I accidentally replied only to Tom
the first time).

On Fri, 2006-11-17 at 11:49 -0800, Jeremy Smith wrote:

> Now, the default value for new.id gets evaluated *each time* I
> reference new.id - meaning the rule's first insert sees N for new.id
> while the rule's second insert sees N+1.  That is kind of odd - I
> would think that the default value would get evaluated and then
> assigned to new.id (since it is a concrete row), but that appears not
> to be the case.
>
> My stopgap solution to this is to call a PSQL/PL function for the body
> of the rule, which can store new.id in a variable so it is evaluated
> only once.  If there is a way to do this inside the rule without
> resorting to creating a view, 3 rules and 3 functions for every child
> table, I would love to hear about it!
>

I think you're operating on the edge of what rules are supposed to do. A
rule won't copy values or create variables, which is why you needed
COALESCE.

Why do you need so many functions though? Won't one SQL function do the
trick?

Regards,
    Jeff Davis

On 11/17/06, Jeff Davis <pgsql@j-davis.com> wrote:
>
> Why do you need so many functions though? Won't one SQL function do the
> trick?
>
> Regards,
>         Jeff Davis
>
>

You're right, one will do the trick - I was thinking update and
delete, but of course those will be fine with plain rules because they
don't need to do anything with default values.

Even so, I guess I have to use a trigger+function instead of a real
rule, and have an INSTEAD DO NOTHING rule for insert so that the
trigger gets called without error.  Seems like a bit of a kludge; I
think I may just give up and do all this stuff in application logic
instead of database logic.

I learned a helluva lot about PG though!  Thanks!

Jeremy

On Fri, 2006-11-17 at 16:42 -0800, Jeremy Smith wrote:
> On 11/17/06, Jeff Davis <pgsql@j-davis.com> wrote:
> >
> > Why do you need so many functions though? Won't one SQL function do the
> > trick?
> >
> > Regards,
> >         Jeff Davis
> >
> >
>
> You're right, one will do the trick - I was thinking update and
> delete, but of course those will be fine with plain rules because they
> don't need to do anything with default values.
>
> Even so, I guess I have to use a trigger+function instead of a real
> rule, and have an INSTEAD DO NOTHING rule for insert so that the
> trigger gets called without error.  Seems like a bit of a kludge; I
> think I may just give up and do all this stuff in application logic
> instead of database logic.
>

For a trigger to work the columns have to match up. You'd have to create
some kind of empty table that you can insert everything you want into,
and have a BEFORE INSERT trigger to take in all the values, pass them
off to child tables, and then return NULL.

That's functionally not much different from having a rule rewrite the
insert into a function call. If you decide to implement it in the
database I think that's the cleanest way. Why do you think a rule that
makes a function call won't work?

Regards,
    Jeff Davis