Re: rules and default values

Awhile back I said:
> nconway(at)klamath(dot)dyndns(dot)org (Neil Conway) writes:
>> In other words, when the insert statement on the view is transformed by
>> the rule, the "default value" columns are replaced by explicit NULL
>> values (which is the default value for the columns of the pseudo-table
>> created by CREATE VIEW). Is this the correct behavior?

> It's correct, from the point of view of the rule rewriter, but that
> doesn't make the behavior useful.

> What'd make sense to me is to allow defaults to be attached to the
> view columns, say by doing ALTER TABLE ADD DEFAULT on the view.
> Unfortunately that won't do much in the current implementation,
> because such defaults will never get applied (the planner certainly
> won't see them as applicable).

> Maybe inserting defaults should be the first phase of rewriting, just
> before rule substitution, rather than being left to the planner as it
> is now. We took it out of the parser for good reasons, but perhaps
> we moved it too far downstream.

I recently moved the default-insertion phase to fix a different bug,
so this is now possible. Given the attached patch, it actually works.
However I have not applied the patch because it needs (a) pg_dump
support and (b) documentation, neither of which I have time for at the
moment. Anyone want to pick up the ball?

regards, tom lane

Demonstration of defaults for views (with patch):

regression=# create table foo (f1 int);
CREATE
regression=# create view vv as select * from foo;
CREATE
regression=# create rule vvi as on insert to vv do instead
regression-# insert into foo select new.*;
CREATE
regression=# insert into vv default values;
INSERT 0 0
regression=# select * from vv;
f1
----

(1 row)

regression=# alter table vv alter column f1 set default 42;
ALTER
regression=# insert into vv default values;
INSERT 0 0
regression=# select * from vv;
f1
----

42
(2 rows)

*** src/backend/commands/tablecmds.c~ Mon Apr 15 01:22:03 2002
--- src/backend/commands/tablecmds.c Mon Apr 15 14:16:58 2002
***************
*** 622,629 ****

rel = heap_open(myrelid, AccessExclusiveLock);

! if (rel->rd_rel->relkind != RELKIND_RELATION)
! elog(ERROR, "ALTER TABLE: relation \"%s\" is not a table",
RelationGetRelationName(rel));

if (!allowSystemTableMods
--- 622,635 ----

rel = heap_open(myrelid, AccessExclusiveLock);

! /*
! * We allow defaults on views so that INSERT into a view can have
! * default-ish behavior. This works because the rewriter substitutes
! * default values into INSERTs before it expands rules.
! */
! if (rel->rd_rel->relkind != RELKIND_RELATION &&
! rel->rd_rel->relkind != RELKIND_VIEW)
! elog(ERROR, "ALTER TABLE: relation \"%s\" is not a table or view",
RelationGetRelationName(rel));

if (!allowSystemTableMods