Tag: libpq

LibreOffice announces native support for PostgreSQL

Today I found out about new release of LibreOffice (version 3.5), which is now my choice instead of MS Office and it’s brother OpenOffice killed by Oracle.

And the most pleasant surprise is PostgreSQL native support added in this minor release.

I remember messages by Lionel Elie Mamane sent to pgsql-hackers list. And the problems LibO community was faced with. Kudos guys!

BTW, how did you manage cross platform compilation of libpq library, especially for MacOS? And how do you provide libpq.so on Macs?

Delete tuple from libpq’s PGresult

I wrote how to modify result set in underlying libpq’s PGresult object with new ones.

But there was no solution for deletion. Here I want to propose one solution. The main idea is to copy existing PGresult content to the new record except deleted tuple. For this we will use PQcopyResult function. Delphi code looks like this:

  CurrentRecNum: LongInt;
  DeletedRecNum: LongInt;
  ATempCopyStmt: PGResult;
  fval: PAnsiChar;
  flen, i, j: integer;

     //FStatement - is our active result set
     ATempCopyStmt := PQcopyResult(FStatement, PG_COPYRES_ATTRS);
     if not Assigned(ATempCopyStmt) then
       raise Exception.Create('Cannot copy results');
     j := 0;
     for CurrentRecNum := 0 to PQntuples(FStatement) - 1 do
       if CurrentRecNum = DeletedRecNum then 
          Continue; //exclude row from new set
       for i := 0 to PQnfields(FStatement) - 1 do
           fval := PQgetvalue(FStatement, j, i);
           flen := PQgetlength(FStatement, j, i);
           if PQsetvalue(ATempCopyStmt, j, i, fval, flen) = 0 then
             raise Exception.Create('Refresh for deleted fiels failed');
     PQclear(FStatement); //clear old result set 
     FStatement := ATempCopyStmt; //switch to the new

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.

Windows path in libpq connection control functions

I already wrote a post about using Windows paths in SQL statements. But today I was playing with SSL connections to PostgreSQL from MicroOLAP Database Designer for PostgreSQL and found one interesting thing:

warning Windows user! Replace your usual slashes (backslashes, actually) with the slashes like this one: ‘/’, in the file system path parameters passed to libpq database connection control functions, e.g. PQconnectdbParams, PQconnectdb, PQconnectStartParams etc.!


PQconnectdb(“dbname=’carsales’ host=’localhost’ port=5434 user=’sslguy’ password=” sslmode=’require’ sslcert=’C:\MySSL\postgresql.crt’ sslkey=’C:\MySSL\postgresql.key'”);


PQconnectdb(“dbname=’carsales’ host=’localhost’ port=5434 user=’sslguy’ password=” sslmode=’require’ sslcert=’C:/MySSL/postgresql.crt’ sslkey=’C:/MySSL/postgresql.key'”);

PostgreSQL v9.0.0 client libraries update

We (in MicroOLAP Technologies) just finished client libraries update for 9.0.0 version.

Quick facts:

  • Two packages were updated:
    • Deployment libraries shipped with PostgreSQL installation
    • Deployment libraries built with MinGW environment
  • Both packages are built against 9.0.0 version of PostgreSQL
  • Dump and restore libraries for 8.4.x, 8.3.x and 8.2.x PostgreSQL branches are included into “Deployment libraries built with MinGW environment” package
  • Included dump and restore libraries (pg_dump-x.x.x.dll, pg_restore-x.x.x.dll) may be used with PaGoDump and PaGoRestore utilities respectively.

Have a nice day!

libpq stands for…

I always was interested why PostgreSQL client library named “libpq”. Of course I guessed that “lib” means “library”, but there are two more characters left. I supposed that “p” is some how connected with “Postgres”. But I have no any idea why “q” appeared in the library name.

Now I know the answer. Thanks to Bruce Momjian:

Libpq is called ‘libpq’ because of the original use of the QUEL query language, i.e. lib Post-QUEL. We have a proud backward-compatibility history with this library.

PostgresDAC 2.5.5 released

This release introduces the latest 8.4.3 libraries, improvements for user-defined types support, savepoint functionality support, new behaviour options for TPSQLDataset descendants and several bug fixes.

Full changelog:
[!] v8.4.3 client libraries added
[!] v8.4.3 dump & restore libraries (pg_dump.dll, pg_restore.dll)
[+] v8.2.16 and v8.3.10 dump & restore libraries (pg_dump_8.x.x.dll, pg_restore_8.x.x.dll)
[+] dsoEmptyCharAsNull option added to TPSQLDataaset.Options property
[+] dsoUDTAsMaxString option added to TPSQLDataaset.Options property
[+] Methods Savepoint, ReleaseSavepoint, RollbackToSavepoint added to TPSQLDatabase
[+] Some unit tests added for RAD Studio 2010
[*] Current record position preserved after changing index or performing local sort
[*] Improved processing for NAME, TIME WITH TIMEZONE, TIMESTAMP WITH TIMEZONE field types
[*] Improved support for network address types (inet, cidr, macaddr)
[*] Improved support for user-defined types with OIDs greater then 65535
[*] Prevent “:” character followed by non-printable character in TPSQLQuery.SQL to be treated as parameter
[*] Prevented index info fetching for non-table datasets
[*] Thread safety improved for date\time low level conversion routines
[*] TPSQLStoredProc.Overload is set to 0 if StoredProcName were changed from now
[*] UTF8 encoding is not performed for connection parameters in IDE’s prior to Delphi 2009
[*] XML type mapped to TMemoField from now
[-] “Cannot obtain parameters from server if TPSQLStoredProc.Overload differs from default value” bug fixed
[-] “In IDE versions prior to 2009 using UTF-8 client encoding corrupts data display” bug fixed
[-] “Range methods (SetRange, SetRangeStart, SetRangeEnd) may fail on string values if UTF8 used” bug fixed
[-] “TPSQLDatabase.SelectString doesn’t return aDefaultValue if aFieldNumber less then zero” bug fixed
[-] “TPSQLTable.Options change have no effect” bug fixed
[-] “Wrong time value passed to server in some system date and time regional options, e.g. Italian” bug fixed
[-] Unwanted Assert() call eliminated in TPSQLDataset.Lookup method

There are both Trial and Full versions of installer of PostgresDAC v2.5.5 available.

You’re welcome to download the PostgresDAC v2.5.5 right now at
or login to your private area on our site at

Please don’t hesitate to ask any questions or report bugs with our Support Ticketing system available at