Re: PostgreSQL: Question about rules

On 11/16/06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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).