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

On Aug 29, 2011, at 3:49 PM, Kevin Grittner wrote:

> Alexey Klyukin wrote:
>
>> The following statement produces an error message in PostgreSQL 8.4
>> - 9.2 (head):
>>
>> postgres=# select val from random()::integer as val;
>
>> The same statement rewritten with CAST AS works as expected:
>>
>> postgres=# select val from CAST(random() as integer) as val;
>
>> The documentation says these casts are equivalent, so either that's
>> wrong, or this is a bug.
>
> Please point out where you think the documentation says that.

Here:

>
> A type cast specifies a conversion from one data type to another. PostgreSQL accepts two equivalent syntaxes for type casts:
>
> CAST ( expression AS type )
> expression::type
>

http://www.postgresql.org/docs/9.0/interactive/sql-expressions.html#SQL-SYNTAX-TYPE-CASTS

> The way I read it, this is the correct syntax:
>
> test=# select val from (select random()::integer) as x(val);
> val
> -----
> 1
> (1 row)
>
> Not only are you missing required parentheses and the SELECT keyword,
> you're returning a record rather than a scalar value.

SELECT val FROM random() AS val (same as the problematic query, but w/o casts) doesn't produce any errors and IMO is a valid syntax. Here's a quote from the SELECT documentation:

> 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.

http://www.postgresql.org/docs/9.0/interactive/sql-select.html

The problem is that 2 types of casts behave differently when applied to random() in this query.

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