Re: Use C++ to iterate integer array returned from stored procedure

On Thu, Jan 7, 2010 at 1:44 AM, Yan Cheng Cheok <yccheok(at)yahoo(dot)com> wrote:
> Sorry if this question had been asked before. Although I had googled, but find no answer.
>
> I try to use C++, to iterate the array returned from stored procedure.
>
>    std::stringstream ss;
>    ss << "SELECT * FROM get_array_test()";
>    res = PQexec(conn, ss.str().c_str());
>
>    int nrows = PQntuples(res);
>    int nfields = PQnfields(res);
>    printf("number of rows returned = %d\n", nrows);
>    printf("number of fields returned = %d\n", nfields);
>
>    for (int r = 0; r < nrows; r++) {
>        for (int n = 0; n < nfields; n++)
>            printf(" %s = %s(%d),",
>            PQfname(res, n),
>            PQgetvalue(res, r, n),
>            PQgetlength(res, r, n));
>            printf("\n");
>    }
>
> Here is my result :
>
> number of rows returned = 1
> number of fields returned = 1
>  get_array_test = {100,200,300}(13),
>
> Here is my stored procedure :
>
> CREATE OR REPLACE FUNCTION get_array_test()
>  RETURNS integer[] AS
> DECLARE
>    i int4[];
> BEGIN
>    i[1] = 100;
>    i[2] = 200;
>    i[3] = 300;
>    return i;
> END;
>
> Is there any way, I can obtain the stored procedure result in c/c++ int array, instead of 1 line of char*?

not exactly. however you do have the libpqtypes library which extends
libpq to deal with arrays:

http://libpqtypes.esilo.com/
note, libpqtypes requires postgres 8.4 (or 8.3 with patched libpq).

PGarray arr;
int ntups;
res = PQexecf(conn, "SELECT * FROM get_array_test()");
PQgetf(res, 0, "%int4[]", 0, &arr);
ntups = PQntuples(arr.res);

for(i=0; i<ntups; i++)
{
PGint4 val;
PQgetf(arr.res, i, "%int4", 0, &val);
printf("val=%d\n", val);
}

PQclear(res);
PQclear(arr.res);

merlin