/** @mainpage Qore pgsql Module

    @tableofcontents

    Contents of this documentation:
    - @ref pgsqlintro
    - @ref pgsqloptions
    - @ref pgsqltrans
    - @ref pgsqlbind
    - @ref pgsqlstoredprocs
    - @ref pgsqlreleasenotes

    @section pgsqlintro Introduction to the pgsql Module

    The pgsql module provides a <a href="www.postgresql.org">PostgreSQL</a> 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:
    - <a href="http://www.gnu.org/licenses/old-licenses/lgpl-2.1.html">LGPL 2.1</a>
    - 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:
    @code
$db = new Datasource(SQL::DSPGSQL, $user, $pass, $db, $charset, $hostname, $port);
    @endcode

    This driver supports the following DBI capabilities when compiled against PostgreSQL 7 or better:
    - \c DBI_CAP_TIME_ZONE_SUPPORT
    - \c DBI_CAP_TRANSACTION_MANAGEMENT
    - \c DBI_CAP_CHARSET_SUPPORT
    - \c DBI_CAP_LOB_SUPPORT
    - \c DBI_CAP_STORED_PROCEDURES
    - \c DBI_CAP_BIND_BY_VALUE
    - \c DBI_CAP_HAS_EXECRAW
    - \c DBI_CAP_HAS_STATEMENT
    - \c DBI_CAP_HAS_SELECT_ROW
    - \c DBI_CAP_HAS_NUMBER_SUPPORT
    - \c DBI_CAP_HAS_OPTION_SUPPORT
    - \c DBI_CAP_SERVER_TIME_ZONE
    - \c 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:

    @verbatim
major_version * 10000 + minor_version * 100 + sub_version
    @endverbatim

    (For example: \c 90101 = <tt>9.1.1</tt>).

    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 @ref Qore::Pgsql::PGSQL_HAVE_GETCLIENTVERSION "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 \c 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: \c 90101 = <tt>9.1.1</tt>).

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

    @section pgsqloptions Driver Options

    When compiled against Qore 0.8.6+ the pgsql driver support the following DBI options:
    - \c "optimal-numbers": return numeric types as an integer if possible, if not as an arbitrary-precision number
    - \c "string-numbers": return numeric types as strings (for backwards-compatibility)
    - \c "numeric-numbers": return numeric types as arbitrary-precision number values
    - \c "timezone": accepts a string argument that can be either a region name (ex: \c "Europe/Prague") or a UTC offset (ex: \c "+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 @ref timezone.

    Options can be set in the \c Datasource or \c DatasourcePool constructors as in the following examples:
    @code
    my Dataource $ds("pgsql:user/pass@db{numeric-numbers,timezone=Europe/Vienna}");
    my DataourcePool $dsp("pgsql:user/pass@db%host.internal:5432{optimal-numbers}");
    @endcode

    Options are stored separately for each connection.

    @subsection pgsql_number_options Number Options

    The number options (\c "optimal-numbers", \c "string-numbers", and \c "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 @ref False).

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

    @section pgsqltrans Transaction Management

    When the Datasource is not in auto-commit mode, this driver automatically inserts \c "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 \c "rollback" to be executed to maintain compatibility with other Qore DBI drivers.

    @section pgsqlbind 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 \c 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. \c "::type") or use the pgsql_bind() or pgsql_bind_array() functions to specify the type to bind. See @ref pgsql_binding_by_value for more information about how types are bound when binding by value with this driver.

    For \c 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:

    @code
# 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);
    @endcode

    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 @ref pgsql_to_qore.

    @subsection pgsql_timezone 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 \c "timezone" driver option (see @ref pgsqloptions).

    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.

    @subsection pgsql_binding_by_value 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. \c "::type") or use the pgsql_bind() or pgsql_bind_array() functions.

    @htmlonly <style><!-- td.qore { background-color: #5b9409; color: white; } --></style> @endhtmlonly
    <table>
      <tr>
        <td class="qore"><b>QoreType</b></td>
        <td class="qore"><b>PostgreSQL Type</b></td>
        <td class="qore"><b>Description</b></td>
      </tr>
      <tr>
        <td>\c int</td>
        <td>\c INT2, \c INTEGER, \c INT8</td>
        <td>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.</td>
      </tr>
      <tr>
        <td>\c float</td>
        <td>\c FLOAT88</td>
        <td>Qore float data is converted directly to PostgreSQL float8 data.</td>
      </tr>
      <tr>
        <td>\c string</td>
        <td>\c TEXT</td>
        <td>The character encoding is converted to the encoding specified for the connection if necessary.</td>
      </tr>
      <tr>
        <td>\c bool</td>
        <td>\c BOOLEAN</td>
        <td>Qore boolean values are converted directly to the PostgreSQL boolean format.</td>
      </tr>
      <tr>
        <td>\c date</td>
        <td>\c INTERVAL, \c TIMESTAMP</td>
        <td>Relative date/time values are bound as interval data, absolute date/time values are bound as timestamp data</td>
      </tr>
      <tr>
        <td>\c binary</td>
        <td>\c BYTEA</td>
        <td>Qore binary data is bound directly as bytea data.</td>
      </tr>
    </table>

    @subsection pgsql_to_qore PostgreSQL to Qore Type Mappings
    <table>
      <tr>
        <td class="qore"><b>PostgreSQL Type</b></td>
        <td class="qore"><b>Qore Type</b></td>
        <td class="qore"><b>Notes</b></td>
      </tr>
      <tr>
        <td>\c BOOLEAN</td>
        <td>\c bool</td>
        <td>direct conversion</td>
      </tr>
      <tr>
        <td>\c BYTEA</td>
        <td>\c binary</td>
        <td>direct conversion</td>
      </tr>
      <tr>
        <td>\c CHAR</td>
        <td>\c string</td>
        <td>trailing whitespace is removed</td>
      </tr>
      <tr>
        <td>\c BPCHAR</td>
        <td>\c string</td>
        <td>trailing whitespace is removed</td>
      </tr>
      <tr>
        <td>\c INT8</td>
        <td>\c Int</td>
        <td>direct conversion</td>
      </tr>
      <tr>
        <td>\c INTEGER</td>
        <td>\c Int</td>
        <td>direct conversion</td>
      </tr>
      <tr>
        <td>\c OID</td>
        <td>\c Int</td>
        <td>direct conversion</td>
      </tr>
      <tr>
        <td>\c XID</td>
        <td>\c Int</td>
        <td>direct conversion</td>
      </tr>
      <tr>
        <td>\c CID</td>
        <td>\c Int</td>
        <td>direct conversion</td>
      </tr>
      <tr>
        <td>\c INT2</td>
        <td>\c Int</td>
        <td>direct conversion</td>
      </tr>
      <tr>
        <td>\c TEXT</td>
        <td>\c string</td>
        <td>direct conversion</td>
      </tr>
      <tr>
        <td><tt>CHAR VARYING</tt></td>
        <td>\c string</td>
        <td>trailing whitespace is removed</td>
      </tr>
      <tr>
        <td>\c NAME</td>
        <td>\c string</td>
        <td>direct conversion</td>
      </tr>
      <tr>
        <td>\c FLOAT4</td>
        <td>\c Float</td>
        <td>direct conversion</td>
      </tr>
      <tr>
        <td>\c FLOAT8</td>
        <td>\c Float</td>
        <td>direct conversion</td>
      </tr>
      <tr>
        <td>\c ABSTIME</td>
        <td>\c Date (absolute)</td>
        <td>direct conversion</td>
      </tr>
      <tr>
        <td>\c RELTIME</td>
        <td>\c Date (relative)</td>
        <td>direct conversion to relative seconds</td>
      </tr>
      <tr>
        <td>\c TIMESTAMP</td>
        <td>\c Date (absolute)</td>
        <td>When compiled with qore 0.8.0+, microseconds are maintained.</td>
      </tr>
      <tr>
        <td><tt>TIMESTAMP WITH TIMEZONE</tt></td>
        <td>\c Date (absolute)</td>
        <td>When compiled with qore 0.8.0+, microseconds and time zone information are maintained.</td>
      </tr>
      <tr>
        <td>\c DATE</td>
        <td>\c Date (absolute)</td>
        <td>direct conversion</td>
      </tr>
      <tr>
        <td>\c INTERVAL</td>
        <td>\c Date (relative)</td>
        <td>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.</td>
      </tr>
      <tr>
        <td>\c TIME</td>
        <td>\c Date (absolute)</td>
        <td>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.</td>
      </tr>
      <tr>
        <td><tt>TIME WITH TIMEZONE</tt></td>
        <td>\c Date (absolute)</td>
        <td>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.</td>
      </tr>
      <tr>
        <td>\c TINTERVAL</td>
        <td>\c string</td>
        <td>string format is given in ~PostgreSQL tinterval format ([\c "YYYY-MM-DD hh:mm:ss" \c "YYYY-MM-DD hh:mm:ss"])</td>
      </tr>
      <tr>
        <td>\c NUMERIC</td>
        <td>\c string</td>
        <td>To avoid loss of precision, the information is converted to a string</td>
      </tr>
      <tr>
        <td>\c CASH</td>
        <td>\c Float</td>
        <td>direct conversion</td>
      </tr>
      <tr>
        <td>\c MACADDR</td>
        <td>\c string</td>
        <td>format: <tt>xx:xx:xx:xx:xx:xx</tt></td>
      </tr>
      <tr>
        <td>\c INET</td>
        <td>\c string</td>
        <td>ipv4: <tt>n.n.n.n/net</tt>, ipv6: <tt>x:x:x:x:x:x:x:x/net</tt></td>
      </tr>
      <tr>
        <td>\c CIDR</td>
        <td>\c string</td>
        <td>ipv4: <tt>n.../net</tt>, ipv6: <tt>x:...::/net</tt></td>
      </tr>
      <tr>
        <td>\c TID</td>
        <td>\c string</td>
        <td>format: <tt>(n,n)</tt></td>
      </tr>
      <tr>
        <td>\c BIT</td>
        <td>\c binary</td>
        <td>direct conversion</td>
      </tr>
      <tr>
        <td>\c VARBIT</td>
        <td>\c binary</td>
        <td>direct conversion</td>
      </tr>
      <tr>
        <td>\c POINT</td>
        <td>\c string</td>
        <td>format: <tt>n,n</tt></td>
      </tr>
      <tr>
        <td>\c LSEG</td>
        <td>\c string</td>
        <td>format: <tt>(n,n),(n,n)</tt></td>
      </tr>
      <tr>
        <td>\c BOX</td>
        <td>\c string</td>
        <td>format: <tt>(n,n),(n,n)</tt></td>
      </tr>
      <tr>
        <td>\c PATH</td>
        <td>\c string</td>
        <td>in PostgreSQL text format depending on path type</td>
      </tr>
      <tr>
        <td>\c POLYGON</td>
        <td>\c string</td>
        <td>in PostgreSQL text format for polygons <tt>((n,n),...)</tt></td>
      </tr>
      <tr>
        <td>\c CIRCLE</td>
        <td>\c string</td>
        <td>format: <tt><(n,n),n></tt></td>
      </tr>
    </table>

    @section pgsqlstoredprocs Stored Procedures

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

    Example PostgreSQL PLPG/SQL function:
    @code
create or replace function int_test(val int4) returns int4 as $$
begin
    return val * 2;
end;
$$ language plpgsql;
   @endcode

   Example Qore code to call this function:
   @code
$result = $db.exec("select int_test(%v)", 11);
printf("%N\n", $result);
   @endcode

   Resulting in:
   @verbatim
hash: (1 member)
  int_test : 22
   @endverbatim

    @section pgsqlreleasenotes Release Notes

    @subsection pgsql241 pgsql Driver Version 2.4.1
    <b>New Features and Bug Fixes</b>
    - updated SQLStatement::fetchColumns() to return an empty hash when no data is available (<a href="https://github.com/qorelanguage/qore/issues/1241">issue 1241</a>)
    - fixed selecting numeric values between 0 and 1 (<a href="https://github.com/qorelanguage/qore/issues/2149">issue 2149</a>)

    @subsection pgsql24 pgsql Driver Version 2.4

    <b>New Features and Bug Fixes</b>
    - 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 \c 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 (<a href="https://github.com/qorelanguage/qore/issues/386">issue 386</a>)
    - 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; <a href="https://github.com/qorelanguage/qore/issues/831">issue 831</a>)

    @subsection pgsql23 pgsql Driver Version 2.3

    <b>New Features and Bug Fixes</b>
    - treat \c UNKNOWNOID as string; fixes cases such as selecting string constants (ie \c "select 'string'")

    @subsection pgsql22 pgsql Driver Version 2.2

    <b>New Features and Bug Fixes</b>
    - fixed a bug with the SQLStatement class where bind arguments were not bound on subsequent calls to SQLStatement::exec() or SQLStatement::execArgs()

    @subsection pgsql21 pgsql Driver Version 2.1

    <b>New Features and Bug Fixes</b>
    - 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

    @subsection pgsql20 pgsql Driver Version 2.0

    <b>New Features and Bug Fixes</b>
    - 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:
      - \c "optimal-numbers": return \c NUMERIC or \c DECIMAL types as an integer if possible, if not as an arbitrary-precision number
      - \c "string-numbers": return \c NUMERIC or \c DECIMAL types as strings (for backwards-compatibility)
      - \c "numeric-numbers": return \c NUMERIC or \c DECIMAL types as arbitrary-precision number values
      - \c "timezone": accepts a string argument that can be either a region name (ex: \c "Europe/Prague") or a UTC offset (ex: \c "+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 @ref pgsql_timezone.
    - the default for the number options (see @ref pgsql_number_options) is now "optimal-numbers" - meaning that \c NUMERIC or \c DECIMAL values are retrieved as <tt>int</tt> if possible, otherwise a <tt>number</tt> type is returned.\n
      For backwards-compatibility, set the \c "string-numbers" option to return \c NUMERIC or \c DECIMAL values as strings, however note that PostgreSQL does not allow binding a string for a \c NUMERIC or \c DECIMAL column value; this will result in an error response by the server

*/