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, -@#= %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, -@#= %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


For putf or getf, the conversion specifier is a % followed by a two character
encoding. The first character indicates the type class while the second
character identifies the data type within that class.

The byteaptr and textptr are really bytea and text. The "ptr" extension
indicates that only a pointer assignment should occur rather than a copy.

Most of the below types are already implemented. Some are still being
worked on.

Character types:
cc "char"
ct text, varchar, char
cT textptr

Boolean types:
bb bool

Numeric Types:
n2 int2
n4 int4
n8 int8
nf float4
nd float8
nn numeric

Bytea types:
Bb bytea
BB byteaptr

Geometric types:
gp point
gl lseg
gb box
gc circle
gP path
gy polygon

Network addrress types:
Ni inet/cidr
Nm macaddr

Monetary types:
mm money

Array types:
aa array

Date and time types:
dt time, timetz
dd date
dT timestamp, timestamptz
di interval

Object identifier types:
oi oid


PQputf use:

SPEC PGTYPE ARGTYPE BYTES NOTES
cc "char" int 1

ct text char* strlen+1 NUL-terminated string that gets copied internally

cT textptr char* strlen+1 NUL-terminated string that does not get copied

bb bool int 1

n2 int2 int 2

n4 int4 int 4

n8 int8 long long 8

nf float4 double 4

nd float8 double 8

nn numeric -- --

Bb bytea size_t, char* -- Specify byte len of the bytea, copys internally

BB byteaptr size_t, char* -- Specify byte len of the bytea, no copy

gp point PGpoint* --

gl lseg PGlseg* --

gb box PGbox* --

gc circle PGcircle* --

gP path PGpath* --

gy polygon PGpolygon* --

Ni inet/cidr PGinet* --

Nm macaddr PGmacaddr* --

mm money double 4 or 8

aa array -- --

dt time -- --

dd date -- --

dT timestamp -- --

di interval -- --

oi oid uint 4



PQgetf use:

NOTE: All get arguments must include the field_num followed by the below ARGTYPE.

size_t bytea_len;
char **bytea;
int field_num = 0;
PQgetf(res, tup_num, "%BB", field_num, &bytea_len, &bytea);

SPEC PGTYPE ARGTYPE BYTES NOTES
cc "char" int* 1

ct text size_t, char* -- Specify char buffer length followed by a buffer
Copies to provided buffer.

cT textptr char** -- Provides a direct pointer, no copying

bb bool int* 1

n2 int2 int* 2

n4 int4 int* 4

n8 int8 long long* 8

nf float4 double* 4

nd float8 double* 8

nn numeric -- --

Bb bytea size_t*, char* -- Specify char buffer length followed by a buffer.
Copies to provided buffer and assigns provided
size_t* to the bytea's length

BB byteaptr size_t*,char** -- Provides a direct pointer to bytea, no copying.
size_t* is assigned to bytea's length

gp point PGpoint* --

gl lseg PGlseg* --

gb box PGbox* --

gc circle PGcircle* --

gP path PGpath* --

gy polygon PGpolygon* --

Ni inet/cidr PGinet* --

Nm macaddr PGmacaddr* --

mm money double 4 or 8

aa array -- --

dt time -- --

dd date -- --

dT timestamp -- --

di interval -- --

oi oid uint 4



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Andrew Chernow <ac@esilo.com> writes:
> This proposal extends libpq by adding a printf style functions for
> sending and recveiving through the paramterized interface.

I think a printf-style API is fundamentally a bad idea in this context.
printf only works well when the set of concepts (datatypes, format
specifiers, etc) is small and fixed; neither of which adjectives
describe PG's set of datatypes. You've already had to go to
two-character format codes in order to have even slightly mnemonic codes
for the most commonly used built-in types; that doesn't look like it's
going to scale up for long. And what are you going to do about add-on
data types, such as contrib stuff, PostGIS and other add-on projects,
or user-defined types?

> 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, -@#= %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:

I find the idea of embedding state like that into the PGconn to be
pretty horrid, as well. It makes the design non-reentrant --- consider
the example of wanting to execute a query during the process of
computing parameters for a later query. If there's merit in the idea
at all, expose PGparam as a separate (but opaque) data structure that is
explicitly passed into and out of the functions that are concerned with
it.

> * PQexecParams
> * PQexecPrepared
> * PQsendQueryParams
> * PQsendQueryPrepared

You can't just randomly change the behavior of existing API functions.

> Date and time types:
> dt time, timetz
> dd date
> dT timestamp, timestamptz
> di interval

I'm not sure whether timestamp/timestamptz can or should be treated
as interchangeable; but time and timetz definitely are not.

BTW, how will this code react to the inevitable future changes in
binary formats? As examples, show what you'd do with

* the 8.2-to-8.3 change in the width of type money

* the likely future change to type timestamptz to store original
timezone explicitly

* the likely future change to type text to store encoding/collation
info explicitly

If the answer is that libpq will be unable to deal with these
events, I think the proposal is dead in the water. There's a reason
why we aren't pushing client-side use of binary formats very hard:
in many cases those formats are subject to change.

There might be some value in the concept of building up parameter
values in a PGparam object before passing it to an eventual PQexec-like
function. However, I see no reason to tie that concept to the
use of binary parameter format.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match