Thread: Is there any ways to pass an array as parameter in libpq?
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. So I plan to write a pl/pgsql function (procedure) to perform the whole updating and inserting.
But I don't know how to pass the array to the procedure through libpq. It seems the only way is to using libpq to do updating and inserting separately. Like this
for(;;) {
/* receive data */
/* libpq updating */
PQexec(...);
PQprepare(...);
for (i=0; i<n; i++) {
/* libpq inserting */
PQexecPrepared()...
}
}
The PQprepare function has to be called in each loop.So my question is that whether there is a method to pass the array as parameter to libpq?
Best regards,
Xun
Messenger保护盾2.0，更安全可靠的Messenger聊天！ 现在就下载！
On Tue, 2009-10-27 at 08:07 +0800, ChenXun wrote:
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
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
2009/10/26 ChenXun <p.smasher@hotmail.com>:
> 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. So
> I plan to write a pl/pgsql function (procedure) to perform the whole
> updating and inserting.
> But I don't know how to pass the array to the procedure through libpq. It
> seems the only way is to using libpq to do updating and inserting
> separately. Like this
>
> for(;;) {
> /* receive data */
>
> /* libpq updating */
> PQexec(...);
>
> PQprepare(...);
> for (i=0; i<n; i++) {
> /* libpq inserting */
> PQexecPrepared()...
> }
> }
>
> The PQprepare function has to be called in each loop.So my question is that
> whether there is a method to pass the array as parameter to libpq?
check out libpqtypes (http://libpqtypes.esilo.com/). It's exactly what
you want. We expose sending arrays/composites directly in
paramaterized fashion in binary (no escaping). You still have to loop
because a C array is not directly compatible to postgres array.
PGint4 i;
PGarray arr;
PGparam *param;
arr.ndims = 0;
arr.param = PQparamCreate(conn);
for(i=0; i < 1000; i++)
PQputf(arr.param, "%int4", i); // client side
param = PQparamCreate(conn);
PQputf(param, "%int[]", &arr); // client side
res = PQparamExec(conn, param, "insert into foo values ($1)", 0);
merlin
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 conver ting 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@postnewspapers.com.au
To: p.smasher@hotmail.com
CC: pgsql-general@postgresql.org
Date: Tue, 27 Oct 2009 13:14:37 +0800
On Tue, 2009-10-27 at 08:07 +0800, ChenXun wrote:
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。 了解详情。
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 conver ting 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@postnewspapers.com.au
To: p.smasher@hotmail.com
CC: pgsql-general@postgresql.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。 了解详情。