Re: Views and default values

Michael Ansley <Michael(dot)Ansley(at)intec-telecom-systems(dot)com> writes:
> I think that a coalesce might work here, something like:

> create rule v_foo_ins as on insert to v_foo do instead
> insert into foo (explication, silly) values (
> NEW.explication,
> COALESCE(NEW.silly, 'DEFAULT_VALUE'));

> Obviously you replace the DEFAULT_VALUE with whatever you want your default
> to be.

This isn't really a good solution, because it prevents explicit
insertion of a NULL (which isn't something that DaVinci cares about
in the particular example, but it would be annoying in related cases).
Also, you might not want your rule to have to know just what default
value the table has.

There is a better answer: INSERT's default-value-addition machinery
operates upstream of the rule rewriter, so it could work for a view.
Unfortunately the system won't accept
ALTER TABLE v_foo ALTER COLUMN silly SET DEFAULT 't'
but I see no real good reason why it shouldn't. DaVinci could either
remove the restriction against doing this (quick code hack) or insert
the appropriate pg_attrdef entry by hand (ugly...).

Meanwhile, if Bruce is paying attention, please add a TODO list item
to allow column defaults to be set on views. (Please note pg_dump
would need to be fixed to know it needs to dump these, too.)

regards, tom lane