Re: <int> = any((select <int[]>)) needs cast, why?

Frank van Vugt <ftm(dot)van(dot)vugt(at)foxi(dot)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