namespace postgres

Summary

class Connection

class Connection
  : public std::enable_shared_from_this< Connection >

A connection to a PostgreSQL database.

Summary

Members

public Connection(Settingssettings)

Constructor.

Copy and move constructor have been explicitly deleted to prevent the copy of the connection object.

public ~Connection()

Destructor.

The destuctor will implicitly close the connection to the server and associated results if necessary.

publicConnection& connect(const char * connInfo)

Open a connection to the database.

Parameters

• connInfo A postgresql connection string. Both Keyword/Value and URI are accepted. The passed connInfo can be empty or null to use all default parameters (see Environment Variables).

postgresql://[user[:password]@][netloc][:port][/dbname][?param1=value1&...]

See also: https://www.postgresql.org/docs/9.5/static/libpq-connect.html#LIBPQ-CONNSTRING

Returns

The connection itself.

publicConnection& close() noexcept

Close the database connection.

This method is automatically called by the destructor.

publicConnection& cancel()

Cancel queries in progress.

Calling this method will request the cancellation of the current query in progress. There is no guaranty the query will be cancelled or when it will be cancelled. So usually once you've called this method you can't use anymore the connection since the previous query might still be in progress.

Returns

The connection ifself.

public template<typename... Args>
inlineResult& execute(const char * sql,Args... args)

Execute one or more SQL commands.

Parameters

• sql One or more SQL commands to be executed.

• args Zero or more parameters of the SQL command. If parameters are used, they are referred to in the sql command as $1, $2, etc... The PostgreSQL datatype of a parameter is deducted from the C++ type of the parameter according to the following table:

// Execute a query
auto results = cnx.execute("SELECT MAX(emp_no) FROM employees");

// Execute a query with parameters.
cnx.execute("UPDATE titles SET to_date=$1::date WHERE emp_no=$2", "1988-02-10", 10020);

// Execute multiple statements in one call.
cnx.execute(R"SQL(

  CREATE TYPE GENDER AS ENUM ('M', 'F');

  CREATE TABLE employees (
    emp_no      INTEGER         NOT NULL,
    birth_date  DATE            NOT NULL,
    first_name  VARCHAR(14)     NOT NULL,
    last_name   VARCHAR(16)     NOT NULL,
    gender      GENDER          NOT NULL,
    hire_date   DATE            NOT NULL,
    PRIMARY KEY (emp_no)
  );

)SQL");

When the parameter data type might be ambigious for the server, you can use the PostgreSQL casting syntax. In the example below both parameters are send as character varying but the server will perform the cast to date and integer.

cnx.execute("UPDATE titles SET to_date=$1::date WHERE emp_no::integer=$2", "1988-02-10", "10020");

The null value can be send as a parameter using the nullptr keyword.

cnx.execute("INSERT INTO titles VALUES ($1, $2, $3::date, $4)",
            10020, "Technique Leader", "1988-02-10", nullptr);

Parameters are only supported for a single SQL command. If execute() is called with more than one sql command and any of those commands are using parameters, execute() will fail.

Returns

The results of the SQL commands.

You can iterate over the returned result using the Result::iterator. When a query in a the sql command is a SELECT all the rows of the result must be fetched using the iterator before the connection can be reused for another command. For other commands such as an UPDATE or a CREATE TABLE, using the iterator is not required and the connection can be reused for the next command right away.

publicConnection& begin()

Start a transaction.

begin(), commit() and rollback() are helper methods to deal with transactions. The main benefit of using those methods rather than executing the SQL commands is on nested transactions. If a code start a transaction and call another code also starting a transaction, thoses methods will create only one transaction started at the first all to begin() and commited at the last call to commit().

Returns

The connection itself.

publicConnection& commit()

Commit a transaction.

Returns

The connection itself.

publicConnection& rollback()

Rollback a transaction.

Returns

The connection itself.

protected PGconn * pgconn_

The native connection pointer.

protectedResultresult_

Result of the current query.

protected std::string lastError() const noexcept

Last error messages sent by the server.

Returns

The error message most recently generated by an operation on the connection.

protected inline operator PGconn *()

Cast operator to the native connection pointer.

class ConnectionException

class ConnectionException
  : public runtime_error

Exception thrown on connection failure.

Summary

Members

public inline ConnectionException(const std::string & what)

Constructor.

Parameters

• what - The error message returned by the server.

class ExecutionException

class ExecutionException
  : public runtime_error

Exception thrown on any runtime error except a connection failure.

Summary

Members

public inline ExecutionException(const std::string & what)

Constructor.

Parameters

• what - The error message returned by the server.

class Result

class Result
  : public db::postgres::Row

A result from an SQL command.

SQL commands always return a result. For a SELECT command you can iterate through the result using the Result::iterator. Otherwise you can use the count() method to get the number of rows affected by the SQL command.

When executing more than one SQL command, the iterator can also be used to access the result of each command.

Summary

Members

classiterator

Support of the range-based for loops.

auto &result = cnx.exectute("SELECT ...");
for (auto &row: result) {
  ...
}

public uint64_t count() const noexcept

Number of rows affected by the SQL command.

This function can only be used following the execution of a SELECT, CREATE TABLE AS, INSERT, UPDATE, DELETE, MOVE, FETCH, or COPY statement, or an EXECUTE of a prepared query that contains an INSERT, UPDATE, or DELETE statement. If the command that generated the PGresult was anything else, count() returns 0.

Returns

The number of rows affected by the SQL statement.

publiciteratorbegin()

First row of the result.

Returns

An iterator pointing to the first row of the result.

publiciteratorend()

Last row of the result.

Returns

An iterator pointing to the past-the-end row of the result.

class iterator

Support of the range-based for loops.

auto &result = cnx.exectute("SELECT ...");
for (auto &row: result) {
  ...
}

Summary

Members

public inline iterator(Row* ptr)

Constructor.

publiciteratoroperator++()

Next row in the resultset.

public inline bool operator!=(constiterator& other)

public inlineRow& operator*()

class Row

A row in a Result.

Rows can be accessed using the Result::iterator except the first one that can be directly access through the result.

Summary

Members

public bool isNull(int column) const

Test a column for a null value.

Parameters

• column Column number. Column numbers start at 0.

Returns

true if the column value is a null value.

public template<typename T>
T as(int column) const

Get a column value.

int32_t emp_no = row.as<int32_t>(0);

The typename used to call the function must by compatible with the SQL type. The following table defines compatible types:

If the column value is null, the null value defined in the table above will be returned. To insure the column value is really null the method isNull() should be used.

Parameters

• column Column number. Column numbers start at 0.

Returns

The value of the column.

Calling this method with incompatible types is treated as programming errors, not user or run-time errors. Those errors will be captured by an assert in debug mode and the behavior in non debug modes is undertermined.

public template<typename T>
std::vector<array_item< T > > asArray(int column) const

Get a column values for arrays.

array_int32_t quarters = row.asArray<int32_t>(0);

Usage is the similar to using T as(int column) and binding between SQL names and C++ types are the same. Only bytea is not supported.

Only array of one dimention are supported.

Parameters

• column Column number. Column numbers start at 0.

Returns

The value of the column. The value is actually a vector of array_item. Each item has two properties (value and isNull).

public const char * columnName(int column) const

Get a column name.

Parameters

• column Column number. Column numbers start at 0.

Returns

The column name associated with the given column number.

cnx.execute("SELECT emp_no, firstname from employees").columnName(1) // → "firstname"

public int num() const noexcept

Get the row number.

Returns

For each row returned by a query, num() returns a number indicating the order of the row in the result. The first row selected has a num() of 1, the second has 2, and so on.

public template<typename T>
inline T get(int column) const

Get a column value.

Deprecated: use Row::as(int column) for replacement.

struct array_item

A values in an array.

Summary

Members

public T value

The value for non null values.

public bool isNull

true if the value is null.

public array_item() = default

public inline array_item(T v)

Constructor of a non null value.

Parameters

• v The value.

public inline array_item(std::nullptr_t)

Constructor of a null value.

public inline operator const T &() const

public inline bool operator==(constarray_item< T > & other) const

struct date_t

A date value.

This struct can be used set a date parameter when calling execute, or to get a date value from a Row. An alternative for date parameters is to use a date literal with an explict cast of the parameter in the sql command.

execute("SELECT $1::date", "2014-11-01");

Summary

Members

public int32_t epoch_date

Number of seconds sine Unix epoch time.

public inline operator int32_t() const

Cast to int32_t.

struct interval_t

An interval value.

Summary

Members

public int64_t time

Number of microseconds on the day since 00:00:00.

public int32_t days

Number of days.

public int32_t months

Number of months.

struct Settings

Settings of a PostgreSQL connection.

Those settings are internal setting of the libpqmxx library. Other standard PostgreSQL seetings should be set when calling connect().

Summary

Members

public bool emptyStringAsNull

If true, empty strings passed as parameters to exectute() are considered as null values.

struct time_t

A time value.

Summary

Members

public int64_t time

Number of microseconds since 00:00:00.

public inline operator int64_t() const

Cast to int64_t.

struct timestamp_t

A timestamp value (without time zone).

This struct can be used set a timestamp parameter when calling execute, or to get a timestamp value from a Row. An alternative for timestamp parameters is to use a timestamp literal with an explict cast of the parameter in the sql command.

execute("SELECT $1::timestamp", "2014-11-01T05:19:00");

Summary

Members

public int64_t epoch_time

Number of microsecondes since Unix epoch time.

public inline operator int64_t() const

Cast to int64_t.

struct timestamptz_t

A timestamp with timezone value.

This struct can be used set a timestamptz parameter when calling execute, or to get a timestamptz value from a Row. An alternative for timestamptz parameters is to use a timestamp literal with an explict cast of the parameter in the sql command.

execute("SELECT $1::timestamptz", "2014-11-01T05:19:00-500");

Summary

Members

public int64_t epoch_time

Number of microsecondes since Unix epoch time.

public inline operator int64_t() const

Cast to int64_t.

struct timetz_t

A time with timezone value.

Summary

Members

public int64_t time

Number of microseconds since 00:00:00.

public int32_t offset

Offset from GMT in seconds.