From: | Rafal Pietrak <rafal(at)zorro(dot)isa-geek(dot)com> |
---|---|
To: | PostgreSQL general <pgsql-general(at)postgresql(dot)org> |
Subject: | A VIEW mimicing a TABLE |
Date: | 2006-12-13 08:49:04 |
Message-ID: | 1165999744.4326.206.camel@zorro.isa-geek.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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);
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
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2006-12-13 08:53:29 | Re: order by text-type : whitespaces ignored?? |
Previous Message | DANTE Alexandra | 2006-12-13 08:21:35 | PostgreSQL 8.2 : warnings during the built with ICC 9.1.045 |