Le 7 mai 08 à 07:52, Tom Lane a écrit :
> Dimitri Fontaine <dim@hi-media.com> writes:
>> Could we consider ALTER VIEW ALTER COLUMN ... SET DEFAULT ...;?
>
> We could if we hadn't already done it five or so years ago.
> Or am I missing what you need here?
My 8.3.1 installation psql \h only gives me:
Syntax:
ALTER VIEW name RENAME TO newname
Ok, I've been quite bad at explaining the case, let's retry.
CREATE TABLE t(id serial, name text not null, a_count not null default
0, option);
CREATE VIEW v AS SELECT * FROM t;
Now we add a on INSERT RULE such as INSERT INTO v will insert to t.
And we want the RULE to be able to use the a_count DEFAULT when user
didn't give a_count column in its INSERT order: INSERT INTO v(name) VALUES ('foo');
The RULE kicks in and rewrite the INSERT to target t, and a_count not
being given explicitely any value will get rewritten to NULL, not to
DEFAULT, and the INSERT subsequently fails.
In order to be able to avoid this, AFAIK, you need to poke into
catalogs to retrieve the DEFAULT value, at the RULE definition level...
>> Bonus question: why is the rewriter unable to distinguish whether
>> NULL
>> comes from the user or comes from the column was not part of the user
>> query?
>
> Not following this either ...
...and the RULE definition has no chance to figure out by itself if
the user omit to give any value for our not null column, or
explicitely gave a NULL, in which case we'd better not rewrite it to
DEFAULT...
Hope I made the case clear by now, thx for your interest,
--
dim