Re: the IN clause saga

On Wed, 23 Jul 2003, Fernando Nasser wrote:

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

The prepare+execute is no different than the select.  It's not going to
perform any magic for you.

template1=# PREPARE stmt (integer[]) AS SELECT 1 WHERE 1 IN ($1);
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



template1=# PREPARE stmt (integer) AS SELECT 1 WHERE 1 IN ($1);
PREPARE
template1=# EXECUTE stmt(array[1,2]);
ERROR:  Parameter $1 of type integer[] cannot be coerced into the expected
type integer
        You will need to rewrite or cast the expression
IN:  transformExecuteStmt (analyze.c:2553)
ERROR:  Parameter $1 of type integer[] cannot be coerced into the expected
type integer
        You will need to rewrite or cast the expression


I hope this helps.

Kris Jurka