Introduction to the pgsql Module

The pgsql module provides a PostgreSQL driver to Qore's DBI system, allowing Qore programs to access PostgreSQL databases through the Datasource, DatasourcePool, and SQLStatement classes.

This module is released under a choice of two licenses:

LGPL 2.1
MIT (see COPYING.MIT in the source distribution for more information)

The module is tagged as such in the module's header (meaning it can be loaded unconditionally regardless of how the Qore library was initialized).

Example of creating a PostgreSQL Datasource:

$db = new Datasource(SQL::DSPGSQL, $user, $pass, $db, $charset, $hostname, $port);

This driver supports the following DBI capabilities when compiled against PostgreSQL 7 or better:

DBI_CAP_TIME_ZONE_SUPPORT
DBI_CAP_TRANSACTION_MANAGEMENT
DBI_CAP_CHARSET_SUPPORT
DBI_CAP_LOB_SUPPORT
DBI_CAP_STORED_PROCEDURES
DBI_CAP_BIND_BY_VALUE
DBI_CAP_HAS_EXECRAW
DBI_CAP_HAS_STATEMENT
DBI_CAP_HAS_SELECT_ROW
DBI_CAP_HAS_NUMBER_SUPPORT
DBI_CAP_HAS_OPTION_SUPPORT
DBI_CAP_SERVER_TIME_ZONE
DBI_CAP_AUTORECONNECT

The driver employs efficient binary bindings for all non-text data types and fully supports multidimensional arrays when selecting and binding by value. The driver determines on a per-connection basis by querying server capabilities whether the server uses 8-byte integer or floating-point data for date/time types, and also whether or not a binary day value is included in intervals.

The Datasource::getServerVersion() method is implemented for this driver and returns an integer giving the major, minor, and sub version numbers in a single integer according to the following formula:

major_version * 10000 + minor_version * 100 + sub_version

(For example: 90101 = 9.1.1).

The Datasource::getClientVersion() method is implemented only when the driver is compiled against the PostgreSQL client library version 9.1 or better, as the PQlibVersion() function was first supplied in that version of the PostgreSQL client library. Check the PGSQL_HAVE_GETCLIENTVERSION constant before calling this method with this driver, as if the client library did not support this API at compile time, calling getClientVersion() will cause a DBI:PGSQL-GET-CLIENT-VERSION-ERROR exception to be thrown.

If the function is available, the return value is equal to the return value of the getServerVersion() method described above.

(For example: 90101 = 9.1.1).

Like all Qore components, the PostgreSQL DBI driver is thread-safe.

Driver Options

When compiled against Qore 0.8.6+ the pgsql driver support the following DBI options:

"optimal-numbers": return numeric types as an integer if possible, if not as an arbitrary-precision number
"string-numbers": return numeric types as strings (for backwards-compatibility)
"numeric-numbers": return numeric types as arbitrary-precision number values
"timezone": accepts a string argument that can be either a region name (ex: "Europe/Prague") or a UTC offset (ex: "+01:00") to set the server's time zone rules; this is useful if connecting to a database server in a different time zone. If this option is not set then the server's time zone is assumed to be the same as the client's time zone; see timezone.

Options can be set in the Datasource or DatasourcePool constructors as in the following examples:

my Dataource $ds("pgsql:user/pass@db{numeric-numbers,timezone=Europe/Vienna}");

my DataourcePool $dsp("pgsql:user/pass@db%host.internal:5432{optimal-numbers}");

Options are stored separately for each connection.

Number Options

The number options ("optimal-numbers", "string-numbers", and "numeric-numbers") are all mutually-exclusive; setting one automatically disables the others. These options also ignore their arguments; the last one set takes effect (also note that setting it with any value sets the option, even False).

The default if no other option is explicitly set is "optimal-numbers". Note that this represents a change from previous versions where NUMERIC or DECIMAL values were returned as strings in order to avoid the loss of information. To set the old behavior, set the "string-numbers" option when creating the Datasource or DatasourcePool object.

Transaction Management

When the Datasource is not in auto-commit mode, this driver automatically inserts "begin" statements at the start of each transaction to provide consistent transaction handling across all Qore DBI drivers; it's not necessary to do this manually.

Note that an exception while already in a transaction will have the effect that the pgsql connection cannot be used until the transaction is closed; this is different from most other current Qore DBI drivers. An exception in a Datasource::exec() method as the first statement of a transaction will cause an automatic implicit "rollback" to be executed to maintain compatibility with other Qore DBI drivers.

Binding and Types

When binding by value, PostgreSQL servers do not convert data types as freely as many other database servers; instead the server throws an error if the bound type does not closely match the expected data type. For example, binding a string or float value to a NUMERIC data type will cause an exception to be thrown. To successfully bind by value, either use a PostgreSQL type cast in the SQL text (i.e. "::type") or use the pgsql_bind() or pgsql_bind_array() functions to specify the type to bind. See Binding By Value for more information about how types are bound when binding by value with this driver.

For NUMERIC columns, the d specification in the query string can be used to substitute a numeric value or a literal null directly in the query string, providing the same effect as a bind by value in a very convenient form for the programmer. For example:

# the %d will be substituted with the value of $id directly in the query text
# or with a "null" in case $id has no value (or is NULL)
my *hash $results = $db.select("select * from table where id = %d", $id);

Due to the fact that Qore date/time values support time zone information, when PostgreSQL data including a time zone component is converted to a Qore data type, the time zone information is also maintained. Also, Qore date/time values support a microsecond resolution, matching PostgreSQL's microsecond support, so this information is also maintained.

Note that binding by placeholder is not required or supported by this driver as PostgreSQL returns values directly; Qore DBI placeholder buffer specifications are ignored when the pgsql driver is used.

When binding arrays, all data types in the Qore list must be identical. When binding multi-dimensional arrays, the number of elements in each list in each array dimension must be the same. These are PostgreSQL restrictions (at least up to version 8.2).

When retrieving PostgreSQL data, PostgreSQL types are converted to Qore types as per PostgreSQL to Qore Type Mappings.

Time Zone Support

The driver now sets the server's time zone rules when the connection is established; this is taken from the current time zone settings of the calling Program object and can also be overridden/changed by setting the "timezone" driver option (see Driver Options).

All date/time values bound by value are converted to the server's time zone before binding to ensure that date/time values are stored correctly in the server.

When selecting date/time values, the values returned are tagged with the server's time zone.

Note that the above rules are applied when the current Program's time zone settings are different than the connection's time zone settings at the time when the write operation (bind) read operation (select) is performed. This is meant to provide consistent support to connecting to a database server in a different time zone.

Binding By Value

The following table gives data mapping when binding qore types by value. Be aware that PostgreSQL does only limited implicit type conversions when binding by value; to specify other types, either use a PostgreSQL type cast (i.e. "::type") or use the pgsql_bind() or pgsql_bind_array() functions.

QoreType	PostgreSQL Type	Description
`int`	`INT2`, `INTEGER`, `INT8`	Depending on the size of the integer, the appropriate PostgreSQL type is bound. This is to avoid errors by using a type larger than that specified for the column or variable, as converting from a smaller type to a larger type is done automatically by the server.
`float`	`FLOAT88`	Qore float data is converted directly to PostgreSQL float8 data.
`string`	`TEXT`	The character encoding is converted to the encoding specified for the connection if necessary.
`bool`	`BOOLEAN`	Qore boolean values are converted directly to the PostgreSQL boolean format.
`date`	`INTERVAL`, `TIMESTAMP`	Relative date/time values are bound as interval data, absolute date/time values are bound as timestamp data
`binary`	`BYTEA`	Qore binary data is bound directly as bytea data.

PostgreSQL to Qore Type Mappings

PostgreSQL Type	Qore Type	Notes
`BOOLEAN`	`bool`	direct conversion
`BYTEA`	`binary`	direct conversion
`CHAR`	`string`	trailing whitespace is removed
`BPCHAR`	`string`	trailing whitespace is removed
`INT8`	`Int`	direct conversion
`INTEGER`	`Int`	direct conversion
`OID`	`Int`	direct conversion
`XID`	`Int`	direct conversion
`CID`	`Int`	direct conversion
`INT2`	`Int`	direct conversion
`TEXT`	`string`	direct conversion
`CHAR VARYING`	`string`	trailing whitespace is removed
`NAME`	`string`	direct conversion
`FLOAT4`	`Float`	direct conversion
`FLOAT8`	`Float`	direct conversion
`ABSTIME`	`Date` (absolute)	direct conversion
`RELTIME`	`Date` (relative)	direct conversion to relative seconds
`TIMESTAMP`	`Date` (absolute)	When compiled with qore 0.8.0+, microseconds are maintained.
`TIMESTAMP WITH TIMEZONE`	`Date` (absolute)	When compiled with qore 0.8.0+, microseconds and time zone information are maintained.
`DATE`	`Date` (absolute)	direct conversion
`INTERVAL`	`Date` (relative)	direct conversion to relative months, seconds, microseconds (with qore 0.8.0+, otherwise milliseconds), and for servers that send a separate day value, to relative days.
`TIME`	`Date` (absolute)	When compiled with qore 0.8.0+, microseconds are maintained. Note that the date portion is set to January 1, 1970, the beginning of the 64-bit qore epoch.
`TIME WITH TIMEZONE`	`Date` (absolute)	When compiled with qore 0.8.0+, microseconds and time zone information are maintained. Note that the date portion is set to January 1, 1970, the beginning of the 64-bit qore epoch.
`TINTERVAL`	`string`	string format is given in ~PostgreSQL tinterval format ([`"YYYY-MM-DD hh:mm:ss"` `"YYYY-MM-DD hh:mm:ss"`])
`NUMERIC`	`string`	To avoid loss of precision, the information is converted to a string
`CASH`	`Float`	direct conversion
`MACADDR`	`string`	format: `xx:xx:xx:xx:xx:xx`
`INET`	`string`	ipv4: `n.n.n.n/net`, ipv6: `x:x:x:x:x:x:x:x/net`
`CIDR`	`string`	ipv4: `n.../net`, ipv6: `x:...::/net`
`TID`	`string`	format: `(n,n)`
`BIT`	`binary`	direct conversion
`VARBIT`	`binary`	direct conversion
`POINT`	`string`	format: `n,n`
`LSEG`	`string`	format: `(n,n),(n,n)`
`BOX`	`string`	format: `(n,n),(n,n)`
`PATH`	`string`	in PostgreSQL text format depending on path type
`POLYGON`	`string`	in PostgreSQL text format for polygons `((n,n),...)`
`CIRCLE`	`string`	format: `<(n,n),n>`

Stored Procedures

Stored procedure execution is supported; the following is an example of a stored procedure call:

Example PostgreSQL PLPG/SQL function:

create or replace function int_test(val int4) returns int4 as $$
begin
    return val * 2;
end;
$$ language plpgsql;

Example Qore code to call this function:

$result = $db.exec("select int_test(%v)", 11);

printf("%N\n", $result);

Resulting in:

hash: (1 member)
  int_test : 22

Release Notes