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