PGparam proposal v2 - Mailing list pgsql-hackers

Here is our updated PGparam extension to the libpq api:
http://archives.postgresql.org/pgsql-hackers/2007-12/msg00356.php

We have a patch implementing the following which we are cleaning up.  We 
are also kicking around some ideas for arrays and possibly composite 
types which we may consider if the community wants to move forwards with 
this proposal.

Tom made a number of comments some of which we have addressed:

*) Separate PGparam from PGconn: we agree with this and separated them.

*) Chanages to existing API functions: we agreed and moved new behavior 
to new functions

*) 3rd party types: we now support this through a type registration 
interface

*) Internal type changes: We think changes to binary format are fairly 
rare and easily addressed.

*) Type confusion was removed by giving each type its own specifier.

*) Objections to printf: We agreed in part: we moved to natural names, 
from %n4 to %pgint for example.  This addressed scalability concerns and 
should be less cryptic to use.

*) Argument passing in putf and getf is identical to the previous 
proposal.  All we changed was the naming schema for the %spec and putf 
now takes a PGparam rather than a PGconn.


* API INTERFACE

/* opqaue */
typedef struct pg_param PGparam;

PGparam *PQparamCreate(PGconn *conn);

/* manually reset a param struct.  This is done by * all execution functions for you. */
void PQparamReset(PQparam *param)

/* free a PGparam */
void PQparamClear(PQparam *param);

int PQputf(  PGparam *param,  const char *typeSpec,  ...);

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

/* PGparam Execution Functions */
PGresult *PQparamExec(  PGconn *conn,  PGparam *param,  const char *command,  int resultFormat);

int PQparamSendQuery(  PGconn *conn,  PGparam *param,  const char *command,  int resultFormat);

PGresult *PQparamExecPrepared  PGconn *conn,  PGparam *param,  const char *stmtName,  int resultFormat);

int PQparamSendQueryPrepared  PGconn *conn,  PGparam *param,  const char *stmtName,  int resultFormat);

/* All in wonder, no PGparam needed */
PGresult *PQexecParamsf(  PGconn *conn,  const char *commandSpec,  int resultFormat,  ...);
/* All in wonder, no PGparam needed */
int PQsendQueryParamsf(  PGconn *conn,  const char *commandSpec,  int resultFormat,  ...);

/* All in wonder, no PGparam needed */
PGresult *PQexecPreparedf(  PGconn *conn,  const char *stmtName,  const char *typeSpec,  int resultFormat,  ...);

/* All in wonder, no PGparam needed */
int PQsendQueryPreparedf(  PGconn *conn,  const char *stmtName,  const char *typeSpec,  int resultFormat,  ...);

/* gets the PGparam error message */
char *PQparamErrorMessage(const PGparam *param);


* TYPE ALIAS SPECIFIERS

The convention for postgresql built-in types is a % followed by the
type alias.  Every pgtype begins with "pg".  For example:
  %pgint4  %pgpolygon  %pgbox

3rd party types can register their own specifiers, which is discussed
int the TYPE HANDLER SYSTEM section.  Type aliases must be unique.


* TYPE HANDLER SYSTEM

typedef struct pg_typeputargs
{  /* The out buffer will be at least 16K. If more room is needed,   * use the PQ_TYPE_SETOUT to grow the buffer.  In
mostcases,   * 16K is plenty of room.   */  char *out;
 
  /* the size in bytes of the out buffer */  int outl;
  /* Should not use directly, see PQ_TYPE_SETOUT.  For the brave,   * set to 1 if you point the out buffer at memory
thatshould be   * freed after your put callback returns.   */  int free_out;
 
  /* The arguments to putf. Use PQ_TYPE_NEXTARG. */  va_list *ap;
  /* The type's alias name, like 'pgint8'. */  const char *type_alias;
  /* Sets an error message. This msg shows up in   * PQparamErrorMessage().   */  int (*seterr)(struct pg_typeputargs
*args,const char *format, ...);
 
} PGtypePutArgs;

typedef struct pg_typegetargs
{  const PGresult *res;  int tup_num;  int field_num;
  /* pointer to the output of PQgetvalue for this tup+field */  char *value;
  /* The arguments to getf. Use PQ_TYPE_NEXTARG. NOTE: the field_num   * supplied to getf has already been pulled out
ofthe va_list and   * assigned to this structs field_num member.   */  va_list *ap;
 
  /* The type's alias name, like 'pgint8'. */  const char *type_alias;
  /* Sets an error message. This msg shows up in   * PQresultErrorMessage().   */  int (*seterr)(struct pg_typegetargs
*args,const char *format, ...);
 
} PGtypeGetArgs;

#define PQ_TYPE_NEXTARG(typeArgs, type) va_arg(*(typeArgs)->ap, type)

/* makes sure that putArgs->out is larger enough for new_outl */
#define PQ_TYPE_SETOUT(putArgs, new_outl) do{ \  if((new_outl) > (putArgs)->outl) \  { \    (putArgs)->out = (char
*)malloc(new_outl);\    if(!(putArgs)->out) \      return -1; \    *(putArgs)->out = 0; \    (putArgs)->outl =
(new_outl);\    (putArgs)->free_out = 1; \  } \
 
} while(0)

/* * Returns - the number of bytes put or -1 for error. */
typedef int (*PGtypePut)(PGtypePutArgs *args);

/* * Returns - 0 for success or -1 for error. */
typedef int (*PGtypeGet)(PGtypeGetArgs *args);

/* Indicates that a type handler should use text format for puts. * The default is binary. */
#define PQ_TEXTPUTFMT 0x01

/* By default, PQputf will make an internal copy of the type data.  By * setting this flag, it will only maintain a
pointerto the type data. * * An example of this flag is the %pgtextptr and %pgbyteaptr, both * of which can consume
largeamounts of memory.  In most cases, it * is more effiecent to store a direct pointer rather than making a * copy.
*/
#define PQ_NOCOPYONPUT 0x02

/* register a type handler */
int PQregisterTypeHandler(  const char *alias,  Oid oid,  PGtypePut put,  PGtypeGet get,  int flags);


* TYPE HANDLER EXAMPLES

Below are two examples of how to implement a libpq type handler.  The 
postgresql int4 and text types are always available and would never need 
to be registered.  This is just an example.

PQregisterTypeHandler(  "pgint4", /* The %alias used by PQputf/PQgetf, must be unique */  INT4OID,  /* Oid of the type.
notrequired - can be InvalidOid */  put_int4, /* put callback for this type */  get_int4, /* get callback for this type
*/ 0);       /* no flags needed */
 

PQregisterTypeHandler(  "pgtext",  TEXTOID,  put_text,  get_text,  PQ_TEXTPUTFMT);

//
// PQputf(param, "%pgint4", 100);
//
static int put_int4(PGtypePutArgs *args)
{  *(int *)args->out = (int)htonl((int)PQ_TYPE_NEXTARG(args, int));  return 4;
}

//
// int n;
// PQgetf(res, tup_num, "%pgint4", field_num, &n);
//
static int get_int4(PGtypeGetArgs *args)
{  int *i4p = PQ_TYPE_NEXTARG(args, int *);
  if(!i4p)    return args->seterr(args, "%s out pointer is NULL",      args->type_alias);
  *i4p = 0;
  if(PQgetisnull(args->res, args->tup_num, args->field_num))    return 0;
  /* text format conversion */  if(PQfformat(args->res, args->field_num) == 0)  {    int n;
    errno = 0;    if((n = (int)strtol(args->value, NULL, 10)) == 0 && errno)      return args->seterr(args, "%s string
conversionfailed: [%d] %s",        args->type_alias, errno, strerror(errno));
 
    *i4p = n;    return 0;  }
  /* binary format conversion */  *i4p = (int)ntohl(*(unsigned int *)args->value);  return 0;
}


//
// PQputf(param, "%pgtext", text);
//
static int put_text(PGtypePutArgs *args)
{  args->out = PQ_TYPE_NEXTARG(args, char *);  return (args->out != NULL) ? (int)strlen(args->out) + 1 : 0;
}

//
// char text[512];
// PQgetf(res, tup_num, "%pgtext", field_num, sizeof(text), text);
//
static int get_text(PGtypeGetArgs *args)
{  size_t textl;  size_t bufl = PQ_TYPE_NEXTARG(args, size_t);  char *buf = PQ_TYPE_NEXTARG(args, char *);
  if(!buf)    return args->seterr(args, "%s out buffer is NULL",      args->type_alias);
  *buf = 0;
  if(PQgetisnull(args->res, args->tup_num, args->field_num))    return 0;
  textl = PQgetlength(args->res, args->tup_num, args->field_num);  if(bufl <= textl)    return args->seterr(args, "%s
bufferis too small",      args->type_alias);
 
  memcpy(buf, args->value, textl + 1);  return 0;
}