Re: SELECT * in a view

"Rob Richardson" <Rob.Richardson@rad-con.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.

            regards, tom lane