Awhile back I said:
> [EMAIL PROTECTED] (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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
