Re: the IN clause saga

OK, I got the message that the parameter can only be used in the set
predicates like ANY but not in the IN predicate.

But I never wanted to generate a 'in (array[1,2,3])'.

I thought specifying IN (?) and doing a prepare with integer[] (where
integer[] is ARRAY[1,2,3]) could produce the equivalent of 'in (1, 2,
3)'. It is just a PREPARE+EXECUTE syntax, not the SELECT command's IN
predicate itself.

You've mentioned a possible ambiguity. Can anyone provide me with an
example so I can understand it better? Please mind that I am only
talking about PREPARE+EXECUTE syntax, not the SQL command itself.

Thanks for the clarifications.

Regards,
Fernando

Joe Conway wrote:
> Kris Jurka wrote:
>
>> Perhaps these cases would also be illustrative as I believe that is what
>> Fernando was suggesting.
>>
>> template1=# select 1 where 1 in (array[1,2,3]);
>> ERROR: Unable to identify an operator '=' for types 'integer' and
>> 'integer[]'
>> You will have to retype this query using an explicit cast
>> IN: op_error (parse_oper.c:608)
>> ERROR: Unable to identify an operator '=' for types 'integer' and
>> 'integer[]'
>> You will have to retype this query using an explicit cast
>
>
> Right, as I said, this syntax is trying to compare the scalar value to
> the entire array value, not its elements.
>
>> template1=# select 1 where 1 in ([1,2,3]);
>> ERROR: syntax error at or near "["
>> IN: yyerror (scan.l:596)
>> ERROR: syntax error at or near "[" at character 22
>
>
> And this syntax was never valid and still isn't.
>
> Joe
>
>

--
Fernando Nasser
Red Hat Canada Ltd. E-Mail: fnasser(at)redhat(dot)com
2323 Yonge Street, Suite #300
Toronto, Ontario M4P 2C9