PGparam proposal

We will have a 0.6 patch tomorrow. This is not a patch, its a proposal.

The implementation has been adjusted and is now a simple printf-style
interface. This is just a design proposal to see if people like the
idea and interface. Up to this point, we have not provided a formal
proposal; just a few patches with some explainations.

We would appreciate feedback!

DESIGN PROPOSAL

This proposal extends libpq by adding a printf style functions for
sending and recveiving through the paramterized interface. In
addition, a number of structs were introduced for storing the
binary version of built-in pgtypes.

RATIONALE

*) Removes the need to manually convert values to C types.

*) Simplifies use of binary interface, putting or getting values

*) Provide simple structures for many pgtypes, such as polygon,
which are not documented for client use.

*) Promotes use of parameterized API, which has performance and
security benefits.

*) Support for arrays is a major plus; w/o parsing or dealing with
the binary format.

*) Only requires 4 new functions to exports.txt.

INTERFACE

*) PQputf
*) PQgetf
*) PQexecParamsf
*) PQsendQueryParamsf

NOTE: Only PQputf and PQgetf are required for this interface to work.
With that in mind, the other two are really cool :)

int PQputf(PGconn *conn, const char *paramspec, ...);

PQputf offers a way of packing pgtypes for use with the parameterized
functions. One or more values can be put at the same time. The params
are stored within the PGconn struct as a PGparam structure (internal
API only). The paramspec describes the pgtypes that you want to put.
In the paramspec, anything other than a valid conversion specifiers is
ignored. "%n4, -(at)#= %n8" is treated the same way as "%n4%n8".
Once all params have been put, one of four paramterized functions that
are aware of PGparam can be used:

* PQexecParams
* PQexecPrepared
* PQsendQueryParams
* PQsendQueryPrepared

For a list of PQputf conversion specifiers, see format_spec.txt.

Example:

PGpoint pt = {1.2, 4.5};

/* This puts an int4, int8, point and a text */
PQputf(conn, "%n4 %n8 %gp %cT", 100, 123LL, &pt, "text");

/* execute: Only the conn, command and resultFormat args are used. */
PQexecParams(conn, "INSERT INTO t VALUES ($1,$2,$3,$4)",
0, NULL, NULL, NULL, NULL, 1);

int PQgetf(
const PGresult *res,
int tup_num,
const char *fieldspec,
...);

PQgetf offers a way of getting result values from binary results. It
currently offers the ability to get from text results as well, but we
are not sure this should be supported. PQgetf is really a way of
getting binary results. In the fieldspec, anything other than a valid
conversion specifier is ignored. "%n4, -(at)#= %n8" is treated the same
way as "%n4%n8".

For a list of PQgetf conversion specifiers, see format_spec.txt.

Example:

int i4;
long long i8;
PGpoint pt;
char *text;

/* From tuple 0, get an int4 from field 0, an int8 from field 1, a point
* from field 2 and a text from field 3.
*/
PQgetf(res, 0, "%n4 %n8 %gp %cT", 0, &i4, 1, &i8, 2, &pt, 3, &text);

PUT & EXEC

We also propose two other functions that allow putting parameters and
executing all in one call. This is basically a wrapper for PQputf +
exec/send. These are the natural evolution of PQputf.

extern PGresult *PQexecParamsf(
PGconn *conn,
const char *cmdspec,
int resultFormat,
...);

extern int PQsendQueryParamsf(
PGconn *conn,
const char *cmdspec,
int resultFormat,
...);

Example:

int format = 1;
PGpoint pt = {1.2, 4.5};

/* 2 step example */
PQputf(conn, "%n4 %n8 %gp %cT", 100, 123LL, &pt, "text");
PQexecParams(conn, "INSERT INTO t VALUES ($1,$2,$3,$4)",
0, NULL, NULL, NULL, NULL, 1);

/* 1 step example */
PQexecParamsf(conn, "INSERT INTO t VALUES (%n4, %n8, %gp, %cT,)",
format, 100, 123LL, &pt, "text");

This causes the four params to be put. Then the parameterized function
arrays are built and the below query is executed.

INSERT INTO t VALUES ($1, $2, $3, $4)

If you use PQputf prior to execf/sendf, then those parameters are included.
Doing this is basically appending more params during the exec/send call.

PQputf(conn, "%n4", 100);
PQexecParamsf(conn, "INSERT INTO t VALUES (%cT, $1)", format, "text");

Resulting query assigns an int4 to $1 and a text to $2.

INSERT INTO t VALUES ($2, $1)

andrew & merlin