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

[Merlin Moncure]

On Thu, Jan 7, 2010 at 1:44 AM, Yan Cheng Cheok <ycc...@yahoo.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

--
Sent via pgsql-general mailing list (pgsql-...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Peter Geoghegan]

I suggest that you use libpqxx. libpqxx already has a utility template
function called separated_list() that lives in the pqxx namespace for
converting std containers into array literals. It also has a function
template called "from_string" that lives in the same place. I imagine
it's implemented in terms of stringstream though (otherwise, JTV would
have written a bunch of specialisations or overloads), and as such is
highly generic and unlikely to do what you want (I've just taken a
look at the doxygen generated docs on pqxx.org, I cannot confirm that
right now).

In any case, you are better of with pqxx. You can make some noise on
the pqxx mailing list if you feel that it ought to have this
functionality.

Regards,
Peter Geoghegan

[Merlin Moncure]

On Thu, Jan 7, 2010 at 11:30 AM, Peter Geoghegan
<peter.ge...@gmail.com> wrote:
> I suggest that you use libpqxx. libpqxx already has a utility template
> function called separated_list() that lives in the pqxx namespace for
> converting std containers into array literals. It also has a function
> template called "from_string" that lives in the same place. I imagine
> it's implemented in terms of stringstream though (otherwise, JTV would
> have written a bunch of specialisations or overloads), and as such is
> highly generic and unlikely to do what you want (I've just taken a
> look at the doxygen generated docs on pqxx.org, I cannot confirm that
> right now).
>
> In any case, you are better of with pqxx. You can make some noise on
> the pqxx mailing list if you feel that it ought to have this
> functionality.

libpqxx is good, but libpqtypes handling of arrays and composites is
far superior. honestly, libpqxx might want to consider wrapping
libpqtypes to bring full support for arrays into the library
(libpqtypes has solved this
(http://pqxx.org/development/libpqxx/ticket/97). Also libpqtypes has
no C++ dependency, and routes all data via binary which is much faster
for arrays.

merlin