Re: { SELECT *->NOT(column1, column2) FROM table } syntax idea

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(at)edgewave(dot)com]
Sent: Wednesday, June 15, 2011 7:15 PM
To: David Johnston; pgsql-general(at)postgresql(dot)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(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of David Johnston
Sent: Wednesday, June 15, 2011 3:08 PM
To: pgsql-general(at)postgresql(dot)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.