Re: libpq Prepared Statement with dynamic IN operator

[Dave Greeko <davegreeko@xxxxxxxxx>]

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

Dave Greeko <davegreeko@xxxxxxxxx> 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