Thread: A VIEW mimicing a TABLE

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

Rafal Pietrak <rafal@zorro.isa-geek.com> writes:
> 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.

You can add a default to a view's column, either the same as the
underlying table's default, or different if you want.

ALTER TABLE view ALTER COLUMN col SET DEFAULT expr

            regards, tom lane

On Wed, 2006-12-13 at 11:08 -0500, Tom Lane wrote:
>
> You can add a default to a view's column, either the same as the
> underlying table's default, or different if you want.
>
> ALTER TABLE view ALTER COLUMN col SET DEFAULT expr

G! The obvious solutions are most difficult to spot.

Thenx!

--
-R

Tom Lane wrote:
> Rafal Pietrak <rafal@zorro.isa-geek.com> writes:
>> 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.
>
> You can add a default to a view's column, either the same as the
> underlying table's default, or different if you want.
>
> ALTER TABLE view ALTER COLUMN col SET DEFAULT expr

After blinking my eyes a few times, I tested this and indeed: You can
use ALTER TABLE on a view. Amazing...

/me makes a note to check the archives why there is no ALTER VIEW command.

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

On Thu, 2006-12-14 at 08:01 -0200, William Leite Araújo wrote:
>
> On 12/13/06, Rafal Pietrak <rafal@zorro.isa-geek.com> wrote:
>         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.

Yes, this one is less overtalkative, but does not solve the problem of
having the default value used ONLY when INSERT *does*not* set the field;
as opposed to the case, when INSERT *sets* the field, but sets it to
NULL.

The above solution would set new.id to [default] in case of: "INSERT
(id) VALUES (null)". Which is not desired.

But in fact, "ALTER TABLE <view_name> ALTER ... SET DEFAULT", suggested
earlier in this thread by Tom Lane does the trick :) Thenx Tom.


-R

> 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]));

what would [default] insert here?
the default of the view or the default of the underlying table?

B.

On Thu, 2006-12-14 at 02:45 -0800, SunWuKung wrote:
> > 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]));
>
> what would [default] insert here?
> the default of the view or the default of the underlying table?

I admit, I haven't tested that myself yet.

But as I understand 'the theory', it would be the default of a VIEW.

This is because we don't have any means to access the [default] of an
underlaying table (nothing like 'foreign-default-reference' :), so we
define a [default] for a VIEW as an entirely separate entity.

This might be a problem for sequences, but in that case, we may put
*the*same* sequence for a VIEW [default] and for an underalaying TABLE
[default], so we don't actually need 'foreign-default-reference'
'construct' here. Other requirements for 'common [default]' might prove
more difficult to implement, but for me this is quite sufficient.

--
-R