libpqtypes

• Full support for binary and text format (parameter and result)
• Full support for composites, arrays and composite arrays. (binary format only)
• printf style interface to libpq's binary parameterized API. (PQputf)
• scanf style interface for getting values: PQgetvalue extension. (PQgetf)
• Ability to register user-defined types, aliases and data type sub-classes for use with printf style interfaces: ex. "%mytype".
• Per-thread global error message: (PQgeterror)
• Online docs as well as man pages.

Portability

• Windows 2000, XP, Vista, 2003 server, 2008 server (msvc 6,7,8, cygwin, mingw)
• Linux 2.4 - 2.6 kernels (redhat/centos/gentoo/ubuntu/suse)
• OSX (darwin) 10.4 and 10.5
• FreeBSD 6.3
• Solaris 2.5.1 and 9 SPARC
• Solaris 8 and 10 Intel
• SCO OpenServer 5
• SCO UnixWare 7.1.4
• AIX 4.3 POWER3, 6.2 POWER4
• HPUX 10.20 PA-RISC, 11.23 IA64 (Itanium)

#include <libpqtypes.h>

/* Always call first on any conn that is to be used with libpqtypes */
PQtypesRegister(conn);

/* Instead of using PQexecParams and friends .... */
int resultFormat = 1;
PGparam *param = PQparamCreate(conn);

/* Pack one or more parameters into a PGparam. */
PQputf(param, "%int4 %text", 654321, "some text");

/* Execute a parameterized query. */
PGresult *res = PQparamExec(conn, param, 
  "INSERT INTO t VALUES ($1, $2)", resultFormat);
PQclear(res);

/* Release all resources used by `param'. */
PQparamClear(param);

/* Or, do all of the above in a single statement!  This will put an 
 * int4 and text into a temporary PGparam and execute 
 * "INSERT INTO t VALUES ($1, $2)".
 */
PGresult *res = PQexecf(conn, 
  "INSERT INTO t VALUES (%int4, %text)", 654321, "some text");
PQclear(res);

/* Let's get a composite.
 * CREATE TYPE simple AS (a int4, t text);
 */
PGint4 i4;
PGtext text;
PGresult *simple;
const char *composites[] = {"simple"};

/* Your composites need to be registered */
PQregisterComposites(conn, composites, 1);

/* 2nd arg, PGparam, can be NULL if there are no query params.
 * Composites require binary results, so we can't use PQexec().
 */
res = PQparamExec(conn, NULL, "SELECT my_simple FROM t", resultFormat);
if(!res)
  fprintf(stderr, "ERROR: %s\n", PQgeterror());

/* Get the simple composite, which is exposed as a PGresult. */
PQgetf(res, 0, "%simple", 0, &simple);
PQclear(res); /* no longer needed */

/* Get the simple composite attributes from the simple result.
 * Reference fields by name by using a '#' rather than a '%'.
 * The field names are the composite attribute names.
 */
PQgetf(simple, 0, "#int4 #text", "a", &i4, "t", &text);
PQclear(simple);

NOTE:
This requires using postgresql 8.4 or a patched version of 8.3.x. libpqtypes requires a new 8.4 feature called libpq-events. To learn more about this feature, see the 8.4 docs. The 8.3 patch can be found here: libpq 8.3 patch.

pgFoundry

Source Code

Documentation

Package Documents
README
INSTALL
LICENSE
AUTHORS
ChangeLog

Overview Documents
How to Put and Get data types
Describes every structure used by libpqtypes: like PGtimestamp or PGpolygon.
How to handle Composites
Detailed examples for simple, nested and arrays of composites.
Writing Data Type Handlers
Documents structures used by type handlers; includes examples.

Managing PGparam
PQparamCreate
PQparamCount
PQparamReset
PQparamClear

Putting Parameters
PQputf
PQputvf

Executing Commands
PQexecf
PQexecvf
PQsendf
PQsendvf
PQparamExec
PQparamExecPrepared
PQparamSendQuery
PQparamSendQueryPrepared

Getting Result Values
PQgetf
PQgetvf

Registering Types
PQregisterTypeHandler deprecated
PQregisterComposites
PQregisterUserDefinedTypes
PQregisterSubClasses

Error System
PQgeterror
PQseterror
PQgetErrorField

Misc. Functions
PQspecPrepare
PQtypesRegister
PQlocalTZInfo

Contact