Thread: Libpq binary mode SELECT ... WHERE ID IN ($1) Question
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 );
You could try to prepare a query like this:
select name from foo where id=any($1);
and then pass the array of integers as $1 (although, I don't know how
you can do that as I've never used the C interface of libpq).
On Fri, Nov 13, 2009 at 1:34 AM, Matt Sanchez <matt-sanchez@comcast.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 );
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
On Fri, Nov 13, 2009 at 1:34 AM, Matt Sanchez <matt-sanchez@comcast.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
Merlin Moncure wrote:
> On Fri, Nov 13, 2009 at 1:34 AM, Matt Sanchez <matt-sanchez@comcast.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
>
>
/* For prepared statements, use this */
PQparamExecPrepared(conn, param, "getname", 1);
/* Here is another way of doing it. You can putf more than
* one array element at a time. Also, PQexecf is handy
* but has no support for prepared statements :(
*/
PGresult *res;
PGarray arr = {0};
arr.param = param = PQparamCreate(conn);
PQputf(arr.param, "%int4 %int4 %int4 %int4", 3, 5, 6, 8);
res = PQexecf(conn,
"select name from foo where id in (%int4[])", &arr);
PQparamClear(arr.param);
--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/