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@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9