From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Rob Richardson <Rob(dot)Richardson(at)rad-con(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: SELECT * in a view |
Date: | 2010-04-21 18:04:56 |
Message-ID: | x2lb42b73151004211104qcdfcd800n70f9dd05c5b88372@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, Apr 21, 2010 at 1:59 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Rob Richardson" <Rob(dot)Richardson(at)rad-con(dot)com> writes:
>> The old view and the new view should have the same fields. So, I tried
>> something like this:
>
>> CREATE VIEW new_view AS
>> SELECT * FROM old_view
>> WHERE complete = 1;
>
>> The query was accepted and the new_view was created.
>
>> But when I went back into PGAdmin and looked at the definition of
>> new_view, I saw something like this:
>
>> CREATE VIEW new_view AS
>> SELECT old_view.field1, old_view.field2, <snip> FROM old_view
>> WHERE complete = 1;
>
>> That's not what I want. That means that if old_view changes, new_view
>> will not reflect the changes. Is there any way to get new_view to
>> automatically include all fields from old_view, no matter how many or
>> how few fields there are?
>
> No. This behavior is specifically required by the SQL standard: the
> result rowtype of a view is determined when the view is created, and
> is not supposed to change when underlying tables have columns added.
> That's why we expand * when the view is created.
You can skirt this problem w/composite types:
create view v as select 1 as a, 2 as b, 3 as c;
CREATE VIEW
create view vv as select v from v;
CREATE VIEW
select * from vv;
v
---------
(1,2,3)
(1 row)
create or replace view v as select 1 as a, 2 as b, 3 as c, 4 as d;
CREATE VIEW
select (v).* from vv;
a | b | c | d
---+---+---+---
1 | 2 | 3 | 4
(1 row)
btw tom: does the sql standard define that in the case of:
create view select (foo).* from foo;
so that this restriction could be possibly relaxed for cases involving
composite types depending if you do select * vs select (something).*?
merlin
From | Date | Subject | |
---|---|---|---|
Next Message | Andreas Kretschmer | 2010-04-21 18:14:37 | Re: SELECT * in a view |
Previous Message | Tom Lane | 2010-04-21 17:59:38 | Re: SELECT * in a view |