Re: Libpq binary mode SELECT ... WHERE ID IN ($1) Question

On Fri, Nov 13, 2009 at 1:34 AM, Matt Sanchez <matt-sanchez(at)comcast(dot)net> wrote:
> Hello,
>
> Suppose I have a table:
>        create table foo ( id int4, name varchar(50) );
>
> I want to prepare and execute in binary mode:
>        select name from foo where id in ($1);
>
> Execute works when I have a single value for $1, and treat it as a
> normal INT4. However, when I attempt to send multiple values for the
> parameter, I get no meaningful results.
>
> My code snippets are below.
>
> When setting up an array of numbers to pass as a parameter, is there
> something special that needs to be done? The documentation is quite
> vague; I did poke around the source and found in contrib some int_array
> code, but don't fully understand it.
>
> I suspect I need to do something like ...
> (a) when I prepare, do something to tell postgres that I will have an
> array of values, instead of a single value, and/or
> (b) perhaps encapsulate the array of integers in some manner.
>
> If I could be pointed to an example or documentation, it would be much
> appreciated.
>
> Thanks,
> Matt Sanchez
>
> The prepare code snippet:
>
> Oid oids[1] = { 23 };   //INT4OID
>
> result = PQprepare( pgconn, "getname",
> "select name from foo where id in ($1)"
> 1, oids );
>
> The execute code snippet:
>
> int     ids[4] = { 3, 5, 6, 8 };        // param values we want to setup
>
> /* convert numbers to network byte order ... */
>
> char *  paramvalues[1] = (char *) ids;
> int     paramlengths[1];
> int     paramformats[1] = { 1 };
>
> paramlengths[0] = = 4 * sizeof( int );
>
> result = PQexecPrepared( pgconn,
>        "getname",      // statement name
>        1,              // number of params
>        paramvalues,
>        paramlenths,
>        paramformats,
>        1 );

Here is how to do it with libpqtypes (http://libpqtypes.esilo.com/).
libpqtypes will reinvent the way you use libpq.

PGint4 i;
PGarray arr;
PGparam *param;

/* One dimesional arrays do not require setting dimesion info. For
* convience, you can zero the structure or set ndims to zero.
*/
arr.ndims = 0;

/* create the param object that will contain the elements */
arr.param = PQparamCreate(conn);

/* Use PQputf(3) to put the array elements */
for(i=0; i < 1000; i++)
PQputf(arr.param, "%int4", i);

/* The PGarray must be put into a PGparam struture. So far, only
* the array elements have been put. 'param' can continue to be
* used to pack more parameters. The array is now a single parameter
* within 'param'.
*/
param = PQparamCreate(conn);
PQputf(param, "%int[]", &arr);

/* no longer needed */
PQparamClear(arr.param);

/* send it up :-) */
PQparamExec(conn, param, "select unnest($1)", 1);

merlin