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:

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

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:

[PGEConnection]
BlobSize=32
Database=localhost/mydb,BlockRead=True
DriverName=PostgreSQL
Password=temp123
User_Name=foo
RowsetSize=200

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.

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.

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

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.

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;

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

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.

create domain lo oid

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

CREATE FUNCTION lo_in(cstring)
     RETURNS lo
     AS 'int4in'
     LANGUAGE 'internal' WITH (ISCACHABLE, ISSTRICT);
  
  CREATE FUNCTION lo_out(lo)
     RETURNS cstring
     AS 'int4out'
     LANGUAGE 'internal' WITH (ISCACHABLE, ISSTRICT);
  
  CREATE TYPE lo(
     internallength = 4,
     externallength=10,
     input = lo_in,
     output = lo_out,
     alignment = int4,
     default = '',
     passedbyvalue
  );
  CREATE CAST (lo AS oid) WITHOUT FUNCTION;

create type lo(
    internallength=4,
    externallength=10,
    input=oidin,
    output=oidout, 
    default='',
    passedbyvalue
  );

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

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

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.

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 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):

BlockingMode=False

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

BlockingMode=True

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

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.