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
