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

Started by Yan Cheng Cheokover 16 years ago4 messagesgeneral

Jump to latest

Yan Cheng Cheok

yccheok@yahoo.com

over 16 years ago

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*?

Thanks and Regards
Yan Cheng CHEOK

--- On Thu, 1/7/10, Yan Cheng Cheok <yccheok@yahoo.com> wrote:

Show quoted text

From: Yan Cheng Cheok <yccheok@yahoo.com>
Subject: Re: [GENERAL] PostgreSQL Write Performance
To: "Greg Smith" <greg@2ndquadrant.com>
Cc: "Dann Corbit" <DCorbit@connx.com>, pgsql-general@postgresql.org
Date: Thursday, January 7, 2010, 1:17 PM
Thanks for the valuable advice! Will
take them into consideration seriously.

From my point of view, my current requirement is limited by

so-called "overhead" during communication with database. See
the following result from SQL Shell :

SemiconductorInspection=# \timing on
Timing is on.
SemiconductorInspection=# ;
Time: 0.660 ms
SemiconductorInspection=# ;
Time: 0.517 ms
SemiconductorInspection=# ;
Time: 2.249 ms
SemiconductorInspection=#

I assume there shall be no hard disc activity involved, as
I am sending "empty" SQL statement over.

Thanks and Regards
Yan Cheng CHEOK
--- On Thu, 1/7/10, Greg Smith <greg@2ndquadrant.com>
wrote:
From: Greg Smith <greg@2ndquadrant.com>
Subject: Re: [GENERAL] PostgreSQL Write Performance
To: "Yan Cheng Cheok" <yccheok@yahoo.com>
Cc: "Dann Corbit" <DCorbit@connx.com>,

pgsql-general@postgresql.org

Date: Thursday, January 7, 2010, 12:49 PM
Yan Cheng Cheok wrote:

The time taken to perform measurement per unit is

in

term of ~30 milliseconds. We need to record down the
measurement result for every single unit. Hence, the

time

taken by record down the measurement result shall be

far

more less than milliseconds, so that it will have

nearly 0

impact on the machine speed (If not, machine need to

wait

for database to finish writing, before performing
measurement on next unit)

Saving a piece of data to a hard disk permanently

takes a

few milliseconds. As pointed out already, exactly

how

many depends on the drive, but it's probably going to

be 8ms

or longer on your system. There are a few options
here:

1) Add a battery-backed write caching controller to

your

system. Then the battery will help make sure the

data

doesn't get lost even if the power goes out before

the

driver writes it out. This will cost you around

$300.

2) Use some other type of faster storage, such as a

SSD

drive that has a battery on it to cache any

unfinished

writes. Probably also going to be around that

price,

the cheaper SSDs (and some of the expensive ones)

don't take

data integrity very seriously.

3) Write the data to a flat file. Periodically

import

the results into the database in a batch.

The thing you should realize is that using (3) is

going to

put you in a position where it's possible you've told

the

machine the measurement was saved, but if the system

crashes

it won't actually be in the database. If you're

saving

to a flat file now, you're already in this

position--you

can't write to a flat file and make sure the result is

on

disk in less than around 8ms either, you just

probably

haven't tested that out yet. Just because the write
has returned successfully, that doesn't mean it's

really

stored permanently. Power the system off in the

window

between that write and when the memory cache goes out

to

disk, and you'll discover the data missing from the

file

after the system comes back up.

If you're OK with the possibility of losing a

measurement

in the case of a system crash, then you should just

write

measurements to a series of flat files, then have

another

process altogether (one that isn't holding up the

machine)

load those files into the database. The fact that

it

takes a few ms to write to disk is a physical

limitation you

can't get around without using more expensive hardware

to

improve the situation. If you haven't been seeing

that

in your app already, I assure you it's just because

you

haven't looked for the issue before--this limitation

on disk

write speed has been there all along, the database is

just

forcing you to address it.

-- Greg Smith
2ndQuadrant Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com
www.2ndQuadrant.com

Merlin Moncure

mmoncure@gmail.com

over 16 years ago

In reply to: Yan Cheng Cheok (#1)

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

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

Peter Geoghegan

pg@bowt.ie

over 16 years ago

In reply to: Merlin Moncure (#2)

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

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

mmoncure@gmail.com

over 16 years ago

In reply to: Peter Geoghegan (#3)

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

On Thu, Jan 7, 2010 at 11:30 AM, Peter Geoghegan
<peter.geoghegan86@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