PostgreSQL: relationship between types given w/placeholders and types in PQexecPrepared() paramValues parameter

Question

If I have a placeholder given as $1::text, does that means the corresponding paramValues that I pass to PQexecPrepared() needs to be a C-style string? So far I've done it that way and so far it's worked. (And so far, I've needed to give ::text along w/the placeholder or I get an error). But what if I declare the placeholder as $1:int? Can I still give a C-style string in the paramValues? Should I give a C-style string? The docs on this are a bit vague and aren't very clear on what to do w/the paramTypes parameter of PQprepare() which I've left NULL anyway, so far.

More importantly, what if I need to express an array, like as $1::text[]? The docs say nothing of this and the solutions I've seen so far don't seem very efficient or even very reliable, such as https://stackoverflow.com/a/36930781/1676382. So far I've been giving the values to PQexecPrepared() in "text" format, but should I be switching to "binary" and in what conditions?

So far I've been able to get away w/some pretty simple queries:

const char *query = "SELECT * FROM table WHERE col=$1::text";
const void *types = NULL;    //Wish I knew what could be assigned to an Oid
PQprepare(conn, name, query, 1, static_cast<const Oid *>(types));

...

const int *lengths = NULL;      //It doesn't seem like these vars are used for anything right now
const int *formats = NULL;
int result_format = 0;
//values is a const char * const * and is the return value of QScopedArrayPointer <char*>::data()
res = PQexecPrepared(conn, name, 1, values, lengths, formats, result_format);

Now so far this works, but what if I wanted to have

WHERE col = ANY ($1::text[])

? How would I express values that is passed to PQexecPrepared()?

My project is in GNU C++ using PostgreSQL 9.6.

Answer 1

If you sent untyped string arguments like in your example, they will have type unknown and converted to the proper type, if possible. This is quite similar to string literals in SQL statements.

It should not be necessary to cast to text in the SQL statement. I'd be curious what the type of col is if you find that cast necessary.

If you want to pass a text[] in text format, simply use its string representation, for example

{first element,two,three}

This will also work for integers and other types; just use the text representation of the value.

You can also use the paramTypes argument to PQexecParams to specify which type it is explicitly. The possible values are in the oid column of pg_type, and they are constant for system types.