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

Alexey Klyukin <alexk@commandprompt.com> wrote:
> On Aug 29, 2011, at 5:02 PM, Kevin Grittner wrote:
>
>> Alexey Klyukin <alexk@commandprompt.com> wrote:
>>
>>> Function calls can appear in the FROM clause. (This is
>>> especially useful for functions that return result sets, but any
>>> function can be used.) This acts as though its output were
>>> created as a temporary table for the duration of this single
>>> SELECT command.
>>
>> It doesn't say that operators which provide equivalent
>> functionality to functions can also be used.
>
> I agree, but why is it possible to use the type casting with CAST
> there?

Because the syntax is that of a function, which is allowed.

> Doesn't this break the promise of equivalency between the
> 'CAST .. ' and '::'?

No.  Equivalent functionality doesn't imply that the different
syntax forms can be used in the same places; just that they do the
same thing when used.   This is hardly unique to casting.
Comparison of two text values is done through the texteq function.

test=# select val from texteq('a', 'a') as val;
 val
-----
 t
(1 row)

test=# select val from 'a' = 'a' as val;
ERROR:  syntax error at or near "'a'"
LINE 1: select val from 'a' = 'a' as val;
                        ^
test=# select val from (select 'a' = 'a') as val;
 val
-----
 (t)
(1 row)

> select val from CAST(random() as integer) as val;
>  val
> -----
>    1
> (1 row)

Right.  A function is allowed as a from_item.  Arbitrary expressions
using operators which happen to provide equivalent services are not.

-Kevin