Search Postgresql Archives

Re: SELECT * in a view

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Wed, Apr 21, 2010 at 1:59 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
> "Rob Richardson" <Rob.Richardson@xxxxxxxxxxx> 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

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux