Re: Is there any ways to pass an array as parameter in libpq?

2009/10/26 ChenXun <p(dot)smasher(at)hotmail(dot)com>:
> Hello,
>
> I'm starting to learn programming with libpq.
> In the main loop of my code, I will receive some data in the format of an
> array of a struct. The data will be inserted to the database, in different
> lines.
> I also need to update the last record in the table before the insertion. So
> I plan to write a pl/pgsql function (procedure) to perform the whole
> updating and inserting.
> But I don't know how to pass the array to the procedure through libpq. It
> seems the only way is to using libpq to do updating and inserting
> separately. Like this
>
> for(;;) {
> /* receive data */
>
> /* libpq updating */
>   PQexec(...);
>
>   PQprepare(...);
>   for (i=0; i<n; i++) {
>   /* libpq inserting */
>     PQexecPrepared()...
>  }
> }
>
> The PQprepare function has to be called in each loop.So my question is that
> whether there is a method to pass the array as parameter to libpq?

check out libpqtypes (http://libpqtypes.esilo.com/). It's exactly what
you want. We expose sending arrays/composites directly in
paramaterized fashion in binary (no escaping). You still have to loop
because a C array is not directly compatible to postgres array.

PGint4 i;
PGarray arr;
PGparam *param;

arr.ndims = 0;
arr.param = PQparamCreate(conn);

for(i=0; i < 1000; i++)
PQputf(arr.param, "%int4", i); // client side

param = PQparamCreate(conn);
PQputf(param, "%int[]", &arr); // client side

res = PQparamExec(conn, param, "insert into foo values ($1)", 0);

merlin