1

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
Opux
  • 702
  • 1
  • 10
  • 30
  • That depends: Are you sending the parameters in text or binary format? Do you specify their types or not? Maybe it would help if you add a code sample to the question. – Laurenz Albe Jan 18 '19 at 15:53
  • @LaurenzAlbe The question shows I'm specifying types. So far I'm doing it in "text" format, but another part of the question is that I don't know if I should be doing "binary" and in what conditions. I'll work on code snippets. – Opux Jan 18 '19 at 15:57
  • Sorry, I should have been more specific: how do the `paramTypes` and `paramFormats` arguments to `PQexecParams` look? – Laurenz Albe Jan 18 '19 at 16:04
  • @LaurenzAlbe I've included snippets explaining what I'm doing and what I want to do (but the short answer is that those parameters are `NULL`) – Opux Jan 18 '19 at 16:14

1 Answers1

0

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • I could have sworn that the `::text` was essential, but I can't duplicate the error that I got before. In any case, is this any better than https://stackoverflow.com/a/36930781/1676382? It seems the engine still has to parse a string and then there's the danger of what happens if the delimiter character appears in one of the values – Opux Jan 18 '19 at 19:25
  • Then you have to escape the entry with double quotes, and double quotes inside the entry with backslashes: `{entry one,entry two,"evil{,}\"entry"}` – Laurenz Albe Jan 22 '19 at 08:02
  • My position here is to end shortly and my next won't involve any SQL at all, so I wont have a chance to really test it. My only challenge is: are you sure that covers all bases? I seem to remember looking into PostgreSQL escaping and found a case where it could all be unreliable. – Opux Feb 05 '19 at 20:14
  • I am pretty sure that is correct, but I agree that it is not nice to have to escape strings "by hand". – Laurenz Albe Feb 06 '19 at 07:13