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.

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:

  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.

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.

About these ads

Nerd.

Tagged with: , , , ,
Posted in Coding
One comment on “Editing result set using libpq functions
  1. [...] wrote how to modify result set in underlying libpq’s PGresult object with new [...]

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Archives
Map of wandering
Follow

Get every new post delivered to your Inbox.

Join 80 other followers

%d bloggers like this: