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

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

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

Here:

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

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


>  The way I read it, this is the correct syntax:
>=20
> test=3D# select val from (select random()::integer) as x(val);
> val=20
> -----
>   1
> (1 row)
>=20
> 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 cas=
ts)  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 duratio=
n of this single SELECT command.=20

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

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

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