6. Advanced Features

6.1. Stored Procedures Support

As most PostgreSQL users know, it does not have stored procedures support. Instead, it has a more flexible mechanism called Functions (created by CREATE FUNCTION statements).

The pgExpress Driver allows using of functions as stored procedures.

An usage example:

Example 2.4. Stored Procedures usage

procedure TForm1.Button1Click(Sender: TObject);
  with SQLDataset1 do
    CommandType := ctQuery;
    CommandText := 'create or replace function Test1(int2, int2) returns'
      + ' int2 as ''select $1 + $2;'' language ''SQL'';';
    CommandType := ctStoredProc;
    // This MUST be before setting paramaters.
    // VCL clears Params on setting CommandText.
    CommandText := 'Test1';
    Params[0].Value := 10;
    Params[1].Value := 20;
    ShowMessage(Params[2].AsString); // Will display 30

6.2. Fetch Cursors

PostgreSQL supports a feature named Fetch Cursors. These special cursors allow the records to be retrieved from the server in smaller sets then the defaulbehaviort, which is retrieving the full recordset. To achieve this, the the PostgreSQL DECLARE and FETCH commands are used. This can accelerate the queries response in some situations, specially when the connection to the server is fast and the recordset is large. Also, Fetch Cursors do not need the whole query to be processed: they can start retrieving data as soon as the requested number of rows is available. Finally, Fetch Cursors will consume less memory then regular cursors because fewer rows stays in memory each time.

As of version 1.60, the pgExpress Driver features automatic FETCH cursors. The only thing needed is setting the special param BlockRead; however, you probably will want to change also the RowsetSize param (please check your Kylix/Delphi documentation) if you want (as of Delphi 6/7 and Kylix 1/2/3, default VCL/CLX value is 20).

Internally, the pgExpress Driver will DECLARE a cursor with a unique name (per transaction) for each dataset and use FETCH commands as their rows are requested. When the dataset is freed, it's cursor will be closed.

The number of rows retrieved on each internal fetch operation is given by the RowsetSize param. A negative RowsetSize param will issue a FETCH ALL command, what actually does not differ much then a regular query without use of a FETCH command: all rows will be retrieved at once.

Usually larger values, like 50 or 100 (or even more, depending on your environment), would give a better performance. Please try and see what value is best for your application, but have in mind that each FETCH command means a separated query and that could slow down dataset scrolling over slower connections.

A typical dbxconnections(.ini) setup would be:


Internally, the pgExpress Driver will automatically execute all SQL commands needed to use Fetch Cursors (DECLARE and FETCH); all operations are transparent to the user.

An additional, good side effect of using Fetch Cursors is that the overall memory used is much less that if you retrieve all rows at once, due to libpq caching. For large datasets, using this setting is a smart option. Please check Q: 7 for more details.


  • Set TClientDataset.FetchOnDemand to True. If you don't set TClientDataset.FetchOnDemand to True, Fetch Cursors will be worthless because the TClientDataset will be caching all rows in memory. Of course when not using a caching grid such as TClientDataset, this is not needed.
  • Don't sort data on the client. If you sort the data in the client such as in a TClientDataset or in a TDBGrid components, all data will have to be retrieved in order to perform the sorting.
  • Monitor your connection. Always monitor your connection by attaching a TSQLMonitor to your TSQLConnection and log the communication between both into a file. In the case of DECLARE/FETCH cursors, the specific commands for them will be logged in there in case everything is running fine.
  • Registered users only. The Section 6.2, “Fetch Cursors” feature is available only for registered users.
  • The RetainCursor Special Parameter will be ignored if Fetch Cursors are in use. For more details on the inners of Fetch Cursors, please refer to the follow PostgreSQL documentation links:
  • Open transactions on older servers. As of PostgreSQL7.2, a transaction is needed in order to user Fetch Cursors; the pgExpress Driver will start a transaction automatically if one isn't already open. If you want to control the transactions behavior, open a transaction using TSQLConnection.StartTransaction before opening the cursor.

6.3. Authentication

As of PostgreSQL 7.3, three authentication methods are available: password, crypt and md5.

Since the pgExpress Driver is libpq based, use these three methods are automatic, and will follow what you have defined on the pg_hba.conf file (please check here for details).

We advice using the MD5 method (PostgreSQL 7.2 and above), unless you are using SSL or other encryption wrapper in your connection to the server.

6.4. Automatic numeric format detection

The pgExpress Driver has the ability to automatically detect the numeric format used on you server; namely, the Decimal and the thousands separators. There should be no need to set numeric formats manually, but in case you want or need, try the ServerDecimalSeparator special parameter.

In case the automatic detection do not work for you, please mail us at support@vitavoom.com

6.5. Native Int8 support

Since dbExpress (pre-protocol 3.0) does not support int8 types natively, the 1.X series of the pgExpress Driver used to map them as TBcdField fields by default (AsBcd).

As of pgExpress Driver v2.0+, int8 fields are mapped directly to TLargeintField fields using an internal hack. However, that hack will not work on all situations; specially if you need to write to int8 fields, map them as type TBcdField using the AsBcd special param and everything will work as you expect on the Delphi/Kylix side.

If you, however, need only read-only access to the int8 fields, you can use that hack and enjoy a native Int8 suppport instead of mapping to another type.

Important note about dbExpress usage with Int8 fields

Note that Int8 fields support is still not official in dbExpress as of this is written. While they will work most of time, the TIndexDefs create on these fields will not be correctly handled by VCL. You can get "Key violation" messages when trying to insert new values in this field, even if the values differ. To prevent this, you can try any of the following workarounds:

  1. Map Int8 fields as a type that is natively supported by dbExpress such as BCD using AsBcd. Avoid using AsString because this would sort the field as string and not as a number (i.e. '10' would be before '2').
  2. Disable metadata retrieving by setting GetMetadata to False on your TSQLDataset . In older versions then D7, it's the same as enabling NoMetadata.
  3. Delete the IndexDef generated automatically by dbExpress. After opening your ClientDataset, call ClientDataset1.IndexDefs.Delete(0) for removing that IndexDef. However, this will make the field unindexed; the exception will not be raised even when you insert duplicate values in the field.

6.6. Retaining Cursors

The lack of a ISQCursor.First() interface for dbExpress cursors make it necessary to run any query twice if you want to access a previous record again; the dbExpress technology is currently forward-only. Of course, Client Datasets (TCustomCachedDataset descendants) will cache records so random access is possible; but non-cached SQL Datasets (TCustomSQLDataset descendants) will make the query run again.

The pgExpress Driver implements an experimental setting, RetainCursor, that will make non-cached datatasets access MUCH faster to access from the second time, since the records will be retrieved from the internal libpq cache instead of being retrieved once again from the server. Basically, if RetainCursor is True, the query is a select query and the query is the same as the last one executed, the results displayed will be the same from the cache.

This behavior is controlled by the RetainCursor Special Parameter. If you are using RetainCursor = True and want a particular query to be trully executed again, instead of retrieved from the cache, add a '!' char to the beginning of the query (it will be automatically stripped by the pgExpress Driver):

!select * from pg_type;


This setting will be ignored if Fetch Cursors are being used.

6.7. BLOBs Support

As of pgExpress v3.50, there are three kinds of BLOBs supported, according to their mappings:

  • Large Objects -> ftBlob/ftBinary
  • Bytea ->ftBlob/ftGraphic
  • Text -> ftBlob/ftMemo

Detailed explain of these modes follows below. For very large streams, the best approach is to use Section 6.7.1, “Large Objects (BLOBs)”; however, for small (around 1-2Mb or even more), such as pictures, it's much easier and cleaner to use Bytea fields as BLOBs.

6.7.1. Large Objects (BLOBs)

Large Objects implement file-like I/O operations on PostgreSQL through a complicated API. The pgExpress Driver allows using them automatically through the regular dbExpress API.

Important upgrade Note:

As of pgExpress v2.31, we had to make some changes to the way pgExpress handles Large Objects and also changes to this documentation. Due to a bug in PostgreSQL up to v7.4 (PQftype() won't return the current oid for a domain, but for it's defined type instead) the way to create the 'lo' type has changed as shown below; it used to be:

create domain lo oid

but that will not work as it should due to the way the backend protocol works.

If you have a production database using the 'lo' type/domain declared in a different way then below, we suggest to pg_dump the database structure and data separately, then change the struct dump script to create the 'lo' type as shown below, and finally recreate the database structure with the new 'lo' type definition and restore the data. Large Object field declaration

The Large Object 'lo' field type isn't declared by the default in the PostgreSQL distribution, so we must do it manually. However, as of PostgreSQL 8.0, Windows users used to install the contrib modules from the installer might already have installed the 'lo' contrib module, which adds a compatible lo type, and these steps will not be necessary. Of course, users from other platforms that installed that contrib module will also have that type automatically created.

So, before creating the lo type, check if it isn't already created:

Example 2.5. 

howe=# select oid, typname from pg_type where typname='lo';
    oid  | typname
   17245 | lo
  (1 row)

The Large Object fields declaration must follow the method introduced by the PostgreSQL ODBC driver: a 'lo' type. This type can be created easyly using the following query (PostgreSQL 7.3 and above):

CREATE FUNCTION lo_in(cstring)
     RETURNS lo
     AS 'int4in'
  CREATE FUNCTION lo_out(lo)
     RETURNS cstring
     AS 'int4out'
     internallength = 4,
     input = lo_in,
     output = lo_out,
     alignment = int4,
     default = '',


Code originated from a Hiroshi-Saito post.

...or (PostgreSQL 7.2X and below):

create type lo(

You can then use this type normally on your tables:

Example 2.6. Creating a table with a Large Object

create table employee(id integer, name varchar(30), picture lo);


Some of this code is part of the PGSQL ODBC - FAQ Using Large Objects (BLOBs)

The pgExpress Driver can read Large Object (BLOB) fields without problems; however, due to the way the dbExpress technology was designed and the particular implementation of Large Objects on PostgreSQL, the pgExpress Driver has problems on updating Large Object fields.

PostgreSQL refers to Large Objects using a OID that points to the the real data.The libpq library needs this OID to do all sort of operations on the Large Object fields. The problem is that the dbExpress API will be expecting only the BLOB field's data; the OID information has no way to be stored. After the BLOB field is processed internally by the VCL/CLX and is sent back to the pgExpress Driver for being stored in the database, the original OID of the Large Object can't be retrieved, so pgExpress won't know which BLOB is refers to. This means that we can't alter the original Large Object, and if we create a new Large Object, the original Large Object will end up with as a orphan Large Object (a LO that exist but is not referenced by any rows, wasting disk space), unless of course another row refers to the same LO, what is not a common situation.

At Vita Voom Software™, we understand that Large Objects support it is crucial for some users and thus we have the following suggestion as a workaround to this problem, which affects also the JDBC and ODBC drivers:


  • This method has the disavantage of wasting OIDs. This should never be a problem since more then 4 billions (2^32) of OIDs are avaiable. However, if you want to save OIDs, the lo_clean() function above could easily be modified to reuse the old OID value in the case of a UPDATE query, by adding an statment such as:
    update pg_largeobject set loid=old.oid where loid=new.oid
  • The contrib/lo directory on the PostgreSQL distributions contains code that helps avoiding orphan Large Objects; you might be insterested in using it, or even reading the docs for more background on the subject.
  • As of PostgreSQL 8.0, a transaction must be open in order to work with Large Objects; but if there is none open, the pgExpress driver will open one itself, and rollback it when done. In order to avoid this open/close transaction overhead, and having possibility of commiting the trsansaction, it's better to open explicitly a transaction and commiting/rolling back after you're done.
  • It will probably be much easier and flexible to work with bytea fields instead of Large Objects, since it's API is very unflexible and complicated. Check the ByteaMode Special Param for more details.
  1. Create your table normally, including the BLOB field (please read this section about BLOB fields declaration):

    create table lo_test(id serial, image lo);
  2. Create a trigger and function that will delete the Large Object if their values are changed (replace the b fieldname for your real field name):


    create or replace function test_lo_clean() returns trigger as '
          lo_oid oid;
          -- If it is an update action but the BLOB (lo) field was not changed, dont do anything
          if (TG_OP = ''UPDATE'') then
            if (old.image = new.image) or (old.image is null) then
              return new;
            end if;
          end if;
          select into lo_oid loid from pg_largeobject where lo_oid = oid(old.image);
          if found then
            perform lo_unlink(lo_oid);
          end if;
          return new;
      language 'plpgsql';


    create trigger lo_cleanup
      after delete or update on lo_test -- must be after to avoid deleting the Large Object if record is not deleted
      for each row execute procedure test_lo_clean();


    The function above uses the pl/PgSql language, which must be created in the datbase if it wasn't already. This can be accomplished easily through the following command (at the shell prompt):

    $ createlang -d dbname plpgsql

    For more details on creating languages, please check here.

    This trigger will avoid that orphan Large Objects are left on the table. Note that triggers are fast; in fact, referential integrity is implemented internally in PostgreSQL using them. If you want to try another code to do it, feel free; all you need to do is ensure no orphan OIDs are left on the pg_largeobject table.

    You might have to make some small changes to the test_lo_clean() function to reflect your actual table and field names, if you have large objects reused in more then one field/record, etc.

Hopefully the PostgreSQL developers will introduce a new API for handling Large Objects that does not suffer from these limitations.


  • All Large Object operations must take place inside transactions. If one isn't started, the pgExpress Driver will open one automatically, and close it after finishing reading (or writing, but VCL/CLX always resolve updates inside transactions, so this usually won't be a problem). This transaction opening/closing adds some considerable overhead if you do it for several fields. Hence, always start a transaction prior to using Large Objects.
  • As os PostgreSQLv8.0, Large Object operations aren't compatible with asynchronous connections. The pgExpress Driver will automatically switch for a blocking (synchronous) connection during the Large Object operations and switch back to non-blocking after finished.
  • If you have orphan Large Objects in a table and want to clean them, try the following approach:
    select lo_unlink(lo_column) from mytable;
  • For a demo on BLOB fields, try Demos/Fishfact from the pgExpress Driver distribution.

6.7.2. Bytea fields as BLOBs

As of pgExpress 3.50, it is possible (and actually the new default behavior) to map bytea fields as ftBlob (ftGraphic or ftBinary) through using theByteaMode Special Param. This makes much easier to use BLOB fields on pgExpress since there is no need to use the Large Objects API.

While this is activated by default, it could conflict with older datasets that are mapping bytea fields as TVarBytes fields, which was the old pgExpress behaviour. To force mapping bytea fields as TVarBytesField again, set the special param ByteaMode Special Param to asVarBytes. To completely disable Section 6.7.1, “Large Objects (BLOBs)” support by mapping bytea fields as ftBlob>/ftBinary, set the special param ByteaMode Special Param asBlob.

Remember that bytea chars greater that 128 must be quoted as octals (ex: \343), what will make streams be greater in size (4x more for those chars that have to be quoted) and also waste more bandwidth - a mean of about 2.5x for a random stream. For small streams, this should not be a problem; however, for greater streams, consider using Section 6.7.1, “Large Objects (BLOBs)”. This protocol behavior is internal to PostgreSQL and the pgExpress Driver must conform to it.


When assigning a value to a bytea TParam which is mapped as ftGraphic, you'll tipically use the TParam.asBlob property, or the SetBlobData method.

After doing it, do not forget to set the TParam.DataType to ftGraphic, because these methods set TParam.DataType to True; otherwise, the fields will be try to be saved as Large Objects, and that would be the source of weird errors. Example:

Example 2.7. 

with Dataset1.ParamByName('value') do
  ParamType := ptInput;
  AsBlob := aData;
  DataType := ftGraphic;

Again, this is only needed by bytea fields mapped as ftGraphic, on the specific conditions mentioned above.

6.7.3. TEXT fields as BLOBs

To allow easy use of TEXT fields, the ppgExpress Driver maps them as ftBlob/ftMemo by default; they can have arbitrary lengths, contrary to regular string (ftString) fields, whose must have a specific length.

VARCHAR fields without length specifier behaves exactly like TEXT fields, and will be mapped as such. See also TextAsMemo and Q: 11 for more details.


When assigning a value to a ftMemo TParam, use always the TParam.asMemo property (and not TParam.asBlob, which is intented to work with binary blobs(subtype ftBinary).

6.8. SSL Support

PostgreSQL has built-in support for SSL. However, on the backend (client) side, we depend on libpq support for actually encrypting the connection. Make sure your libpq is SSL-enabled iof you want to make SSL-encrypted connections.

There are, however other ways to do it, even if your libpq is not SSL-enabled. The simplest is making a tunnel using either SSH or Stunnel. Please go to the referred site for more details.

6.9. NOTICEs support

PostgreSQL by default will print NOTICE messages (generated by the server and inside functions by statements such as RAISE NOTICE) in the server's standard output. The pgExpress Driver implements a hook which allows those messages to be logged along other regular messages so that a TSQLMonitor component will be able to log them all automatically.


The NOTICE messages will set CBInfo to traceVENDOR on the OnTrace/OnLogTrace events.

6.10. Asynchrounous connections

The pgExpress Driver has asynchronous connections support. Their advantage is that a query could be cancelled while in execution in the server. Other then that, there is no real advantage on then.

To activate asynchronous support on pgExpress, set this on your dbxconnections.ini file (or equivalent):


To force synchronous connections, whose are more compatible in general, use:


If you have problems with async support, please switch to synchronous mode.

6.11. Canceling a query

Whenever you send a query to the PostgreSQL server, it can delay a long time to return a result to your application. Typical cases include a very long resultset, lots of computations/calculations, or even a function hangup/infinite loop. It is nice to let you user cancel a query if they want to, thus returning control to your program, and quitting things nicely in the server side. The way to accomplish this is by using asynchronous connections, and that can be cancelled by calling the PQrequestCancel function.

The pgExpress Driver wraps that API nicely in the pgeDriverUtils.CancelQuery function. Calling it will make the server try its best to cancel the currently query and return control to the client application. This has the same effect as pressing Ctrl+C in the psql console.