Qore pgsql Module  3.0
Qore pgsql Module

Contents of this documentation:

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

pgsql Driver Version 3.0

New Features and Bug Fixes

  • updated to build with Qore 0.9
  • exceptions thrown by PostgreSQL module contains arg values with alterr and alterr_diag strings as described in PostgreSQL error codes
  • fixed a crash reiterating SQL statements with no bind arguments (issue 3627)

pgsql Driver Version 2.4.2

Bug Fixes

  • fixed a bug retrieving DATE column values after the year 2038 (issue 2986)

pgsql Driver Version 2.4.1

New Features and Bug Fixes

  • updated SQLStatement::fetchColumns() to return an empty hash when no data is available (issue 1241)
  • fixed a formatting bug in an error message (issue 2018)
  • fixed selecting numeric values between 0 and 1 (issue 2149)
  • fixed a bug where DBI-SELECT-ROW-ERROR exceptions were being raised as PGSQL-SELECT-ROW-ERROR exceptions (issue 2542)

pgsql Driver Version 2.4

New Features and Bug Fixes

  • placeholders and quotes in SQL comments are ignored
  • fixed major bugs in SQLStatement::fetchRows() and SQLStatement::fetchColumns() where only the first call would succeed
  • added the pgsql_bind_array() function to support bulk DML
  • fixed a bug retrieving numeric values; values too large for a 64-bit integer were being retrieved as a 64-bit integer
  • ported db-test.q to new-style and QUnit
  • changed the minimum required Qore version to 0.8.12 to support the test script wth QUnit
  • fixed a bug where interval values retrieved from the DB were justified to hours and minutes
  • fixed a bug where CHAR values were returned with an invalid internal string size
  • fixed a bug where arbitrary-precision numeric values bound to numeric columns were bound with an incorrect scale value causing the digits behind the decimal place to be lost (issue 386)
  • fixed a bug where SQLStatement::describe() was failing even though result set information was available
  • fixed a bug where duplicate column names in result sets caused memory leaks (and they were not renamed; issue 831)

pgsql Driver Version 2.3

New Features and Bug Fixes

  • treat UNKNOWNOID as string; fixes cases such as selecting string constants (ie "select 'string'")

pgsql Driver Version 2.2

New Features and Bug Fixes

  • fixed a bug with the SQLStatement class where bind arguments were not bound on subsequent calls to SQLStatement::exec() or SQLStatement::execArgs()

pgsql Driver Version 2.1

New Features and Bug Fixes

  • implemented support for DBI_CAP_AUTORECONNECT; the driver will automatically and transparently reconnect to the DB server if the connection is lost while not in a transaction; if a transaction was in progress a reconnection will also be made, but an exception will also be thrown reflecting the loss of transaction state
  • fixed a crashing bug serializing the zero number value (0n) to PostgreSQL's binary decimal representation

pgsql Driver Version 2.0

New Features and Bug Fixes

  • implemented support for the selectRow DBI method
  • implemented support for the prepared statement API
  • implemented support for binding and retrieving "number" types
  • implemented support for the following DBI options:
    • "optimal-numbers": return NUMERIC or DECIMAL types as an integer if possible, if not as an arbitrary-precision number
    • "string-numbers": return NUMERIC or DECIMAL types as strings (for backwards-compatibility)
    • "numeric-numbers": return NUMERIC or DECIMAL 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 Time Zone Support.
  • the default for the number options (see Number Options) is now "optimal-numbers" - meaning that NUMERIC or DECIMAL values are retrieved as int if possible, otherwise a number type is returned.
    For backwards-compatibility, set the "string-numbers" option to return NUMERIC or DECIMAL values as strings, however note that PostgreSQL does not allow binding a string for a NUMERIC or DECIMAL column value; this will result in an error response by the server