SELECT * in a CREATE VIEW statement doesn't update column set automatically

This isn't exactly a bug, but it could be considered unintuitive
behavior. Consider this:

CREATE VIEW foo AS SELECT * FROM a;
CREATE VIEW foo_v AS SELECT * FROM foo;
ALTER TABLE foo ADD COLUMN b INT;

The ALTER TABLE statement affects VIEW foo, but the column addition
does not propagate to VIEW foo_v. Thus, it makes this deceptive:

... AS SELECT * FROM foo;

I ran into this with an application where a real table is accessed if
the user is an "admin", while regular users access a view instead. I
considered "AS SELECT * FROM foo" to be a promise that all columns
from foo would be included in the view, but the promise is broken when
ADD COLUMN is applied later on.

Would it be a desirable feature to make `CREATE VIEW foo_v AS SELECT *
FROM foo;` automatically update the column set when foo's columns
change? Instead of the wildcard * being expanded once at CREATE VIEW
time, it would (semantically) be expanded every time foo_v is selected
on.

Joseph Adams <joeyadams3(dot)14159(at)gmail(dot)com> writes:
> This isn't exactly a bug, but it could be considered unintuitive
> behavior.

It's required by the SQL standard.

regards, tom lane

On Thu, May 6, 2010 at 3:01 PM, Joseph Adams <joeyadams3(dot)14159(at)gmail(dot)com> wrote:
> This isn't exactly a bug, but it could be considered unintuitive
> behavior.  Consider this:

by unintuitive you mean: 'explicitly defined in the SQL standard' :-).
I happen to agree with you but that's irrelevant. If you absolutely
require this use the composite type workaround.

merlin

Tom Lane wrote:
> Joseph Adams <joeyadams3(dot)14159(at)gmail(dot)com> writes:
>
>> This isn't exactly a bug, but it could be considered unintuitive
>> behavior.
>>
>
> It's required by the SQL standard.
>
>
>

And many places regard "select *" in anything other than throw-away
queries as bad practice anyway. I have seen people get bitten by it over
and over again, and I have worked at companies where it is explicitly
forbidden in coding standards.

cheers

andrew

On Thu, May 6, 2010 at 3:23 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
> And many places regard "select *" in anything other than throw-away queries
> as bad practice anyway. I have seen people get bitten by it over and over
> again, and I have worked at companies where it is explicitly forbidden in
> coding standards.

In terms of application queries I generally agree. However, I think
this rule does not apply to server side definitions, especially in
regards to views and/or composite types. There are cases where you
_want_ the view to be define as 'all fields of x'...In fact, it's
pretty typical IMNSHO. It may be possible to expose this behavior.

I'd like to see:
select * from foo
-- and --
select (foo).*
exhibit different behaviors -- ().* is more a type operator, returning
all the fields of foo, than a field list expression. This gives us a
cool loophole to exploit for views that really want to be defined with
*:
create view particular_foos as select (foo).* from foo where something = true;
create view something_complex as select (foo).*, (func(foo.field)).*;
-- execute func() just one time please!

The something_complex case above is a real problem in how it behaves
currently -- sometimes without a hassle free workaround. Am I off my
rocker? :-) I've made this point many times (prob got annoying a long
time ago) but I'm curious if you guys agree...

merlin

On May 6, 2010, at 4:29 PM, Merlin Moncure wrote:
> On Thu, May 6, 2010 at 3:23 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>> And many places regard "select *" in anything other than throw-away queries
>> as bad practice anyway. I have seen people get bitten by it over and over
>> again, and I have worked at companies where it is explicitly forbidden in
>> coding standards.
>
> In terms of application queries I generally agree. However, I think
> this rule does not apply to server side definitions, especially in
> regards to views and/or composite types. There are cases where you
> _want_ the view to be define as 'all fields of x'...In fact, it's
> pretty typical IMNSHO. It may be possible to expose this behavior.
>
> I'd like to see:
> select * from foo
> -- and --
> select (foo).*
> exhibit different behaviors -- ().* is more a type operator, returning
> all the fields of foo, than a field list expression. This gives us a
> cool loophole to exploit for views that really want to be defined with
> *:
> create view particular_foos as select (foo).* from foo where something = true;
> create view something_complex as select (foo).*, (func(foo.field)).*;
> -- execute func() just one time please!
>
> The something_complex case above is a real problem in how it behaves
> currently -- sometimes without a hassle free workaround. Am I off my
> rocker? :-) I've made this point many times (prob got annoying a long
> time ago) but I'm curious if you guys agree...

What you're suggesting makes sense to me.

What is the composite type workaround you mentioned? This is definitely an issue I face at work and would love a more elegant solution than drop and re-create the view.
--
Jim C. Nasby, Database Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net

On Mon, May 17, 2010 at 2:15 PM, Jim Nasby <decibel(at)decibel(dot)org> wrote:
> On May 6, 2010, at 4:29 PM, Merlin Moncure wrote:
>> On Thu, May 6, 2010 at 3:23 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>>> And many places regard "select *" in anything other than throw-away queries
>>> as bad practice anyway. I have seen people get bitten by it over and over
>>> again, and I have worked at companies where it is explicitly forbidden in
>>> coding standards.
>>
>> In terms of application queries I generally agree.  However, I think
>> this rule does not apply to server side definitions, especially in
>> regards to views and/or composite types.  There are cases where you
>> _want_ the view to be define as 'all fields of x'...In fact, it's
>> pretty typical IMNSHO.  It may be possible to expose this behavior.
>>
>> I'd like to see:
>> select * from foo
>>  -- and --
>> select (foo).*
>> exhibit different behaviors -- ().* is more a type operator, returning
>> all the fields of foo, than a field list expression.  This gives us a
>> cool loophole to exploit for views that really want to be defined with
>> *:
>> create view particular_foos as select (foo).* from foo where something = true;
>> create view something_complex as select (foo).*, (func(foo.field)).*;
>> -- execute func() just one time please!
>>
>> The something_complex case above is a real problem in how it behaves
>> currently -- sometimes without a hassle free workaround.  Am I off my
>> rocker? :-) I've made this point many times (prob got annoying a long
>> time ago) but I'm curious if you guys agree...
>
> What you're suggesting makes sense to me.
>
> What is the composite type workaround you mentioned? This is definitely an issue I face at work and would love a more elegant solution than drop and re-create the view.

Well, the workaround I was specifically talking about was dealing with
the problem of composite type return from functions executing the
function multiple times:

select (func()).*;
This gets expanded to select func().f1, func().f2, etc. This is the
behavior I think has to go.

if func returns foo and foo has 6 columns, func gets executed 6 times
for each row. The workaround is this:

select (q).f.* from (select func() as f) q;

the problem here is that forcing the function call into a subquery can
be awkward in non trival queries -- it causes other problems.

What you are probably looking for is to be able to add columns to a
view without recreating it:
create table foo(...);
create view v as select foo from foo;
now you can just do:
select (foo).* from v;

small disclaimer: I don't actually do this much, it might cause other
issues. postgres is pretty smart about detecting how composite type
changes cascade to other structures. This is an exception!

postgres=# create table foo(a int, b int, c int);
CREATE TABLE
postgres=# create view v as select foo from foo;
CREATE VIEW
postgres=# create view vv as select (v).foo.c;
CREATE VIEW
postgres=# insert into foo select 1,2,3;
INSERT 0 1
postgres=# insert into foo select 2,4,6;
INSERT 0 1
alter table foo drop column c; -- uh oh
ALTER TABLE
postgres=# select * from v; -- this seems ok
foo
-------
(1,2)
(2,4)
postgres=# select * from vv; -- urk!

postgres=# \d+ vv
View "public.vv"
Column | Type | Modifiers | Storage | Description
--------+---------+-----------+---------+-------------
c | integer | | plain |
View definition:
SELECT (v.foo)."........pg.dropped.3........" AS c
FROM v;

I don't actually mind this so much TBH...feature not bug. I hesitated
fixing this because I was terrified someone might actually fix it.

merlin

On Mon, May 17, 2010 at 2:15 PM, Jim Nasby <decibel(at)decibel(dot)org> wrote:
> On May 6, 2010, at 4:29 PM, Merlin Moncure wrote:
>> On Thu, May 6, 2010 at 3:23 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>>> And many places regard "select *" in anything other than throw-away queries
>>> as bad practice anyway. I have seen people get bitten by it over and over
>>> again, and I have worked at companies where it is explicitly forbidden in
>>> coding standards.
>>
>> In terms of application queries I generally agree.  However, I think
>> this rule does not apply to server side definitions, especially in
>> regards to views and/or composite types.  There are cases where you
>> _want_ the view to be define as 'all fields of x'...In fact, it's
>> pretty typical IMNSHO.  It may be possible to expose this behavior.
>>
>> I'd like to see:
>> select * from foo
>>  -- and --
>> select (foo).*
>> exhibit different behaviors -- ().* is more a type operator, returning
>> all the fields of foo, than a field list expression.  This gives us a
>> cool loophole to exploit for views that really want to be defined with
>> *:
>> create view particular_foos as select (foo).* from foo where something = true;
>> create view something_complex as select (foo).*, (func(foo.field)).*;
>> -- execute func() just one time please!
>>
>> The something_complex case above is a real problem in how it behaves
>> currently -- sometimes without a hassle free workaround.  Am I off my
>> rocker? :-) I've made this point many times (prob got annoying a long
>> time ago) but I'm curious if you guys agree...
>
> What you're suggesting makes sense to me.
>
> What is the composite type workaround you mentioned? This is definitely an issue I face at work and would love a more elegant solution than drop and re-create the view.

Well, the workaround I was specifically talking about was dealing with
the problem of composite type return from functions executing the
function multiple times:

select (func()).*;
This gets expanded to select func().f1, func().f2, etc. This is the
behavior I think has to go.

if func returns foo and foo has 6 columns, func gets executed 6 times
for each row. The workaround is this:

select (q).f.* from (select func() as f) q;

the problem here is that forcing the function call into a subquery can
be awkward in non trival queries -- it causes other problems.

What you are probably looking for is to be able to add columns to a
view without recreating it:
create table foo(...);
create view v as select foo from foo;
now you can just do:
select (foo).* from v;

small disclaimer: I don't actually do this much, it might cause other
issues. postgres is pretty smart about detecting how composite type
changes cascade to other structures. This is an exception!

postgres=# create table foo(a int, b int, c int);
CREATE TABLE
postgres=# create view v as select foo from foo;
CREATE VIEW
postgres=# create view vv as select (v).foo.c;
CREATE VIEW
postgres=# insert into foo select 1,2,3;
INSERT 0 1
postgres=# insert into foo select 2,4,6;
INSERT 0 1
alter table foo drop column c; -- uh oh
ALTER TABLE
postgres=# select * from v; -- this seems ok
foo
-------
(1,2)
(2,4)
postgres=# select * from vv; -- urk!

postgres=# \d+ vv
View "public.vv"
Column | Type | Modifiers | Storage | Description
--------+---------+-----------+---------+-------------
c | integer | | plain |
View definition:
SELECT (v.foo)."........pg.dropped.3........" AS c
FROM v;

I don't actually mind this so much TBH...feature not bug. I hesitated
fixing this because I was terrified someone might actually fix it.

merlin