I'm using libpq (but I am potentially be ready to switch to other library)
I have a bunch of similar INSERT queries I want to make, they are different only in values between them.
I'd like to use prepared statements to avoid issues with escaping fields. I also want to send them in one batch so that I don't spend roundtrip for each query.
I know only this options:
Use PQExec()
with multiple insert, divided by semicolon:
This doesn't allow prepared statements, so I need to create query dynamically
Use PQexecParams()
for each query separately:
This has problems of multiple roundtrips spent
Use PQprepare()
+ PQExecPrepared()
for each query separately:
Same problem as previous
Use PQexecParams()
for single query with several inserted lines e.g INSERT INTO tablename (a,b,c) VALUES ("A", "B", "C"), ("aa", "bb", "cc")
:
It's a bit better than the first option, because it allow not to escape arguments, but still requires building query dynamically (and the worst part you'll have to increment number of arguments ($1, $2, $3) for first line but ($4,$5,$6) for the second, etc)
So, how to avoid these drawbacks?