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

On Wed, Oct 17, 2007 at 07:37:15AM -0700, 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 );

You're asking here whether "date" matches a single item named by
parameter "4".

> Any quick syntax fix for this?

Try:

  date = ANY ($4);

"ANY" treats its parameter as an array, so will accept an array as a
parameter.  I've not tried this on a large dataset, so I'm not sure what
sort of performance characteristics you should expect.


  Sam

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