I am using libpq to communicate with a postgres database. In postgres, when using the functions PQexecParams and PQprepare, placeholders may be used and one may bind to values via a const char pointer.
I am having issues finding a detailed explanation on this feature. E.g. consider a query like this:
INSERT INTO ... VALUES ($1, $2, $3, $4), ($1, $5, $3, $6), ($1, $7, $3, $8)
where $1 and $3 comes from a single value used in multiple rows, e.g. an int or a double and $2, $5, $7 stem from a collection like a vector and $6, $7, $8 from another collection.
Are the placeholders parsed on query execution to match the const char** offset or is this purely index based, i.e. index of placeholder matches the offset on the char** pointer? I.e., assume
const char** data
is passed into PQexecParams or PQprepare as the data argument. The following mapping valid:
data[0] -> $1
data[1] -> $2
data[3] -> $3
...
data[7] -> $8
Assume that I switch two placeholders in my statement. Say, I switch the first two placeholders:
INSERT INTO ... VALUES ($2, $1, $3, $4), ($1, $5, $3, $6), ($1, $7, $3, $8)
Note that $2 now comes first and $1 second. When index-based, I expect the following mapping:
data[0] -> $2
data[1] -> $1
...
When parsed, I expect the original mapping to be still valid, i.e.
data[0] -> $1
data[1] -> $2
which one is true?
There is definitely something going on there that goes beyond counting placeholders and their positions: I have the following query to a table with two columns col1, col2:
INSERT INTO mytable (col1, col2) VALUES ($1, $2);
This works with both postgres and sqlite. When I replace $2 with $4, it works find with sqlite, but I get an error message from libpq:
INSERT INTO mytable (col1, col2) VALUES ($1, $4);
The error message being:
Status code: 7
error string: PGRES_FATAL_ERROR
error message: ERROR: could not determine data type of parameter $2