Обсуждение: { SELECT *->NOT(column1, column2) FROM table } syntax idea
Is there, or has there ever been, a discussion about introducing syntax to handle specifying which columns you do NOT want to output in the SELECT list?
The use case I am running into is mostly within VIEWS. I want to specify “SELECT * FROM table” but there are a couple of fields that I do NOT want to output (for instance a password hash field for a user table). I guess it would probably be better form to move those columns to a separate enhanced permissions table but since PostgreSQL allows for per-column permissions that is not strictly necessary. Listing all the wanted columns is not desirable though obviously possible. 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.
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
>>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.
Wouldn’t it be easy enough to write a little program to suck in the column names from the information schema, and output the CREATE VIEW statement, excluding all the columns you want to exclude?
Then, if the tables have changed, just run the program, let it fetch the information from the information schema, do the DROP and CREATE, and you’re done.
Susan C.
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of David Johnston
Sent: Wednesday, June 15, 2011 3:08 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] { SELECT *->NOT(column1, column2) FROM table } syntax idea
Is there, or has there ever been, a discussion about introducing syntax to handle specifying which columns you do NOT want to output in the SELECT list?
The use case I am running into is mostly within VIEWS. I want to specify “SELECT * FROM table” but there are a couple of fields that I do NOT want to output (for instance a password hash field for a user table). I guess it would probably be better form to move those columns to a separate enhanced permissions table but since PostgreSQL allows for per-column permissions that is not strictly necessary. Listing all the wanted columns is not desirable though obviously possible. 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.
In this particular use-case that is doable; but the general idea of wanting to exclude specific columns from an output occurs in different areas including adhoc queries.
I’m not saying this feature is going to compete with sliced bread for coolness but more than once I’ve desired this ability – both adhoc and now as I am building more infrastructure around my database creation. SQL is a declarative language and the idea of “Include everything except X, Y, Z” is not a new concept. Having to resort to a procedural language to implement what could be a simple declarative syntax construct seems overkill.
I wouldn’t even care if you silently ignore an attempt to specify a non-present column for exclusion...if I see a column that I didn’t want explicitly the first (and pretty much only) thing to check will be spelling.
I’m just curious if anyone else has considered this and/or whether the implementers have difficulty concerns that make inhibit implementation or whether it is more the fact that the demand is too low to spend the effort.
David J.
From: Susan Cassidy [mailto:scassidy@edgewave.com]
Sent: Wednesday, June 15, 2011 7:15 PM
To: David Johnston; pgsql-general@postgresql.org
Subject: RE: [GENERAL] { SELECT *->NOT(column1, column2) FROM table } syntax idea
Wouldn’t it be easy enough to write a little program to suck in the column names from the information schema, and output the CREATE VIEW statement, excluding all the columns you want to exclude?
Then, if the tables have changed, just run the program, let it fetch the information from the information schema, do the DROP and CREATE, and you’re done.
Susan C.
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of David Johnston
Sent: Wednesday, June 15, 2011 3:08 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] { SELECT *->NOT(column1, column2) FROM table } syntax idea
Is there, or has there ever been, a discussion about introducing syntax to handle specifying which columns you do NOT want to output in the SELECT list?
The use case I am running into is mostly within VIEWS. I want to specify “SELECT * FROM table” but there are a couple of fields that I do NOT want to output (for instance a password hash field for a user table). I guess it would probably be better form to move those columns to a separate enhanced permissions table but since PostgreSQL allows for per-column permissions that is not strictly necessary. Listing all the wanted columns is not desirable though obviously possible. 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.