Skip site navigation (1) Skip section navigation (2)

Re: PQexecParams and IN query?

From: Gregory Stark <stark(at)enterprisedb(dot)com>
To: "Andrew McNamara" <andrewm(at)object-craft(dot)com(dot)au>
Cc: <pgsql-interfaces(at)postgresql(dot)org>
Subject: Re: PQexecParams and IN query?
Date: 2007-05-11 12:31:27
Message-ID: 87tzuj1qzk.fsf@oxford.xeocode.com (view raw or flat)
Thread:
Lists: pgsql-interfaces
"Andrew McNamara" <andrewm(at)object-craft(dot)com(dot)au> writes:

> I'm trying to use PQexecParams to do an "IN" query, where the set of
> values is supplied as a single parameter - essentially:
>
>     SELECT * FROM sometable WHERE colvalue IN $1
>
> With $1 as a set or array of some sort. Unfortunately, the above doesn't
> pass parsing.  Is there any way to do an IN query via PQexecParams that
> doesn't involve enumerating the set of values as individual parameters?

No but there is a way to do something equivalent with arrays:

SELECT * FROM sometable WHERE colvalue = any($1::integer[]);

I'm not sure which (if any) drivers have good support for arrays though. I
think with most if not all drivers you'll have to crunch your list of elements
down into a string argument of the form "{1,2,3}" (without the quotes). 

If you're using something more complex than integers then you'll have to deal
with quoting the array elements which will start to get annoying.

-- 
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com


In response to

Responses

pgsql-interfaces by date

Next:From: Tom LaneDate: 2007-05-11 13:08:01
Subject: Re: PQprepare question
Previous:From: Andrew McNamaraDate: 2007-05-11 12:03:32
Subject: PQexecParams and IN query?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group