A VIEW mimicing a TABLE

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 12/13/06, Rafal Pietrak <rafal@zorro.isa-geek.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

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

14 Dec 2006 02:45:12 -0800, SunWuKung <Balazs.Klein@t-online.hu>:

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?

A constant, a function, anything you want. I have a function to create
rules of insert an update on views that have the same columns that the
tables that its represent. In this case, my [default] is the default value
for the column of the table.
COALESCE function only choose the second argument when the first is
null. An alias to "IF $1 IS NULL THEN $2 ELSE $1".

the default of the view or the default of the underlying table?

B.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

--
William Leite Araújo
Analista de Banco de Dados - QualiConsult