Search Postgresql Archives

Re: a proposal for a new functionality: "SELECT * [EXCEPT col1 [,col2]]

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Paul Jungwirth <pj@xxxxxxxxxxxxxxxxxxxxxxxx> writes:
> Not that this is necessarily fatal, but you'd need to avoid parsing 
> trouble with the other EXCEPT, e.g.
> SELECT 1 EXCEPT SELECT 1;

Yeah, it doesn't sound like much consideration has been given to
that ambiguity, but it's a big problem if you want to use a syntax
like this.

> Google Big Query was mentioned upthread. I see they require parens, e.g. 
> SELECT ... EXCEPT (...). I don't think that actually fixes the ambiguity 
> though.

Indeed it doesn't, because you can parenthesize an EXCEPT's sub-queries:

regression=# select 1 except (select 2);
 ?column? 
----------
        1
(1 row)

In principle, once you got to the SELECT keyword you could tell things
apart, but I'm afraid that might be too late for a Bison-based parser.

> So it seems they require at least one `*` in the SELECT target list. In 
> fact the `*` must be the very last thing. Personally I think it should 
> be as general as possible and work even without a `*` (let alone caring 
> about its position).

I wonder if they aren't thinking of the EXCEPT as annotating the '*'
rather than the whole SELECT list.  That seems potentially more flexible,
not less so.  Consider

SELECT t1.* EXCEPT (foo, bar), t2.* EXCEPT (baz) ... FROM t1, t2, ...

This doesn't have any problem with ambiguity if t2 has a "foo" column,
or if t1 has a "baz" column; which indeed would be cases where this
sort of ability would be pretty useful, since otherwise you end up
with painful-to-rename duplicate output column names.  And certainly
there is no particular need for this construct if you didn't write
a "*".

			regards, tom lane





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux