Обсуждение: 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/