Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-11-02 Thread Valentine Gogichashvili
On Sun, Oct 30, 2011 at 22:12, Eric Ridge eeb...@gmail.com wrote:


 Yes.  It's basically a modifier to the star that immediately precedes
 it.  In order to support excluding multiple columns, it needs parens:
 SELECT foo.* EXCLUDING (foo.x, foo.y), bar.* EXCLUDING (bar.y),
 baz.z, (a+b) AS c

 but yes, that's what I'm thinking.  I think doing this will require
 more changes to the grammar than I had first thought because there'd
 be no point in supporting:
 SELECT foo.* EXCLUDING (foo.* EXCLUDING foo.y) FROM ...
 It looks like the above would be implicitly allowed without a bit of extra
 work.

 But, if you've got a complex query consisting of a few joins, it'd be
 nice to say:
 SELECT * EXCLUDING (table1.*, table2.x) FROM table1 INNER JOIN table2
 ...


Putting aside arguments like it is not a good idea to use * because it
generates not sustainable code especially in case when you extend table
structure,  I think this construct would be really nice for building ROWs,
for example in plpgsql triggers or in conditions for big update statements:

IF (NEW.* EXCLUDING ( last_modified ) ) IS DISTINCT FROM (OLD.* EXCLUDING (
last_modified ) ) THEN NEW.last_modified = clock_timestamp() ; END IF

by now, I do not know any really nice syntax to do that efficiently, and
for some wide tables, building this kind of structures listing all the
fields, that you have there is completely stupid and makes code unreadable.

So I would definitely like to have a syntax, that you are suggesting in
case it would give a possibility to construct ROWs (RECORDs).

Regards,

-- Valentine Gogichashvili


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-11-02 Thread Stephen Frost
* Pavel Stehule (pavel.steh...@gmail.com) wrote:
 I understand it, it is really bad idea use a star in export queries

It's not how I'd want to automate things, but I hardly see it being a
really bad idea for ad-hoc COPY usage..

Stephen


signature.asc
Description: Digital signature


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-11-02 Thread Andrew Dunstan



On 11/02/2011 03:16 AM, Valentine Gogichashvili wrote:


Putting aside arguments like it is not a good idea to use * because 
it generates not sustainable code especially in case when you extend 
table structure,  I think this construct would be really nice for 
building ROWs, for example in plpgsql triggers or in conditions for 
big update statements:


IF (NEW.* EXCLUDING ( last_modified ) ) IS DISTINCT FROM (OLD.* 
EXCLUDING ( last_modified ) ) THEN NEW.last_modified = 
clock_timestamp() ; END IF





That's a very good use case. I could certainly have used this in the past.

cheers

andrew

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-11-02 Thread Tom Lane
Andrew Dunstan and...@dunslane.net writes:
 On 11/02/2011 03:16 AM, Valentine Gogichashvili wrote:
 Putting aside arguments like it is not a good idea to use * because 
 it generates not sustainable code especially in case when you extend 
 table structure,  I think this construct would be really nice for 
 building ROWs, for example in plpgsql triggers or in conditions for 
 big update statements:
 
 IF (NEW.* EXCLUDING ( last_modified ) ) IS DISTINCT FROM (OLD.* 
 EXCLUDING ( last_modified ) ) THEN NEW.last_modified = 
 clock_timestamp() ; END IF

 That's a very good use case. I could certainly have used this in the past.

Well ... this is inventing use cases that have nothing to do with the
proposed feature and are entirely incapable of being supported by the
proposed implementation.  And I'm not sure why we should put aside the
argument that this is only a good idea in ad-hoc queries, either.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-11-02 Thread Andrew Dunstan



On 11/02/2011 09:56 AM, Tom Lane wrote:

Andrew Dunstanand...@dunslane.net  writes:

On 11/02/2011 03:16 AM, Valentine Gogichashvili wrote:

Putting aside arguments like it is not a good idea to use * because
it generates not sustainable code especially in case when you extend
table structure,  I think this construct would be really nice for
building ROWs, for example in plpgsql triggers or in conditions for
big update statements:

IF (NEW.* EXCLUDING ( last_modified ) ) IS DISTINCT FROM (OLD.*
EXCLUDING ( last_modified ) ) THEN NEW.last_modified =
clock_timestamp() ; END IF

That's a very good use case. I could certainly have used this in the past.

Well ... this is inventing use cases that have nothing to do with the
proposed feature and are entirely incapable of being supported by the
proposed implementation.  And I'm not sure why we should put aside the
argument that this is only a good idea in ad-hoc queries, either.




Well, yes, you're right that it's not covered by the original feature. I 
guess I got interested because a couple of years ago I had to write some 
triggers in PLPerl and with much lower efficiency to achieve the same 
effect as this.


cheers

andrew

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-11-02 Thread Eric Ridge
On Tue, Nov 1, 2011 at 11:47 PM, Tom Lane t...@sss.pgh.pa.us wrote:

 Doesn't mean anything, I think --- the SQL standard seems to exclude it.
 It's fairly hard to prevent it at the grammar level, since we regard
 foo.* as a type of primitive expression, but I suspect it might be a
 good idea for transformTargetList to throw an error instead of silently
 ignoring the column label.

Let me take a stab at fixing it in the grammar... if it's even do-able.

If it can be fixed at the grammar, it'll get me a lot closer to doing
what I want to do with the actual feature.

eric

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-11-01 Thread Marti Raudsepp
On Mon, Oct 31, 2011 at 12:54, Marcin Mańk marcin.m...@gmail.com wrote:
 How about an option for psql to truncate too long columns to X characters ?

I would really want this in some form or another; for example, being
able to hide bytea values entirely, or set limits to how many
characters are displayed for fields.

Unfortunately it's far less efficient. Fields would be truncated in
psql, so full values are still detoasted and transmitted over the
network.

Regards,
Marti

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-11-01 Thread Brendan Jurd
On 1 November 2011 00:14, Andrew Dunstan and...@dunslane.net wrote:
 On 10/30/2011 10:00 PM, Christopher Browne wrote:
 I don't think I wish it.  We're telling our developers not to use select
 *, and I don't think having select * except  would change that policy,
 beyond requiring us to waste time explaining :

 No, we're not changing policy.  The fact that PGDG added this to 9.2 does
 *not* imply our policy was wrong.


 That's fine, and it's a good policy. A good policy might well exclude use of
 a number of available features (e.g. one place I know bans doing joins with
 ',' instead of explicit join operators). But I don't think it helps us
 decide what to support.

 The fact is that if you have 100 columns and want 95 of them, it's very
 tedious to have to specify them all, especially for ad hoc queries where the
 house SQL standards really don't matter that much.

I couldn't agree more with Andrew's comment.  What's good for an ad
hoc psql query isn't congruent with what's good for your application
queries.

We could have  * EXCLUDING  and still say that it is undesirable in
all the same contexts that  *  is undesirable.

Cheers,
BJ

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-11-01 Thread Merlin Moncure
On Sat, Oct 29, 2011 at 5:26 PM, Eric Ridge eeb...@gmail.com wrote:
 Would y'all accept a patch that extended the SELECT * syntax to let
 you list fields to exclude from the A_Star?

 Quite regularly I'll be testing queries via psql and want to see all
 the columns from a fairly wide table except maybe a giant text or xml
 column.  A syntax like:

     SELECT * EXCLUDING (big_col1, big_col2) FROM foo;

 would be pretty handy.  It would definitely save some typing in
 certain cases.  It seems like such a syntax would better document the
 intent of a query too, rather than leaving one wondering if big_col1
 was supposed to be omitted from the target list or not.

 Anyways, I just wanted to run the idea by youse guys before I put too
 much more effort into it.  I've already made what appear to be the
 minimum necessary changes to gram.y, and a few quick greps through the
 code make me think the rest will be pretty easy.

 Maybe the SQL spec says something about this and nobody's done the work yet?

 Thanks for your input!

FWIW, this seems to come up all the time for me and I've often
wondered about something like this.  Just be advised that the bar for
syntax extensions is very high because they can burn you down the line
quite easily.

merlin

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-11-01 Thread Pavel Stehule
2011/11/1 Eric Ridge eeb...@gmail.com:
 On Tue, Nov 1, 2011 at 12:24 PM, Pavel Stehule pavel.steh...@gmail.com 
 wrote:
 some other idea - but only for psql

 we can define a special values, that ensure a some necessary
 preexecution alchemy with entered query

 \pset star_exclude_names col1, col2, col3
 \pset star_exclude_types xml, bytea, text(unlimited)


 Sure, something like that could be useful too.  It might be confusing
 to users if they forget that they set an exclusion list, but there's
 probably ways to work around that.

 However, the nice thing about the feature being in SQL is that you can
 use it from all clients, and even in other useful ways.  COPY would be
 an example (something I also do frequently):

 COPY (SELECT * EXCLUDING (a, b, c) FROM big query) TO 'somefile.csv' WITH 
 CSV;

 Right now, if you want to exclude a column, you have to list all the
 others out manually, or just dump everything and deal with it in an
 external tool.

 I generally agree with everyone that says using this in application
 code is a bad idea, but I don't think that's reason alone to reject
 the idea on its face.

 eric


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-11-01 Thread Pavel Stehule
2011/11/1 Eric Ridge eeb...@gmail.com:
 On Tue, Nov 1, 2011 at 12:24 PM, Pavel Stehule pavel.steh...@gmail.com 
 wrote:
 some other idea - but only for psql

 we can define a special values, that ensure a some necessary
 preexecution alchemy with entered query

 \pset star_exclude_names col1, col2, col3
 \pset star_exclude_types xml, bytea, text(unlimited)


 Sure, something like that could be useful too.  It might be confusing
 to users if they forget that they set an exclusion list, but there's
 probably ways to work around that.

 However, the nice thing about the feature being in SQL is that you can
 use it from all clients, and even in other useful ways.  COPY would be
 an example (something I also do frequently):

 COPY (SELECT * EXCLUDING (a, b, c) FROM big query) TO 'somefile.csv' WITH 
 CSV;

 Right now, if you want to exclude a column, you have to list all the
 others out manually, or just dump everything and deal with it in an
 external tool.


sorry, I don't accept it. I am able to understand your request for
adhoc queries. But not for COPY.

and if you need it - you can write C function.

 I generally agree with everyone that says using this in application
 code is a bad idea, but I don't think that's reason alone to reject
 the idea on its face.

I can accept a PostgreSQL extensions if there are no other way how do
it effective. But it is not this case.


 eric


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-11-01 Thread Eric Ridge
On Tue, Nov 1, 2011 at 12:03 PM, Stephen Frost sfr...@snowman.net wrote:
  Note- I haven't looked at the * production or tried to do anything w/ gram.y 
 to
 support this yet, but it's a heck of a lot shorter..

My original thought, that I probably didn't explain too clearly, was
to make the EXCLUDING (...) bit a modifier to the A_Star node.  The
idea being that you could write * EXCLUDING (...) anywhere you can
currently write *.

It's dead simple for the case of:
 SELECT * FROM ...
but because of the indirection productions in gram.y, it's literally
impossible for:
 SELECT tablename.* FROM ...
without possibly breaking existing queries.

Nonetheless, even if it were only available for the first form, it
would be very useful.  For the ad-hoc type stuff I do, it'd still be
great to write:
SELECT * EXCLUDING (x.a, x.b, x.c) FROM (SELECT  x);

I've already got gram.y working the way it needs to, and I've started
to get the exclusion list passed into the places it needs to go.

If y'all would be willing to accept it in this limited form, I'll
continue to work on it.

eric

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-11-01 Thread Ross J. Reedstrom
On Mon, Oct 31, 2011 at 09:14:48AM -0400, Andrew Dunstan wrote:
 The fact is that if you have 100 columns and want 95 of them, it's
 very tedious to have to specify them all, especially for ad hoc
 queries where the house SQL standards really don't matter that much.
 It's made more tedious by the fact that there is no real help in
 constructing the query. This gets particularly bad with views, which
 developers often seem to stuff with every available column that
 might be needed by some query instead of creating views tailored to
 particular queries. Not long ago annoyance with this prompted my to
 write a little utility function that would give me a query with all
 the columns specified  so I could cut and paste it, and delete the
 columns I didn't want. (Another advantage is that the result is
 guaranteed typo free, which my typing certainly is not.) See
 https://gist.github.com/818490. It's far from perfect, but I still
 find myself using it several times a month, mainly for the very
 purpose intended by this suggested feature.
 

As I do the ad hoc query thing more than I'd like to admit,  I think
there's a place for some form of negation for *. A workaround similar to
what you describe here would be to add special tab completion to psql
that would expand * to the full list (probably on double tab ...)

Ross
-- 
Ross Reedstrom, Ph.D. reeds...@rice.edu
Systems Engineer  Admin, Research Scientistphone: 713-348-6166
Connexions  http://cnx.orgfax: 713-348-3665
Rice University MS-375, Houston, TX 77005
GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E  F888 D3AE 810E 88F0 BEDE

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-11-01 Thread Tom Lane
Eric Ridge eeb...@gmail.com writes:
 My original thought, that I probably didn't explain too clearly, was
 to make the EXCLUDING (...) bit a modifier to the A_Star node.  The
 idea being that you could write * EXCLUDING (...) anywhere you can
 currently write *.

I can think of a number of places where you can write * where I'm
pretty sure we *don't* want this.  It should be restricted to top-level
entries in SELECT targetlists, IMO.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-11-01 Thread Eric B. Ridge
On Nov 1, 2011, at 6:47 PM, Tom Lane t...@sss.pgh.pa.us wrote:

 I can think of a number of places where you can write * where I'm
 pretty sure we *don't* want this.  It should be restricted to top-level
 entries in SELECT targetlists, IMO.

Yes. That is the exact conclusion I've come to.  

However, why is

select table.* foo from table 

allowed?  What does that even mean?

eric

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-11-01 Thread Pavel Stehule
2011/11/1 Eric Ridge eeb...@gmail.com:
 On Tue, Nov 1, 2011 at 1:33 PM, Pavel Stehule pavel.steh...@gmail.com wrote:
 COPY (SELECT * EXCLUDING (a, b, c) FROM big query) TO 'somefile.csv' WITH 
 CSV;

 sorry, I don't accept it. I am able to understand your request for
 adhoc queries. But not for COPY.

 I apologize if that example was confusing.  I wasn't also suggesting
 expanding COPY's syntax.  I was merely pointing out that if
 EXCLUDING(…) were implemented, you'd be able to use it within the
 query given to the COPY command.

I understand it, it is really bad idea use a star in export queries

Pavel


 eric

 ps, it seems my messages aren't hitting the list?  weird.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-11-01 Thread Eric Ridge
On Tue, Nov 1, 2011 at 12:24 PM, Pavel Stehule pavel.steh...@gmail.com wrote:
 some other idea - but only for psql

 we can define a special values, that ensure a some necessary
 preexecution alchemy with entered query

 \pset star_exclude_names col1, col2, col3
 \pset star_exclude_types xml, bytea, text(unlimited)


Sure, something like that could be useful too.  It might be confusing
to users if they forget that they set an exclusion list, but there's
probably ways to work around that.

However, the nice thing about the feature being in SQL is that you can
use it from all clients, and even in other useful ways.  COPY would be
an example (something I also do frequently):

COPY (SELECT * EXCLUDING (a, b, c) FROM big query) TO 'somefile.csv' WITH CSV;

Right now, if you want to exclude a column, you have to list all the
others out manually, or just dump everything and deal with it in an
external tool.

I generally agree with everyone that says using this in application
code is a bad idea, but I don't think that's reason alone to reject
the idea on its face.

eric

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-11-01 Thread Eric Ridge
On Tue, Nov 1, 2011 at 1:33 PM, Pavel Stehule pavel.steh...@gmail.com wrote:
 COPY (SELECT * EXCLUDING (a, b, c) FROM big query) TO 'somefile.csv' WITH 
 CSV;

 sorry, I don't accept it. I am able to understand your request for
 adhoc queries. But not for COPY.

I apologize if that example was confusing.  I wasn't also suggesting
expanding COPY's syntax.  I was merely pointing out that if
EXCLUDING(…) were implemented, you'd be able to use it within the
query given to the COPY command.

eric

ps, it seems my messages aren't hitting the list?  weird.

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-11-01 Thread Stephen Frost
* Marti Raudsepp (ma...@juffo.org) wrote:
 Unfortunately it's far less efficient. Fields would be truncated in
 psql, so full values are still detoasted and transmitted over the
 network.

I'm thinking that we're not too worried about performance of ad-hoc
psql queries..?  At least, for the queries that I'd use this for, I
wouldn't be worried about that.

The various syntax proposals do seem overly long for this, however..  I
was just wondering about something like:

select ~* blah, blah, blah FROM ...

Strikes me as pretty unlikely that making a new 'version' of * like this
is going to break anything or be broken by the SQL standard.  Note- I
haven't looked at the * production or tried to do anything w/ gram.y to
support this yet, but it's a heck of a lot shorter..

Thanks,

Stephen


signature.asc
Description: Digital signature


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-11-01 Thread Tom Lane
Eric B. Ridge eeb...@gmail.com writes:
 However, why is
 select table.* foo from table 
 allowed?  What does that even mean?

Doesn't mean anything, I think --- the SQL standard seems to exclude it.
It's fairly hard to prevent it at the grammar level, since we regard
foo.* as a type of primitive expression, but I suspect it might be a
good idea for transformTargetList to throw an error instead of silently
ignoring the column label.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-31 Thread Marcin Mańk
On Sun, Oct 30, 2011 at 8:50 PM, Eric Ridge eeb...@gmail.com wrote:
 Well, it's a display thing as much as any SELECT statement
 (especially via psql) is a display thing.  It's more like I want
 all 127 columns, except the giant ::xml column, and I'm too lazy to
 type each column name out by hand.


How about an option for psql to truncate too long columns to X characters ?

Greetings
Marcin Mańk

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-31 Thread Andrew Dunstan



On 10/30/2011 10:00 PM, Christopher Browne wrote:


There is legitimate reason to reject this on the basis of nondeterminism.

While we are surely obliged to hold our noses and support SELECT 
*, as:

A) The SQL standard obliges us, and
B) People already use it a lot,

Neither of those factors hold true for the EXCLUDING notion.  So all 
things are decidedly not equal.




Surely it's only non-deterministic to the extent that '*' itself is 
non-deterministic. So your argument boils down to 'anything that 
enhances * is bad,' ISTM.


By all means I find it an interesting feature, but that shouldn't be 
mistaken for necessarily being a desirable feature.


I don't think I wish it.  We're telling our developers not to use 
select *, and I don't think having select * except  would change 
that policy, beyond requiring us to waste time explaining :


No, we're not changing policy.  The fact that PGDG added this to 9.2 
does *not* imply our policy was wrong.




That's fine, and it's a good policy. A good policy might well exclude 
use of a number of available features (e.g. one place I know bans doing 
joins with ',' instead of explicit join operators). But I don't think it 
helps us decide what to support.


The fact is that if you have 100 columns and want 95 of them, it's very 
tedious to have to specify them all, especially for ad hoc queries where 
the house SQL standards really don't matter that much.  It's made more 
tedious by the fact that there is no real help in constructing the 
query. This gets particularly bad with views, which developers often 
seem to stuff with every available column that might be needed by some 
query instead of creating views tailored to particular queries. Not long 
ago annoyance with this prompted my to write a little utility function 
that would give me a query with all the columns specified  so I could 
cut and paste it, and delete the columns I didn't want. (Another 
advantage is that the result is guaranteed typo free, which my typing 
certainly is not.) See https://gist.github.com/818490. It's far from 
perfect, but I still find myself using it several times a month, mainly 
for the very purpose intended by this suggested feature.


cheers

andrew

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-31 Thread Mark Mielke

On 10/31/2011 06:54 AM, Marcin Mańk wrote:

On Sun, Oct 30, 2011 at 8:50 PM, Eric Ridgeeeb...@gmail.com  wrote:

Well, it's a display thing as much as any SELECT statement
(especially via psql) is a display thing.  It's more like I want
all 127 columns, except the giant ::xml column, and I'm too lazy to
type each column name out by hand.


How about an option for psql to truncate too long columns to X characters ?


I would use this option frequently... :-)

It seems more to the point of what is trying to be accomplished, and 
doesn't even require a server change? :-)


--
Mark Mielkem...@mielke.cc


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Darren Duncan

Pavel Stehule wrote:

2011/10/30 Darren Duncan dar...@darrenduncan.net:

I agree that this feature would be quite useful and should be included in
SQL. The exact syntax is less of an issue, but just the ability to cleanly
say select all columns except for these.  I have in fact argued for the
same feature in the past.

If you want to and can implement this feature then more power to you.  I'll
look forward to it being in Pg 9.2.

I think then the only discussion point should be what (terse) syntax to use
for it, not whether the feature should exist at all.

Arguing against this feature is like arguing against supporting where
not() or except or not in.  One should be able to do complements not
only of rows but of columns too.  Basic good language design.


My practice  speaks so this is not true - I don't know only bad
designed projects or very bad designed projects that needs too.

I don't see any reason why do it on SQL level.

It can sence only in psql as same special filter - if we would to
enhace a report features there.


The SQL level is exactly the correct and proper place to do this.

Its all about mathematical parity.  That is the primary reason to do it.

- SELECT * gives you a whole set.
- SELECT foo, bar gives you a subset of that.
- SELECT ALL BUT foo, bar gives you the complementary subset.

There's a variety of uses for specifying complementary subsets, and when the 
clean syntax is available, people will start using it for cleaner code, even if 
they previously had workarounds.


The complementary subset should be implemented in exactly the same place and 
manner as the forward subset, on the SQL level.


Arguing against this is like arguing against a subtraction operator, because we 
can emulate using addition plus negation, or saying subtraction should just be a 
special filter in a client app.


-- Darren Duncan

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread David Wilson
On Sun, Oct 30, 2011 at 1:10 AM, Darren Duncan dar...@darrenduncan.net wrote:

 The SQL level is exactly the correct and proper place to do this.

 Its all about mathematical parity.  That is the primary reason to do it.

 - SELECT * gives you a whole set.
 - SELECT foo, bar gives you a subset of that.
 - SELECT ALL BUT foo, bar gives you the complementary subset.

That's not actually entirely true given the usual SQL (and
mathematical) meaning of set. This feature relates to the set of
attributes returned regarding elements of the returned set, not the
set itself- the actual returned set is identical regardless of the
column-specifier formulation. Claiming this as an SQL mathematical
purity issue is a bit disingenuous, as SQL set manipulation takes
place at the member level rather than the attribute level- SQL is
otherwise quite explicit about requiring explicit listings of the
attributes that the client is interested in regarding a returned set
of member rows.


 Arguing against this is like arguing against a subtraction operator, because
 we can emulate using addition plus negation, or saying subtraction should
 just be a special filter in a client app.

That would be true if this was an argument against WHERE or
EXCEPT. Column specification and row specification are very
different and cannot be conflated.

That's not to say this proposal is without merit, merely that your
arguments for it are poorly founded and not particularly relevant.

-- 
- David T. Wilson
david.t.wil...@gmail.com

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Eric Ridge
On Sun, Oct 30, 2011 at 1:51 PM, Eric B. Ridge e...@tcdi.com wrote:

 eric

 PROPRIETARY AND COMPANY CONFIDENTIAL COMMUNICATIONS

snip

my bad.  Switched email accounts without realizing.  :(

eric

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Jim Nasby
On Oct 29, 2011, at 11:53 PM, Joshua D. Drake wrote:
 On 10/29/2011 03:26 PM, Eric Ridge wrote:
 
 Would y'all accept a patch that extended the SELECT * syntax to let
 you list fields to exclude from the A_Star?
 
 Quite regularly I'll be testing queries via psql and want to see all
 the columns from a fairly wide table except maybe a giant text or xml
 column.  A syntax like:
 
  SELECT * EXCLUDING (big_col1, big_col2) FROM foo;
 
 
 If it is quite regular I would actually argue two things:
 
 1. Use a view

If you know that you want all fields except X, Y and Z, why should you be 
forced to manually cut and paste all the other fields into the view definition? 
That's wasted time and effort that could better be spent elsewhere.
--
Jim C. Nasby, Database Architect   j...@nasby.net
512.569.9461 (cell) http://jim.nasby.net



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Eric B. Ridge
On Oct 30, 2011, at 12:53 AM, Joshua D. Drake wrote:
 
 If it is quite regular I would actually argue two things:
 
 1. Use a view
 2. You haven't normalized correctly
 
 I am not trying to be a pedantic zealot or anything but those would be my 
 arguments against.

You know how general database work goes.  For every situation where you can 
make a view or every situation where you should normalize, there's at least one 
corresponding situation where you can't.  All database systems, Postgres 
included, give us plenty of ways to do things wrong, many of which are much 
worse than this little idea.

I guess I'd like for everyone to evaluate the idea on the value it could 
provide to Postgres and its users, rather than imposing philosophical/religious 
beliefs about correct database schema design.  

I'm regularly tasked with debugging queries, analyzing, exporting, and 
otherwise transforming data into something a customer wants.  I'd use something 
like SELECT * EXCLUDING (…) on a *daily* basis.  I'm sick and tired of stuff 
like:

psql -tA db -c \d table | cut -f1 -d\| | grep -v col | tr \\n ,

just to exclude one column from a list of maybe 100.  And if multiple tables 
are involved in the query, it just gets that much more complicated.  I'd rather 
do:

SELECT * EXCLUDING (x.col) FROM ( giant application-generated query ) 
x;

Then, once I verify it's all good:

COPY ( SELECT * EXCLUDING (x.col) FROM ( giant application-generated 
query ) x ) TO '/tmp/foo.out' WITH CSV;

Anyways, looks like it might be an uphill battle to get the idea accepted (let 
alone any code I write!), but I ain't givin' up just yet.

eric

PROPRIETARY AND COMPANY CONFIDENTIAL COMMUNICATIONS
The information contained in this communication is intended only for
the use of the addressee. Any other use is strictly prohibited.
Please notify the sender if you have received this message in error.
This communication is protected by applicable legal privileges and is
company confidential.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Robert Haas
On Sun, Oct 30, 2011 at 2:15 PM, Jim Nasby j...@nasby.net wrote:
 If you know that you want all fields except X, Y and Z, why should you be 
 forced to manually cut and paste all the other fields into the view 
 definition? That's wasted time and effort that could better be spent 
 elsewhere.

I agree.  This isn't something I would recommend that people do in the
queries they embed in their application, but for ad-hoc queries it
comes up all the time.

OTOH, I'm slightly afraid of how much work it would take to implement
this properly.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread David E. Wheeler
On Oct 30, 2011, at 11:54 AM, Robert Haas wrote:

 If you know that you want all fields except X, Y and Z, why should you be 
 forced to manually cut and paste all the other fields into the view 
 definition? That's wasted time and effort that could better be spent 
 elsewhere.
 
 I agree.  This isn't something I would recommend that people do in the
 queries they embed in their application, but for ad-hoc queries it
 comes up all the time.

It can also be very useful for generating queries that need to omit rows, such 
as in PL/pgSQL functions that use EXECUTE, not to mention client code.

Best,

David


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Darren Duncan
I think the loose consensus I've picked up from people in this thread is that 
the ability to tersely specify a complementary subset of columns is something 
that is simple enough and wouldn't hurt us to have it but that its utility is 
limited such that a lot of people wouldn't want to do the work to implement it 
either.


Eric B. Ridge wrote:

Anyways, looks like it might be an uphill battle to get the idea accepted
(let alone any code I write!), but I ain't givin' up just yet.


I think this is the bottom line here.

The real question to ask ourselves is, if Eric Ridge is willing to do all the 
work to implement this feature, and the code quality is up to the community 
standards and doesn't break anything else, then will the code be accepted?


Its one thing to argue whether a new small feature is useful enough to go to the 
trouble to implement, and its another thing to argue whether that feature is 
harmful enough to reject a free working implementation (of otherwise conforming 
code quality) from someone who has already gone to the trouble to implement it.


Eric, if you want to implement this, I say more power to you, and I will use it.

-- Darren Duncan

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Tom Lane
Darren Duncan dar...@darrenduncan.net writes:
 The real question to ask ourselves is, if Eric Ridge is willing to do all the
 work to implement this feature, and the code quality is up to the community 
 standards and doesn't break anything else, then will the code be accepted?

It's entirely possible that it will get bounced on standards-compliance
grounds.  In particular, I don't think it's acceptable to introduce a
new reserved keyword for this --- that would fall under the fails to
not break anything else category.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Eric Ridge
On Sun, Oct 30, 2011 at 2:54 PM, Robert Haas robertmh...@gmail.com wrote:

 OTOH, I'm slightly afraid of how much work it would take to implement
 this properly.

I think first, the A_Star node struct will need to be expanded to
include a List of qualified column references to exclude.  From there,
the target_el rule in gram.y will need to be expanded to support a
syntax like:
  '*' EXCLUDING '(' columnref_list ')' { ... }
I also think that the indirection_el rule will need to be expanded
to support something similar.

Together, that would let us write both:
 SELECT * EXCLUDING(table1.col1, table2.col1) FROM ...
and
 SELECT table.* EXCLUDING(col1, col2) FROM ...
or even
 SELECT * EXCLUDING(table1.col1), table2.* EXCLUDING(col1) FROM ...

I think changing the indirection_el rule might have an impact to
OLD/NEW, but I'm not sure.  Is it legal to write OLD.*, and if so,
would you also want to write OLD.* EXCLUDING (...) in those cases?  I
think this only applies to RULES or SQL-type trigger functions, but
not pl/pgsql?

Then it looks like touching various functions in src/backend/nodes/*.c
to do the right things with the new exclusion list field in A_Star.  I
haven't traced through everything yet, but it looks like if the
various places in src/backend/nodes/*.c are done correctly, then
regurgitating a view definition or whatnot that includes this syntax
will be automatic (i.e., no specific support required for pg_dump)?

Anyways, at first I thought it would be about 8hrs of work just to get
something working.  Maybe it's more like 20, but even still, it seems
fairly straightforward.

eric

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Darren Duncan

David Wilson wrote:

On Sun, Oct 30, 2011 at 1:10 AM, Darren Duncan dar...@darrenduncan.net wrote:


The SQL level is exactly the correct and proper place to do this.

Its all about mathematical parity.  That is the primary reason to do it.

- SELECT * gives you a whole set.
- SELECT foo, bar gives you a subset of that.
- SELECT ALL BUT foo, bar gives you the complementary subset.


That's not actually entirely true given the usual SQL (and
mathematical) meaning of set. This feature relates to the set of
attributes returned regarding elements of the returned set, not the
set itself- the actual returned set is identical regardless of the
column-specifier formulation. Claiming this as an SQL mathematical
purity issue is a bit disingenuous, as SQL set manipulation takes
place at the member level rather than the attribute level- SQL is
otherwise quite explicit about requiring explicit listings of the
attributes that the client is interested in regarding a returned set
of member rows.


SQL rowsets/tables are distinctly sets across two dimensions, both across 
columns and across rows.  You have sets of rows and sets of columns at the same 
time.  And it is useful to slice the rowset/table along either or both 
dimension, wherein you produce a subset in that dimension.  We presently have 
the terse syntax for specifying both the subset we do want and the subset we 
want the complement of, for rows, but not for columns.  It is true that columns 
and rows are not the same, but they are both still sets.



Arguing against this is like arguing against a subtraction operator, because
we can emulate using addition plus negation, or saying subtraction should
just be a special filter in a client app.


That would be true if this was an argument against WHERE or
EXCEPT. Column specification and row specification are very
different and cannot be conflated.


Well I raised the WHERE/EXCEPT analogy in my initial reply.

Not conflating rows and columns is why we have different syntax to work with 
them.


That's not to say this proposal is without merit, merely that your
arguments for it are poorly founded and not particularly relevant.


I disagree, but regardless, other arguments have been made for the feature that 
are more based in utility, and I agree with those, how having the feature can 
save a lot of users a lot of work.


-- Darren Duncan

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Eric Ridge
On Sun, Oct 30, 2011 at 3:17 PM, Tom Lane t...@sss.pgh.pa.us wrote:

 It's entirely possible that it will get bounced on standards-compliance
 grounds.

And that's a perfectly valid reason to reject it.

 In particular, I don't think it's acceptable to introduce a
 new reserved keyword for this --- that would fall under the fails to
 not break anything else category.

Please correct me if I'm wrong, but if we choose the word carefully
(which is why I chose EXCLUDING), I think we're okay?  EXCLUDING is
already defined as an ordinary key word.  And it's new use in this
situation seems to be completely unambiguous, such that you'd still be
able to use excluding everywhere you already could.

You know more about the grammar than I (or probably most anyone), so
I'm wondering why you think it might need to be a reserved keyword?
Alternatively, would it be okay to use an existing reserved keyword?

eric

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Darren Duncan

Tom Lane wrote:

Darren Duncan dar...@darrenduncan.net writes:

The real question to ask ourselves is, if Eric Ridge is willing to do all the
work to implement this feature, and the code quality is up to the community 
standards and doesn't break anything else, then will the code be accepted?


It's entirely possible that it will get bounced on standards-compliance
grounds.  In particular, I don't think it's acceptable to introduce a
new reserved keyword for this --- that would fall under the fails to
not break anything else category.

regards, tom lane


Well then we come up with a (SQL-level) syntax for the feature that doesn't 
introduce new reserved keywords.


As I said before, the important thing is to have the feature, and that the exact 
syntax is the main point to discuss.


Postgres already has a number of syntactic features that aren't in the SQL 
standard and coexist, and so we add one of those.


-- Darren Duncan

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Mark Mielke

Stupid question:

Is this just a display thing? Or does this have impact for things such 
as COUNT(*) vs COUNT(1)?


Is it like a view, but on the fly?

I'm found myself in the *occasional* (certainly not daily!) situation 
where such a feature might be useful, but each time I wonder about if 
there should be a better way, I realize that if I ever saw such a thing 
in production code it would be the first code I shot down. 1) Not 
standards compliant, 2) Not deterministic (i.e. a database change might 
cause my code to break), 3) Working around a problem that maybe 
shouldn't exist in the first place? It's a like buying a rug, so that 
nobody sees the scratches on the floor.


I can see the contention. :-)

If it existed, I would occasionally use it from the command line. I'm 
thinking three times a year. Certainly not daily. Heck, if it's more 
characters to type (than select * ...) the number of times I would 
bother typing it are quite short. :-)


Cheers,

--
Mark Mielkem...@mielke.cc


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Eric Ridge
On Sun, Oct 30, 2011 at 3:38 PM, Mark Mielke m...@mark.mielke.cc wrote:
 Stupid question:

 Is this just a display thing?

Well, it's a display thing as much as any SELECT statement
(especially via psql) is a display thing.  It's more like I want
all 127 columns, except the giant ::xml column, and I'm too lazy to
type each column name out by hand.

 Or does this have impact for things such as COUNT(*) vs COUNT(1)?

If it does, it's broken.

 Is it like a view, but on the fly?

Naw, it's just short-hand for omitting columns from the output target
list.  As I'm envisioning the feature, it would be SQL-level syntax,
so you could bake it into a view, but...

 1) Not standards compliant,

Sure, no doubt.  I think the ::typename syntax is not standards
compliant either, and I bet I'm not the only Postgres user to use that
every day.  But I secretly tend to agree, I wouldn't necessarily want
to use this in production/application code.

 2) Not deterministic (i.e. a database change might cause my code to break),

Okay, I'm inventing a use-case here, but say you have a users table
with various bits of metadata about the user, including password.
Maybe, regardless of database changes, you never want the password
column returned:  SELECT * EXCLUDING (password) FROM tbl_users;

Changes of omission can break your code just as easily.

 3) Working around a problem that maybe shouldn't exist in the first place? 
 It's
 a like buying a rug, so that nobody sees the scratches on the floor.

Sometimes, rugs are cheaper than new floors.

eric

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Tom Lane
Eric Ridge eeb...@gmail.com writes:
 On Sun, Oct 30, 2011 at 3:17 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 In particular, I don't think it's acceptable to introduce a
 new reserved keyword for this --- that would fall under the fails to
 not break anything else category.

 Please correct me if I'm wrong, but if we choose the word carefully
 (which is why I chose EXCLUDING), I think we're okay?  EXCLUDING is
 already defined as an ordinary key word.

Yeah, it's unreserved so it doesn't break use of the same name for
columns or functions.  I'm not sure that you can make the syntax work
the way you suggest without bumping up its reserved-ness level.
That's just a gut feeling, I've not tried it ... but the proposed
syntax sure looks a lot like a call to a function named EXCLUDING.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Tom Lane
Eric Ridge eeb...@gmail.com writes:
 On Sun, Oct 30, 2011 at 3:38 PM, Mark Mielke m...@mark.mielke.cc wrote:
 2) Not deterministic (i.e. a database change might cause my code to break),

 Okay, I'm inventing a use-case here, but say you have a users table
 with various bits of metadata about the user, including password.
 Maybe, regardless of database changes, you never want the password
 column returned:  SELECT * EXCLUDING (password) FROM tbl_users;

Well, here you're not only inventing a use-case, but you're making a lot
of contrary-to-fact-and-to-SQL-spec assumptions about when the * notation
gets expanded.  This thing wouldn't be useful that way in views.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Eric Ridge
On Sun, Oct 30, 2011 at 4:03 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 That's just a gut feeling, I've not tried it ... but the proposed
 syntax sure looks a lot like a call to a function named EXCLUDING.

I think what makes it okay is that its new use is only defined to
immediately follow an asterisk in the target_el production.  If you
look at gram.y:11578 (from git HEAD), I was thinking this:
| a_expr
{
$$ = makeNode(ResTarget);
$$-name = NULL;
$$-indirection = NIL;
$$-val = (Node *)$1;
$$-location = @1;
}
+| '*' EXCLUDING '(' columnref_list ')'
+{
+/** make magic happen */
+}
| '*'
{
ColumnRef *n = makeNode(ColumnRef);
n-fields =
list_make1(makeNode(A_Star));
n-location = @1;

$$ = makeNode(ResTarget);
$$-name = NULL;
$$-indirection = NIL;
$$-val = (Node *)n;
$$-location = @1;
}

And it looks like something similar would be necessary in the
indirection_el production, around line 11478.  But that might be
overly simplistic (and wrong).

eric

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Andrew Dunstan



On 10/30/2011 04:03 PM, Tom Lane wrote:



Please correct me if I'm wrong, but if we choose the word carefully
(which is why I chose EXCLUDING), I think we're okay?  EXCLUDING is
already defined as an ordinary key word.

Yeah, it's unreserved so it doesn't break use of the same name for
columns or functions.  I'm not sure that you can make the syntax work
the way you suggest without bumping up its reserved-ness level.
That's just a gut feeling, I've not tried it ... but the proposed
syntax sure looks a lot like a call to a function named EXCLUDING.


Adding this rule doesn't appear to cause any complications:

   target_el:   '*' EXCLUDING '(' ')'



I'm not saying we need to do this, although there have been times when I 
might have liked it, but I suspect it would not cause us any grammar 
problems at least.


cheers

andrew


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Darren Duncan

Eric Ridge wrote:

On Sun, Oct 30, 2011 at 4:03 PM, Tom Lane t...@sss.pgh.pa.us wrote:

That's just a gut feeling, I've not tried it ... but the proposed
syntax sure looks a lot like a call to a function named EXCLUDING.


I think what makes it okay is that its new use is only defined to
immediately follow an asterisk in the target_el production.  If you
look at gram.y:11578 (from git HEAD), I was thinking this:

snip

That's an interesting thought.

I had previously envisioned EXCLUDING to appear exactly once in the SELECT list, 
where it functions as a column analogy to EXCEPT for rows.  So you'd say:


  SELECT list of stuff to keep EXCLUDING list of stuff to skip FROM ...

But then your comment makes me think that your EXCLUDING actually could have a 
much tighter scope, and as a result might appear multiple times, like AS can:


  SELECT foo.* EXCLUDING foo.x, bar.* EXCLUDING bar.y, baz.z, (a+b) AS c FROM 
...

So if you do this, EXCLUDING has syntactic precedence similar to AS, and while 
AS is only valid not following a *, EXCLUDING is only valid following a *, and 
so EXCLUDING directly modifies a single * and not the SELECT list in general.


Is that where you're going with this?

If so, I think that would make the feature even more valuable and more 
syntactically clean than I had previously thought.


-- Darren Duncan

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Eric Ridge
On Sun, Oct 30, 2011 at 4:43 PM, Darren Duncan dar...@darrenduncan.net wrote:

  SELECT foo.* EXCLUDING foo.x, bar.* EXCLUDING bar.y, baz.z, (a+b) AS c FROM
 ...
 Is that where you're going with this?

Yes.  It's basically a modifier to the star that immediately precedes
it.  In order to support excluding multiple columns, it needs parens:
 SELECT foo.* EXCLUDING (foo.x, foo.y), bar.* EXCLUDING (bar.y),
baz.z, (a+b) AS c

but yes, that's what I'm thinking.  I think doing this will require
more changes to the grammar than I had first thought because there'd
be no point in supporting:
 SELECT foo.* EXCLUDING (foo.* EXCLUDING foo.y) FROM ...
It looks like the above would be implicitly allowed without a bit of extra work.

But, if you've got a complex query consisting of a few joins, it'd be
nice to say:
 SELECT * EXCLUDING (table1.*, table2.x) FROM table1 INNER JOIN table2 ...

 If so, I think that would make the feature even more valuable and more
 syntactically clean than I had previously thought.

I don't actually like the term EXCLUDING, but it conveys what's
happening and is already defined as a keyword.  I thought about
EXCEPT, but that doesn't work for obvious reasons, and NOT might
just be confusing.

eric

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Darren Duncan

Eric Ridge wrote:

I don't actually like the term EXCLUDING, but it conveys what's
happening and is already defined as a keyword.  I thought about
EXCEPT, but that doesn't work for obvious reasons, and NOT might
just be confusing.


How about BUT?

Is that already in use by something?  Its nice and short and conveys the 
except meaning.


And there is already precedent for using that word for this purpose.

CJ Date already uses ALL BUT in his literature as a modifier to his 
illustrative relation projection syntax to give the complementary projection, 
like with r{x,y} vs r{all but x,y}.


Also, a more tenuous connection, Larry Wall likes but as logical modifier.

-- Darren Duncan

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Eric Ridge
On Sun, Oct 30, 2011 at 3:17 PM, Tom Lane t...@sss.pgh.pa.us wrote:
snip

 fails to not break anything else category.

From what I've seen watching this list, you're usually right.  :)

It looks like it's perfectly okay to write:
SELECT pg_class.* AS foo FROM pg_class;
(with or without the AS)

I don't know what the above actually means, but it stops SELECT
pg_class.* EXCLUDING(...) dead in its tracks.  So, I'd have to break a
syntax (albeit silly?) that currently works to support this.  :(

eric

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Mark Mielke

On 10/30/2011 03:50 PM, Eric Ridge wrote:

On Sun, Oct 30, 2011 at 3:38 PM, Mark Mielkem...@mark.mielke.cc  wrote:

2) Not deterministic (i.e. a database change might cause my code to break),

Okay, I'm inventing a use-case here, but say you have a users table
with various bits of metadata about the user, including password.
Maybe, regardless of database changes, you never want the password
column returned:  SELECT * EXCLUDING (password) FROM tbl_users;

Changes of omission can break your code just as easily.


I think I wasn't as clear as I intended. In many ways, I think use of 
* in the first place is wrong for code (despite that I do it as well). 
Therefore, * EXCLUDING (...) would also be wrong. It comes to does 
the code know what it wants?


In the above case - maybe you don't want password - what about social 
insurance number, credit card number, or any other private bit? The only 
way to truly know you aren't accidentally pulling in fields you don't 
need or want to unnecessarily expose on the wire - is to specifically 
list the fields you DO want, which is precisely to not use * at all.


A particular area that I don't like * is that my code may make an 
assumption about the exact field names, or the field order that comes 
out. If this is explicitly specified, then it will survive ALTER 
TABLE, or a restore of the table with columns in a different order, or 
a replacement of the table with a view. However, if I use *, then my 
code is likely to fail in any of these cases, and possibly fail in some 
unexpected way. For example, in a language such as Perl with DBI 
returning a hash, I may accidentally assume that the field is always 
undef. It might even pass some designer testing if the value is usually 
NULL = undef, and I fail to simulate the case where it is not.


select * is not deterministic from a programming perspective.

--
Mark Mielkem...@mielke.cc


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Christopher Browne
On Sun, Oct 30, 2011 at 7:46 PM, Mark Mielke m...@mark.mielke.cc wrote:
 In the above case - maybe you don't want password - what about social
 insurance number, credit card number, or any other private bit? The only way
 to truly know you aren't accidentally pulling in fields you don't need or
 want to unnecessarily expose on the wire - is to specifically list the
 fields you DO want, which is precisely to not use * at all.

A fairly useful characteristic case would be related to this, namely...

select * excluding password from some_table;

The sensitive bits of data are often fairly well enumerated, so it's a
useful-ish idea to pull everything except that.

The joys and challenges may be pretty well characterized by:

  create view sanitized_table as
select * excluding password from some_table;

Now, if someone running queries somewhere downstream is sufficiently
lacking in clue that they don't know which columns they *do* want,
then it seems dubious to me that letting them exclude the columns that
they imagine that they know they *don't* want is actually valid.

That is, if they don't know what they want (e.g. - the column set in a
traditional sans-EXCLUDES query), then I'm rather suspicious that they
aren't competent to build a proper EXCLUDES clause.
-- 
When confronted by a difficult problem, solve it by reducing it to the
question, How would the Lone Ranger handle this?

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Darren Duncan

Mark Mielke wrote:

On 10/30/2011 03:50 PM, Eric Ridge wrote:

Changes of omission can break your code just as easily.


I think I wasn't as clear as I intended. In many ways, I think use of 
* in the first place is wrong for code (despite that I do it as well). 
Therefore, * EXCLUDING (...) would also be wrong. It comes to does 
the code know what it wants?

snip


select * is not deterministic from a programming perspective.


I understand what you're saying.  However, we're stuck with * because it is in 
the standard and is widely used, and if we have * anyway, then the exclusion 
proposal is just an enhancement to that.  So there is no reason to reject the 
complementary columns feature because of the problems with select *; you might 
as well argue to get rid of select *. -- Darren Duncan


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Robert Haas
On Sun, Oct 30, 2011 at 6:11 PM, Darren Duncan dar...@darrenduncan.net wrote:
 Eric Ridge wrote:

 I don't actually like the term EXCLUDING, but it conveys what's
 happening and is already defined as a keyword.  I thought about
 EXCEPT, but that doesn't work for obvious reasons, and NOT might
 just be confusing.

 How about BUT?

 Is that already in use by something?  Its nice and short and conveys the
 except meaning.

 And there is already precedent for using that word for this purpose.

 CJ Date already uses ALL BUT in his literature as a modifier to his
 illustrative relation projection syntax to give the complementary
 projection, like with r{x,y} vs r{all but x,y}.

 Also, a more tenuous connection, Larry Wall likes but as logical modifier.

Look, there's no good speculating about what might work without
sitting down and editing gram.y.  The exact choice of keyword matters
a lot less than whether this can be done with out shift/reduce or
reduce/reduce conflicts.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Eric B. Ridge
 The exact choice of keyword matters
 a lot less than whether this can be done with out shift/reduce or
 reduce/reduce conflicts.

Which is the problem right now.  See my other email.  

I'll post a diff tomorrow. Maybe if enough folks think is a feature worth 
having we can find a solution.  My gram.y-fu is at its limits right now. 

eric

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Christopher Browne
There is legitimate reason to reject this on the basis of nondeterminism.

While we are surely obliged to hold our noses and support SELECT *, as:
A) The SQL standard obliges us, and
B) People already use it a lot,

Neither of those factors hold true for the EXCLUDING notion.  So all things
are decidedly not equal.

By all means I find it an interesting feature, but that shouldn't be
mistaken for necessarily being a desirable feature.

I don't think I wish it.  We're telling our developers not to use select
*, and I don't think having select * except  would change that policy,
beyond requiring us to waste time explaining :

No, we're not changing policy.  The fact that PGDG added this to 9.2 does
*not* imply our policy was wrong.


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Pavel Stehule
2011/10/31 Christopher Browne cbbro...@gmail.com:
 There is legitimate reason to reject this on the basis of nondeterminism.

 While we are surely obliged to hold our noses and support SELECT *, as:
 A) The SQL standard obliges us, and
 B) People already use it a lot,

 Neither of those factors hold true for the EXCLUDING notion.  So all things
 are decidedly not equal.

 By all means I find it an interesting feature, but that shouldn't be
 mistaken for necessarily being a desirable feature.

 I don't think I wish it.  We're telling our developers not to use select
 *, and I don't think having select * except  would change that policy,
 beyond requiring us to waste time explaining :


+1

It can carry some new problems with cache - actually we don't need
rebuild views after additing column to table or view

Pavel

 No, we're not changing policy.  The fact that PGDG added this to 9.2 does
 *not* imply our policy was wrong.

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-29 Thread Stephen Frost
Eric,

* Eric Ridge (eeb...@gmail.com) wrote:
 It seems like such a syntax would better document the
 intent of a query too, rather than leaving one wondering if big_col1
 was supposed to be omitted from the target list or not.

Well, I expect most here would say that any application query should be
specific about exactly what columns it wants (iow- don't use select * in
your apps :).  As for it being useful in psql, I could see that.

 Anyways, I just wanted to run the idea by youse guys before I put too
 much more effort into it.  I've already made what appear to be the
 minimum necessary changes to gram.y, and a few quick greps through the
 code make me think the rest will be pretty easy.
 
 Maybe the SQL spec says something about this and nobody's done the work yet?

I don't know of anything like this in the spec.  Also, there would be
concern about this possibly going against spec, breaking possibly valid
queries, promoting keywords to reserved words, and maybe ending up in a
bad situation if the SQL committee decides to support that kind of
syntax for something completely different.

In general, I doubt this is something we'd implement, but others may
feel differently.  What might be interesting to consider is how hard it
would be to make psql smarter when it comes to line editing in this
regard.  Maybe if there was a way to easily expand the '*' from psql and
then you could remove the columns from the list easily..?

Thanks,

Stephen


signature.asc
Description: Digital signature


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-29 Thread Eric Ridge
On Sat, Oct 29, 2011 at 6:35 PM, Stephen Frost sfr...@snowman.net wrote:
 Maybe the SQL spec says something about this and nobody's done the work yet?

 I don't know of anything like this in the spec.  Also, there would be
 concern about this possibly going against spec, breaking possibly valid
 queries, promoting keywords to reserved words, and maybe ending up in a
 bad situation if the SQL committee decides to support that kind of
 syntax for something completely different.

At least concerning breaking valid queries and promoting keywords, I
don't think the former can happen (they'd fail to parse today) and the
latter doesn't seem necessary as EXCLUDING's use in this case
appears to be completely unambiguous.

However, I realize there's no second-guessing what the SQL committee
might do in the future.

 In general, I doubt this is something we'd implement, but others may
 feel differently.

I hope so.  :)

 What might be interesting to consider is how hard it
 would be to make psql smarter when it comes to line editing in this
 regard.  Maybe if there was a way to easily expand the '*' from psql and
 then you could remove the columns from the list easily..?

Probably really dang hard, especially when you consider a SELECT *
involving lots of joins.  And even if it turned out to be easy, it
would be limited to psql.

Anyways, it's just something I've wanted for quite awhile and thought
I'd actually do the work to make it happen, *if* y'all would take it.

eric

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-29 Thread Darren Duncan
I agree that this feature would be quite useful and should be included in SQL. 
The exact syntax is less of an issue, but just the ability to cleanly say 
select all columns except for these.  I have in fact argued for the same 
feature in the past.


If you want to and can implement this feature then more power to you.  I'll look 
forward to it being in Pg 9.2.


I think then the only discussion point should be what (terse) syntax to use for 
it, not whether the feature should exist at all.


Arguing against this feature is like arguing against supporting where not() or 
except or not in.  One should be able to do complements not only of rows but 
of columns too.  Basic good language design.


-- Darren Duncan

Eric Ridge wrote:

Would y'all accept a patch that extended the SELECT * syntax to let
you list fields to exclude from the A_Star?

Quite regularly I'll be testing queries via psql and want to see all
the columns from a fairly wide table except maybe a giant text or xml
column.  A syntax like:

 SELECT * EXCLUDING (big_col1, big_col2) FROM foo;

would be pretty handy.  It would definitely save some typing in
certain cases.  It seems like such a syntax would better document the
intent of a query too, rather than leaving one wondering if big_col1
was supposed to be omitted from the target list or not.

Anyways, I just wanted to run the idea by youse guys before I put too
much more effort into it.  I've already made what appear to be the
minimum necessary changes to gram.y, and a few quick greps through the
code make me think the rest will be pretty easy.

Maybe the SQL spec says something about this and nobody's done the work yet?

Thanks for your input!

eric




--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-29 Thread Pavel Stehule
2011/10/30 Darren Duncan dar...@darrenduncan.net:
 I agree that this feature would be quite useful and should be included in
 SQL. The exact syntax is less of an issue, but just the ability to cleanly
 say select all columns except for these.  I have in fact argued for the
 same feature in the past.

 If you want to and can implement this feature then more power to you.  I'll
 look forward to it being in Pg 9.2.

 I think then the only discussion point should be what (terse) syntax to use
 for it, not whether the feature should exist at all.

 Arguing against this feature is like arguing against supporting where
 not() or except or not in.  One should be able to do complements not
 only of rows but of columns too.  Basic good language design.

My practice  speaks so this is not true - I don't know only bad
designed projects or very bad designed projects that needs too.

I don't see any reason why do it on SQL level.

It can sence only in psql as same special filter - if we would to
enhace a report features there.

Regards

Pavel Stehule


 -- Darren Duncan

 Eric Ridge wrote:

 Would y'all accept a patch that extended the SELECT * syntax to let
 you list fields to exclude from the A_Star?

 Quite regularly I'll be testing queries via psql and want to see all
 the columns from a fairly wide table except maybe a giant text or xml
 column.  A syntax like:

     SELECT * EXCLUDING (big_col1, big_col2) FROM foo;

 would be pretty handy.  It would definitely save some typing in
 certain cases.  It seems like such a syntax would better document the
 intent of a query too, rather than leaving one wondering if big_col1
 was supposed to be omitted from the target list or not.

 Anyways, I just wanted to run the idea by youse guys before I put too
 much more effort into it.  I've already made what appear to be the
 minimum necessary changes to gram.y, and a few quick greps through the
 code make me think the rest will be pretty easy.

 Maybe the SQL spec says something about this and nobody's done the work
 yet?

 Thanks for your input!

 eric



 --
 Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-hackers


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-29 Thread Joshua D. Drake


On 10/29/2011 03:26 PM, Eric Ridge wrote:


Would y'all accept a patch that extended the SELECT * syntax to let
you list fields to exclude from the A_Star?

Quite regularly I'll be testing queries via psql and want to see all
the columns from a fairly wide table except maybe a giant text or xml
column.  A syntax like:

  SELECT * EXCLUDING (big_col1, big_col2) FROM foo;



If it is quite regular I would actually argue two things:

1. Use a view
2. You haven't normalized correctly

I am not trying to be a pedantic zealot or anything but those would be 
my arguments against.


Sincerely,

Joshua D. Drake
--
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Support, Training, Professional Services and Development
The PostgreSQL Conference - http://www.postgresqlconference.org/
@cmdpromptinc - @postgresconf - 509-416-6579

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers