On 11/16/06, Tom Lane <tgl@xxxxxxxxxxxxx> 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).
