Re: libpq Prepared Statement with dynamic IN operator

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

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