A VIEW mimicing a TABLE

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

Responses

Browse pgsql-general by date

  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