Postgres Pro
Downloads
Home   >   mailing lists

Thread: libpq Prepared Statement with dynamic IN operator

libpq Prepared Statement with dynamic IN operator

From
Dave Greeko
Date:
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

From
"David G. Johnston"
Date:
On Tue, Nov 24, 2020 at 12:14 PM Dave Greeko <davegreeko@yahoo.com> 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

From
Dave Greeko
Date:
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 <david.g.johnston@gmail.com> wrote:


On Tue, Nov 24, 2020 at 12:14 PM Dave Greeko <davegreeko@yahoo.com> 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

From
Tom Lane
Date:
Dave Greeko <davegreeko@yahoo.com> 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

From
Dave Greeko
Date:
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 <tgl@sss.pgh.pa.us> wrote:


Dave Greeko <davegreeko@yahoo.com> 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

From
"David G. Johnston"
Date:
The convention here is to inline or bottom-post, not top-post.

On Tue, Nov 24, 2020 at 3:47 PM Dave Greeko <davegreeko@yahoo.com> 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.

Re: libpq Prepared Statement with dynamic IN operator

From
Dave Greeko
Date:
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 <david.g.johnston@gmail.com> wrote:


The convention here is to inline or bottom-post, not top-post.

On Tue, Nov 24, 2020 at 3:47 PM Dave Greeko <davegreeko@yahoo.com> 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.