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

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

-Kevin

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.

On Mon, Aug 29, 2011 at 7:49 AM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> 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.  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.

yeah, that's the correct way, but why does this work?
select val from random() as val;

That's illegal IMO, and walls you off from syntax (like::) you
normally should be able to use.

merlin

Merlin Moncure <mmoncure@gmail.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 ::).

-Kevin

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.

-Kevin

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? Doesn't this break the promise of equivalency between the 'CAST .. ' and '::'?

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

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

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

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> wrote:

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

Also note the difference between a record and a scalar here. I
forgot to write it to return val as a scalar, which seems to be what
you're after. It should have been:

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

-Kevin

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:

Merlin Moncure <mmoncure@gmail.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.

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

regards, tom lane

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

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:

Merlin Moncure <mmoncure@gmail.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.

Alexey Klyukin <alexk@commandprompt.com> writes:

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

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?

I'm not really excited about that; CAST is more verbose and not
particularly more readable (at least IMO). What I was wondering about
was altering the internal representation to remember which format had
been used, and reverse-listing in that same format. That would both fix
this issue, and please users who have a stylistic preference for one or
the other format.

regards, tom lane

On Mon, Aug 29, 2011 at 9:00 AM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:

Merlin Moncure <mmoncure@gmail.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 -- duh. I knew that...the canonical case for this is the
definition of pg_locks view. I've just never seen it used for a
regular scalar function. The whole thing is pretty peculiar IMO (but
useful occasionally).

merlin