Re: TABLE not synonymous with SELECT * FROM?

On 11 November 2013 15:03, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Colin 't Hart" <colin(at)sharpheart(dot)org> writes:
>> I would've thought it was implemented as a shortcut for "SELECT *
>> FROM" at the parse level (ie encounter "TABLE" and insert "SELECT *
>> FROM" into the parse tree and continue), but it seems there is more to
>> it.
>
> If you look at the PG grammar you'll see that "TABLE relation_expr"
> appears as one variant of simple_select, which means that you can attach
> WITH, ORDER BY, FOR UPDATE, or LIMIT to it. The other things you mention
> are only possible in a clause that actually starts with SELECT. AFAICS,
> this comports with the SQL standard's syntax specification (look at the
> difference between <query specification> and <query expression>).
> The comment for simple_select saith
>
> * Note that sort clauses cannot be included at this level --- SQL requires
> * SELECT foo UNION SELECT bar ORDER BY baz
> * to be parsed as
> * (SELECT foo UNION SELECT bar) ORDER BY baz
> * not
> * SELECT foo UNION (SELECT bar ORDER BY baz)
> * Likewise for WITH, FOR UPDATE and LIMIT. Therefore, those clauses are
> * described as part of the select_no_parens production, not simple_select.
> * This does not limit functionality, because you can reintroduce these
> * clauses inside parentheses.

Makes sense. I had been wondering about that order by stuff too.

Methinks we should fix the documentation, something like:

The command

TABLE name

is equivalent to

SELECT * FROM name

It can be used as a top-level command or as a space-saving syntax
variant in parts of complex queries. Only the WITH, ORDER BY, LIMIT,
and Locking clauses and set operations can be used with TABLE; the
WHERE and ORDER BY clauses and any form of aggregation cannot be used.

Cheers,

Colin