Re: Prepared statement parameters for an 'IN ()' clause

Jason L. Buberel wrote:
> Can someone point me to an example of creating a prepared
> statement for a query with an 'IN' clause?
>
> The query looks like this:
>
> select value from table where
> state = $1 and city = $2 and zip = $3 and
> date in ( $4 );
>
> For the prepared statement, I have tried:
>
> prepare st1(text, text, text, text[] );
>
> Then invoked it as:
>
> execute st1('CA', 'SUNNYVALE', '94086',
> '{2007-10-01,2007-09-25,2007-09-15}' );
>
> But the use of the text array as input parameter does not
> seem to be correctly used in the 'IN' clause. My query
> consistently returns no results.

Two things:

a) the fourth parameter of the function should be declared as
date[] and not as text[].

b) use =ANY instead of IN

Example:

CREATE TABLE t (id serial PRIMARY KEY, datum date);

INSERT INTO t (datum)
VALUES ('2000-01-01'), ('2001-01-01'), ('2002-01-01');

CREATE FUNCTION f(date[]) RETURNS SETOF integer
LANGUAGE sql STABLE STRICT AS
$$SELECT id FROM t WHERE datum =ANY ($1)$$;

SELECT * FROM f('{2001-01-01,2006-01-01}');
f
---
2
(1 row)

Yours,
Laurenz Albe