Re: libpq Prepared Statement with dynamic IN operator

[Dave Greeko <davegreeko@xxxxxxxxx>]

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@xxxxxxxxx> 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@xxxxxxxxx> 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.