Editing result set using libpq functions

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.


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:

  1. Column DEFAULT values (including SERIAL and BIGSERIAL cases);
  2. Triggers;
  3. 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.


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:

  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.


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.


One thought on “Editing result set using libpq functions

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s