On 12/13/06, Rafal Pietrak <[EMAIL PROTECTED]> wrote:
Hi,
May be someone could help me with this:
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
