Re: libpq Prepared Statement with dynamic IN operator

[Dave Greeko <davegreeko@xxxxxxxxx>]

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@xxxxxxxxx> wrote:

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