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

Sorry for not making the question clear.
The table is created like this,
create table prog (
id serial primary key,
pc_id integer,
start_time timestamp with time zone,
end_time timestamp with time zone,
...
);

A remote pc will send a bundle of data to my program after some time. The data is structured like
struct data {
char time[32];
...
}
struct data da[n];
where n is unknown, but it will not exceed 12. The pc_id is also sent.
What I should to is to:
1. update the end_time of the last existing record with the same pc_id (by checking the maximum value of id and null end_time)
2. Insert the received data into the table, set the start_time to be the da[i].time , and end_time to be da[i+1].time (except for the last one).

A simple procedure is written to deal with this. So I just need to pass the values in the array to the server as a whole.

I've found an ugly solution by converting the array to a string using sprintf().
Btw, it seems that the libpqtypes is what I need.

Thanks for your reply.
Best,
Xun
Subject: Re: [GENERAL] Is there any ways to pass an array as parameter in libpq?
From: craig(at)postnewspapers(dot)com(dot)au
To: p(dot)smasher(at)hotmail(dot)com
CC: pgsql-general(at)postgresql(dot)org
Date: Tue, 27 Oct 2009 13:14:37 +0800

On Tue, 2009-10-27 at 08:07 +0800, ChenXun wrote:

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.

You appear to be thinking of a table as an ordered list of data. It doesn't work like that. There is no "last record" in the table, and the records aren't in any particular order.

If you want to get them out of the database in a particular order you must specify that order with an ORDER BY clause in your SELECT statements. Otherwise they'll be returned in whatever order is quickest for the database - which will probably initially be the order you inserted them in, but that'll change over time.

I suspect you may be trying to do things in a way that's going to make things MUCH harder for you down the track.

You should just be able to do a parameterized INSERT INTO where you loop over the elements of the array in your code, feeding them in as query parameters. If you have too much data for that you could do a multi-record INSERT (say insert ten records at a time). If that still isn't good enough, then the network COPY protocol may be what you need. I really doubt, though, that you need to do anything more than loop over the array in your program and INSERT from it one-by-one within a transaction.

--

Craig Ringer

_________________________________________________________________
全新 Windows 7：寻找最适合您的 PC。了解详情。
http://www.microsoft.com/china/windows/buy/