Thread: = any((select )) needs cast, why?

L.S.

Could someone point me to the formal reason why in:

postgres=# select version();
                                        version
---------------------------------------------------------------------------------------
 PostgreSQL 9.0.4 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC)
4.3.3, 64-bit
(1 row)


the cast to int[ ] of the result row is needed here:

postgres=# select 1 = any((select ARRAY[1,2])::int[]);
 ?column?
----------
 t
(1 row)


in order to avoid an error:

postgres=# select 1 = any((select ARRAY[1,2]));
ERROR:  operator does not exist: integer = integer[]
LINE 1: select 1 = any((select ARRAY[1,2]));
                 ^
HINT:  No operator matches the given name and argument type(s). You might need
to add explicit type casts.


Is the right hand side resulting in an array of an array or....?

--


Best,




Frank.

Frank van Vugt <ftm.van.vugt@foxi.nl> writes:
> Could someone point me to the formal reason why in:
> the cast to int[ ] of the result row is needed here:
> postgres=# select 1 = any((select ARRAY[1,2])::int[]);

ANY(SELECT ...) normally means searching down the rows of the select
result, where the select is expected to return a single column matching
the type of the LHS.  Searching across the elements of an array requires
that the top-level argument of ANY() not be a SELECT.  You stuck a cast
in there, which satisfies the syntactic restriction, but realize that
you've broken the ability to search multiple rows of the select result.
The ANY argument is now expected to return a single scalar value of an
array type, where the array elements can be compared to the LHS.

In the particular example here, SELECT is just a waste of typing.
But I assume it's a dumbed-down example.  Depending on what you're
really doing, it might be sensible to use
ANY(SELECT UNNEST(arrayvalue) FROM ...)
if you're trying to search through elements of a column of array values.

            regards, tom lane

Hi Tom,

> ANY(SELECT ...) normally means searching down the rows

ah, yeah, but I was focussing here more on the ANY(<array>) from documentation
paragraph 9.21.3 (postgresql v9.1.1):

    expression operator ANY (array expression)

> where the select is expected to return a single column matching the type
> of the LHS

I understand. Here though, I was in a situation where I knew the select would
return a single row with a single array typed column

> You stuck a cast in there, which satisfies the syntactic restriction,
> but realize that you've broken the ability to search multiple rows of
> the select result.

clear, which in a way matched what I was trying to achieve

So, basically, an ANY(...) construct will favor the ANY(SELECT...) over the
ANY(<array>) by maintaining the notion of getting a set of rows from the inner
expression, even when written as ANY((SELECT...))? Seems to make sense ;)

> In the particular example here, SELECT is just a waste of typing.
> But I assume it's a dumbed-down example.

oh, definitely, but you nailed it already in your next remark:

> Depending on what you're really doing, it might be sensible to use
> ANY(SELECT UNNEST(arrayvalue) FROM ...) if you're trying to search
> through elements of a column of array values.

right, 'unnest'..... see, I'm not using array's enough, this got added
yesterday, right ? ;)

Thanks for you response!




--
Best,




Frank.