Re: the '::' cast doesn't work in the FROM clause

On Aug 29, 2011, at 5:47 PM, Tom Lane wrote:

> "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
>> Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>>> yeah, that's the correct way, but why does this work?
>>> select val from random() as val;
>
>> If you look at the PostgreSQL reference docs for the SELECT
>> statement, a from_item can be a SELECT statement in parentheses or a
>> function call (among other things). It cannot be an arbitrary
>> expression containing operators (like ::).
>
> Right. We also accept things that look syntactically like function
> calls, so as to avoid debates with newbies about whether, say,
> COALESCE(...) is a function or not. CAST() falls into that category,
> while :: doesn't.

I was wondering exactly why CAST() is permitted, while it's not a function (in Kevin's example, texteq is a function) and the explanation above answers my question.

>
> There is actually a practical reason for this policy beyond the question
> of whether CAST is a function call or not: the structure name(...) has
> a well-defined syntactic extent, so there are no issues of operator
> precedence to worry about when it's embedded in a larger construct.
> IIRC, we ran into exactly that problem with the CREATE INDEX syntax,
> which is why an expression index column has to be parenthesized unless
> it looks like a function call.
>
> So IMO there is no syntax bug here.

I agree, thank you and Kevin for the great explanation!

> There is a dump/reload bug though :-( ... if you were to do
>
> create view vv as select val from CAST(random() as integer) as val;
>
> you will find that the system prints it out with the :: syntax,
> which won't work.

Would it be acceptable/sufficient to output CAST(...) instead of '::' for all casts in pg_dump to fix this problem, assuming that CAST can be used anywhere where '::' is accepted?

--
Alexey Klyukin http://www.commandprompt.com
The PostgreSQL Company – Command Prompt, Inc.