Many of us use libpq client library to access PostgreSQL. And so we do in our PostgresDAC component suite. Very often you don’t want to refresh result set on client side after insert\update\delete some row especially if result set is huge. And then it is useful to simply change values in underlying libpq’s PGresult object with new ones.
UPDATE
In this case we must internally execute UPDATE statement and populate PGresult using PQsetValue routine.
“A piece of cake!” – one may think. But! You always must remember of three issues:
- Column DEFAULT values (including SERIAL and BIGSERIAL cases);
- Triggers;
- Rules.
They may change values to be placed to PGresult on server side. Thus it’s wise to execute UPDATE … RETURNING statement and update client data with values returned.
INSERT
The same as above for values. But you may ask: “How I’m supposed to add another record to PGresult?” Don’t worry, PQsetValue handles this well. Let’s have a look at Delphi code:
var
tup_num, f_num: integer;
Res: PPGresult; //pointer to PGresult
//...
tup_num := PQntuples(Res);
for f_num := 0 to PQnfields(Res) - 1 do
PQsetValue(Res, tup_num, f_num, <value for f_hum>, <length of value>);
//...
When PQsetValue first called function will automatically grow the PGresult’s internal tuples array as needed. That’s why we use tup_num variable, because result of PQntuples will differ after growing.
DELETE
Sadly, there is no easy way for this case. I mean there is no libpq’s PQdeleteTuple or something like this. Probably you will need to store somewhere deleted items and ignore them during display.
That’s all.
Pasha Golub's Blog 