Re: rules and default values

Awhile back I said:
> nconway@klamath.dyndns.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