libpq Prepared Statement with dynamic IN operator
Dear All, I am having a hard time figuring out how prepare and execute a Prepared Statement with an "IN" operator in the WHERE clause using libpq. The total elements that will be passed to IN operator is dynamic and varied at runtime. here is an example query: select payload_id,ptime,frequency from codecs where tag IN (‘G729’,’GSM’); The number of elements to filter for with this particular example is 2 but this varies at runtime (between 1 to 127 elements). I would like to know what’s the proper syntax of the (char *query) parameter when calling the PQprepare() function. I tried “select payload_id,ptime,frequency from codecs where tag=ANY(?)” but failed Regards, Dave
Re: libpq Prepared Statement with dynamic IN operator
Hi David, I tried both and I am getting syntax error. char *query="select codec_id,fs_name,pt from codec_defs where pt = ANY(string_to_array(?, ','))"; OR char *query="select codec_id,fs_name,pt from codec_defs where pt = ANY(?::text)"; PGresult *res=PQprepare(conn,"codecs",query,1,NULL); Dave, On Tuesday, November 24, 2020, 11:28:07 AM PST, David G. Johnston wrote: On Tue, Nov 24, 2020 at 12:14 PM Dave Greeko wrote: Dear All, I am having a hard time figuring out how prepare and execute a Prepared Statement with an "IN" operator in the WHERE clause using libpq. The total elements that will be passed to IN operator is dynamic and varied at runtime. here is an example query: select payload_id,ptime,frequency from codecs where tag IN (‘G729’,’GSM’); The number of elements to filter for with this particular example is 2 but this varies at runtime (between 1 to 127 elements). I would like to know what’s the proper syntax of the (char *query) parameter when calling the PQprepare() function. I tried “select payload_id,ptime,frequency from codecs where tag=ANY(?)” but failed ANY wants an array - you can either pass an array input literal and do "?::text[]" or you can pass a probably easier to write "csv" value and write "ANY(string_to_array(?, ','))". David J.
Re: libpq Prepared Statement with dynamic IN operator
I am sorry I used different query in my my last reply and yes you are correct
Tom. Using the $1 worked and the back-end indeed prepared the statement
successfully but this will force me to do some work on the input array that
contains the dynamic elements to comply with string_to_array delimiter when
calling PQexecPrepared(). I would really like to just pass an array of filters
of type (const char* const*) to PQexecPrepared's paramValues[] parameter
instead of making it some sort of csv string.
Here is a sample working code:
char *query="select codec_id,fs_name,pt from codec_defs where fs_name =
ANY(string_to_array($1, ','))";
PGresult *res=PQprepare(conn,"codecs",query,1,NULL);
char *input[1] ={"G729,PCMU"};
PQclear(res);
res=PQexecPrepared(conn,"codecs",1,(const char* const*)input,NULL ,NULL,0);
//The second version( ANY($1::text[] )
char *query="select codec_id,fs_name,pt from codec_defs where fs_name =
ANY($1::text[])";
//this gets prepared successfully
PGresult *res=PQprepare(conn,"codecs",query,1,NULL);
char *input[2] ={"G729","PCMU"};
PQclear(res);
//the below generate an erro:
res=PQexecPrepared(conn,"codecs",2,(const char* const*)input,NULL ,NULL,0);
//PQexecPrepared() generates Error:
ERROR: bind message supplies 2 parameters, but prepared statement "codecs"
requires 1
On Tuesday, November 24, 2020, 01:18:15 PM PST, Tom Lane
wrote:
Dave Greeko writes:
> I tried both and I am getting syntax error.
> char *query="select codec_id,fs_name,pt from codec_defs where pt =
> ANY(string_to_array(?, ','))";
> OR
> char *query="select codec_id,fs_name,pt from codec_defs where pt =
> ANY(?::text)";
> PGresult *res=PQprepare(conn,"codecs",query,1,NULL);
Well, your first problem is that "?" is not the parameter symbol
understood by libpq+backend. Try "$1". The other problem,
at least for the second version of that, is that you want to be
passing a text array not a single text value --- so it needs to
look more like "where pt = ANY($1::text[])".
regards, tom lane
Re: libpq Prepared Statement with dynamic IN operator
Many thanks for the clarification David. I wish there is a way without
touching my input array but at least based on your feedback I can avoid the
call to string_to_array call in the query. I tried it and it worked but I still
have to permute the C array. Here is what I have working so far:
char *query="select codec_id,fs_name,pt from codec_defs where fs_name =
ANY($1::text[])";
PGresult *res=PQprepare(conn,"codecs",query,1,NULL);
char *input[1] ={ "{G722,PCMU}" }; //this will be dynamic at run-time
res=PQexecPrepared(conn,"codecs",1,(const char* const*)input,NULL ,NULL,0);
Dave.
On Tuesday, November 24, 2020, 04:13:06 PM PST, David G. Johnston
wrote:
The convention here is to inline or bottom-post, not top-post.
On Tue, Nov 24, 2020 at 3:47 PM Dave Greeko wrote:
I would really like to just pass an array of filters of type (const char*
const*) to PQexecPrepared's paramValues[] parameter instead of making it some
sort of csv string.
//The second version( ANY($1::text[] )
char *query="select codec_id,fs_name,pt from codec_defs where fs_name =
ANY($1::text[])";
//this gets prepared successfully
PGresult *res=PQprepare(conn,"codecs",query,1,NULL);
char *input[2] ={"G729","PCMU"};
PQclear(res);
//the below generate an erro:
res=PQexecPrepared(conn,"codecs",2,(const char* const*)input,NULL ,NULL,0);
//PQexecPrepared() generates Error:
ERROR: bind message supplies 2 parameters, but prepared statement "codecs"
requires 1
I don't use the C API myself but a quick observation is that you specified
nParams=1 during prepare and nParams=2 during execute, so there is no way it is
going to work. nParams=1 is correct for both - you must only pass a single
value to the backend, that value must be of type "text array" (however one does
that here). Since you've chosen not to provide a data type OID that would
mean: "If paramTypes is NULL, or any particular element in the array is zero,
the server assigns a data type to the parameter symbol in the same way it would
do for an untyped literal string.". As the server will never assign an untyped
literal string to be an array the best you can do without an OID here is supply
a literal that can be cast directly to "text[]"
(https://www.postgresql.org/docs/current/arrays.html#ARRAYS-INPUT) - and then
write the cast into the sql query, all of which is probably only a nominal
difference from using "csv" and the string_to_array function. Even if you do
specify whatever oid is "text[]" all you really avoid is the cast in the query
- the API is expecting text in the values argument, not an "array object" (or
the equivalent in C). That said, if you can import a client-side header that
provides a helper function for this array-to-text conversion (idk?) then
passing the data becomes easier and you just need to decide whether to add a
cast in the SQL or provide the OID.
David J.
