'libpq SELECT loop and UPDATE within the loop
I have a C++ program that does something like the following:
PGresult* res = PQexec(conn, "SELECT id, foo FROM tbl WHERE some_condition");
for (int r = 0; r < PQntuples(res); ++r) {
auto id = PQgetvalue(res, r, 0);
auto foo = PQgetvalue(res, r, 1);
cout << "Processing: " << foo << endl;
// process foo ...
res = PQexecParams(conn, "UPDATE tbl SET bar = $1 WHERE id = $2", args...);
}
If I comment out the PQexecParams
line, and say, there are three rows that match the some_condition
, then I see three "Processing" lines on stdout. However, with the PQexecParams
in there, only the first row gets processed and updated and the loop is then exited.
I believe this is because there is some interaction between UPDATE within a SELECT loop that basically cuts off the loop after the first UPDATE.
[As pointed out by @jjanes, the problem was my stoopid mistake of using the same variable name for the result of the SELECT and the UPDATE].
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|