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

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