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