For some time now, I exercise the use of VIEWs to expose just the features of TABLES a particular user is supposed to see/have.
I can see that with a VIEW, I can do prity mutch everything I can do with a TABLE, so a VIEW mimics a TABLE quite well.... but one feature: a default value for a row on INSERT.
Here is the case. I have:
CREATE TABLE logfile (id serial, tm timestamp default current_timestamp, info text);
When I: INSERT INTO logfile (info) VALUES ('hello');
I get ID and TM fields filled up for me by postgres. But when I: INSERT INTO logfile (id,tm,info) VALUES (NULL, NULL, 'hello'); I have 'overridden' the defaults with NULL values - sometimes this is desirable.
Now, I cannot really figure out any way to do that with a VIEW:
CREATE VIEW logview AS SELECT * FROM logfile; CREATE RULE new_entry AS ON INSERT to logview DO INSTEAD INSERT (id,tm,info) VALUES (new.id,new.tm,new.info); CREATE RULE new_entry_noid AS ON INSERT to logview WHERE new.id IS NULL DO INSTEAD INSERT (tm,info) VALUES (new.tm,new.info); CREATE RULE new_entry_notm AS ON INSERT to logview WHERE new.tm IS NULL DO INSTEAD INSERT (id,info) VALUES (new.id,new.info); CREATE RULE new_entry_notm AS ON INSERT to logview WHERE new.tm IS NULL AND new.id IS NULL DO INSTEAD INSERT (info) VALUES (new.info);
All can be done with:
CREATE RULE new_entry AS ON INSERT to logview DO INSTEAD INSERT (id,tm,info) VALUES (COALESCE(new.id,[default]),COALESCE(new.tm,[default]),COALESCE(new.info,[default]) );
Which is overtalkative, but sort of works.
"Sort of", because " new.tm IS NULL" is not actually "new.tm was not provided". When it *was*provided*, but its value was NULL, the VIEW behaves differently then the TABLE.
Is there a way, to make such VIEW behave *exactly* as the TABLE does? -- -R
---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
-- William Leite Araújo Analista de Banco de Dados - QualiConsult
