>>On Wed, Jun 15, 2011 at 6:08 PM, David Johnston <polobo@yahoo.com> wrote:
>> The main reason to avoid doing so is to allow for a view to output all
the columns of the underlying tables. If I drop/create the view after
altering the underlying tables the new view will have the additional columns
without >>any direct change to the view being required.
>>David J.
>Even with CREATE VIEW foo AS SELECT * FROM bar you do not get this
behavior. Try that, then use \dv foo and you'll see that it has expanded
the set of columns at CREATE VIEW time. It will not get any new columns you
add to >the underlying table.
> (tested on 8.4)
>So this is a deeper issue than just being able to exclude certain tables.
>Derrick
First: I intentionally said (DROP/CREATE the VIEW) because of this fact -
and that does not bother me that much; but I'd rather not have to change the
view definition in addition to dropping and recreating it.
What I am basically requesting is that the rewriter that handles evaluation
of " * " within a Select List be able to be told that specific columns, by
name, are not to be included in the resulting expansion. In the case where
the name would be ambiguous neither/none of the fields would be output -
though if you prefix the column with the table you could just exclude that
specific column. This would help when you want to use:
SELECT *
FROM t1
JOIN t2 ON (t1.id = t2.id)
And you do not want a duplicate (and auto-named) id column. You can just
do:
SELECT *->NOT(t2.id)
FROM t1
JOIN t2 ON (t1.id = t2.id)
And only the id field from column 1 will display
David J.