Re: A VIEW mimicing a TABLE

On 12/13/06, Rafal Pietrak <rafal(at)zorro(dot)isa-geek(dot)com> 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