SOCI - The C++ Database Access Library

## PostgreSQL Backend Reference

### Prerequisites

#### Supported Versions

The SOCI PostgreSQL backend is supported for use with PostgreSQL >= 7.3, although versions older than 8.0 will suffer from limited feature support. See below for details.

#### Tested Platforms

PostgreSQL version | Operating System | Compiler
--- | --- | ---
9.0 | Mac OS X 10.6.6 | g++ 4.2
8.4 | FreeBSD 8.2 | g++ 4.1
8.4 | Debian 6 | g++ 4.3
8.4 | RedHat 5 | g++ 4.3

#### Required Client Libraries

The SOCI PostgreSQL backend requires PostgreSQL's `libpq` client library.

Note that the SOCI library itself depends also on `libdl`, so the minimum set of libraries needed to compile a basic client program is:

    -lsoci_core -lsoci_postgresql -ldl -lpq

#### Connecting to the Database

To establish a connection to the PostgreSQL database, create a `session` object using the `postgresql` backend factory together with a connection string:

    session sql(postgresql, "dbname=mydatabase");

    // or:
    session sql("postgresql", "dbname=mydatabase");

    // or:
    session sql("postgresql://dbname=mydatabase");

The set of parameters used in the connection string for PostgreSQL is the same as accepted by the `PQconnectdb` function from the `libpq` library.

Once you have created a `session` object as shown above, you can use it to access the database, for example:

    int count;
    sql << "select count(*) from invoices", into(count);

(See the SOCI basics and exchanging data documentation for general information on using the `session` class.)

### SOCI Feature Support

#### Dynamic Binding

The PostgreSQL backend supports the use of the SOCI `row` class, which facilitates retrieval of data whose type is not known at compile time.

When calling `row::get<T>()`, the type you should pass as `T` depends upon the underlying database type.
For the PostgreSQL backend, this type mapping is:

PostgreSQL Data Type | SOCI Data Type | `row::get<T>` specializations
--- | --- | ---
numeric, real, double | `dt_double` | `double`
boolean, smallint, integer | `dt_integer` | `int`
int8 | `dt_long_long` | `long long`
oid | `dt_integer` | `unsigned long`
char, varchar, text, cstring, bpchar | `dt_string` | `std::string`
abstime, reltime, date, time, timestamp, timestamptz, timetz | `dt_date` | `std::tm```

(See the dynamic resultset binding documentation for general information on using the `row` class.)

#### Binding by Name

In addition to binding by position, the PostgreSQL backend supports binding by name, via an overload of the `use()` function:

    int id = 7;
    sql << "select name from person where id = :id", use(id, "id")

Apart from the portable "colon-name" syntax above, which is achieved by rewriting the query string, the backend also supports the PostgreSQL native numbered syntax:

    int i = 7;
    int j = 8;
    sql << "insert into t(x, y) values($1, $2)", use(i), use(j);

The use of native syntax is not recommended, but can be nevertheless imposed by switching off the query rewriting. This can be achieved by defining the macro `SOCI_POSTGRESQL_NOBINDBYNAME` and it is actually necessary for PostgreSQL 7.3, in which case binding of use elements is not supported at all. See the Configuration options section for details.

#### Bulk Operations

The PostgreSQL backend has full support for SOCI's bulk operations interface.

#### Transactions

Transactions are also fully supported by the PostgreSQL backend.

#### blob Data Type

The PostgreSQL backend supports working with data stored in columns of type Blob, via SOCI's `blob` class with the exception that trimming is not supported.

#### rowid Data Type

The concept of row identifier (OID in PostgreSQL) is supported via SOCI's rowid class.

#### Nested Statements

Nested statements are not supported by PostgreSQL backend.

#### Stored Procedures

PostgreSQL stored procedures can be executed by using SOCI's procedure class.

### Acessing the native database API

SOCI provides access to underlying datbabase APIs via several `get_backend()` functions, as described in the beyond SOCI documentation.

The PostgreSQL backend provides the following concrete classes for navite API access:

Accessor Function | Concrete Class
--- | ---
`session_backend * session::get_backend()` | `postgresql_session_backend`
`statement_backend * statement::get_backend()` | `postgresql_statement_backend`
`blob_backend * blob::get_backend()` | `postgresql_blob_backend`
`rowid_backend * rowid::get_backend()` | `postgresql_rowid_backend`

### Backend-specific extensions

None.

### Configuration options

To support older PostgreSQL versions, the following configuration macros are recognized:

- `SOCI_POSTGRESQL_NOBINDBYNAME` - switches off the query rewriting.
- `SOCI_POSTGRESQL_NOPARAMS` - disables support for parameterized queries (binding of use elements), automatically imposes also the `SOCI_POSTGRESQL_NOBINDBYNAME` macro. It is necessary for PostgreSQL 7.3.
- `SOCI_POSTGRESQL_NOPREPARE` - disables support for separate query preparation, which in this backend is significant only in terms of optimization. It is necessary for PostgreSQL 7.3 and 7.4.

Copyright © 2004-2008 Maciej Sobczak, Stephen Hutton