PGparam proposal - Mailing list pgsql-hackers

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