OCIDefineArrayOfStruct() or OCIBindArrayOfStruct equivalent in PostgreSQL

Question

I am currently working updating client side code from OCI to libq. I am aware that OCIDefineArrayOfStruct() and OCIBindArrayOfStruct() are used to improve performance by enabling multi-row, multi-column fetch.

Is there any way to do same using libpq?.

Answer 1

PostgreSQL does not have a concept of bulk fetch or bulk DML statements that uses arrays on the client side.

To fetch rows in batches, use a cursor and the FETCH statement. All fetched rows are returned in a single PGresult, and you can iterate through them conveniently. To insert rows in batches, use a multi-line INSERT statement or COPY.

You will have to rewrite code that uses the C API from scratch, because the C APIs of Oracle and PostgreSQL are quite different. The good news is that PostgreSQL's API is much more convenient than the Byzantine OCI.