libpqtypes home page

pqt-handlers(3)                libpqtypes Manual               pqt-handlers(3)

       pqt-handlers - A manual for implementing libpqtypes type handlers.

       Type  handlers  are  I/O  routines  used  by libpqtypes for sending and
       receiving data for specific types.  Internally,  libpqtypes  uses  type
       handlers  to  support  PostgreSQL's  builtin base types: such as point,
       int4, timestamp, etc...

       NOTE: Builtin types always serialize parameters being sent "put" to the
       backend  in  binary  format;  user-defined types may choose to use text
       format.  C data types are translated into the backend's external binary
       format.   Even if text results are used, C data types are still exposed
       when getting result data.

       Type handlers have the below three properties:

              Type Specifier Name
              A [schema].type name that will be used to  reference  your  han-
              dler.  `man pqt-specs' for complete documentation on syntax.

              Put Routine
              A  PGtypeProc  put  routine  takes a C data type and converts it
              into a valid backend external format.  The converted  format  is
              used with libpq's parameterized API.  For instance: a C int data
              type is used to put a postgresql int4.  To  convert  this  to  a
              valid  external format, libpqtypes swaps the bytes (when needed)
              so they are in network order.  A put routine returns the  number
              of bytes being put.  On error, a put routine must return -1.

              Get Routine
              A PGtypeProc get routine does the opposite of a put routine.  It
              converts a type's text or binary external format to its native C
              type.   For instance: a postgresql int4 is converted to a C int.
              For binary results, the 4 bytes are converted to host order  and
              stored  as a C int.  A get routine returns zero to indicate suc-
              cess.  On error, a get routine must return -1.

       The PGregisterType structure is used by  all  PQregisterXXX  functions.
       It contains a typname, put and get routine.  The typname can optionally
       contain the type's schema, like pg_catalog.int4.   When  registering  a
       sub  class  via  PQregisterTypes,  an inheritence operator must be used
       within typname to indicate what type is  being  extended:  myint4=int4.
       If the typput and typget routines are NULL during a sub class registra-
       tion, the result is a direct sub class or alias of the base type:  like
       "s=text" allowing one to use "%s" instead of "%text".  When registering
       a composite, typput and typget are ignored.

              typedef struct
                   const char *typname;
                   PGtypeProc typput;
                   PGtypeProc typget;
              } PGregisterType;

       To implement a type handler, you need to  be  aware  of  4  structures:
       PGtypeFormatInfo,  PGrecordAttDesc,  PGtypeHandler and PGtypeArgs.  All
       exist for use with type handlers.

       The  PGtypeFormatInfo  structure   provides   useful   connection-based
       information  for  type  handlers.   For instance, your handler may have
       different implementations depending on the server version ..  sversion.

              typedef struct
                   int sversion;          /* server version, e.g. 70401 for 7.4.1 */
                   int pversion;          /* FE/BE protocol version in use */
                   char datestyle[32];    /* server's datestyle: like "SQL, MDY" */

                   /* When non-zero, server uses int64 timestamps */
                   int integer_datetimes;
              } PGtypeFormatInfo;

       The  PGrecordAttDesc  structure  defines the attributes of a composite.
       Internally, libpqtypes keeps track of composite attributes  using  this

              typedef struct
                   Oid attoid;    /* Oid of the attribute */
                   int attlen;    /* storage size of attribute.  -1 if not known */
                   int atttypmod; /* The typmod of attribute. */
                   char *attname; /* The name of the attribute. */
              } PGrecordAttDesc;

       The  PGtypeHandler  structure  represents  all the properties of a type
       handler.  When a type is registered, this structure is used to  catalog
       the type's information.

              typedef struct pg_typhandler
                   /* An internal libpqtypes assigned id for this type handler. */
                   int id;

                   /* The schema name of this type, which may be empty if not
                    * provided during registration.
                   char typschema[65];

                   /* The name of this type: like int2 or bytea, cannot be empty */
                   char typname[65];

                   /* The storage size of this type.  -1 if not known. */
                   int typlen;

                   /* The backend OID of the type. */
                   Oid typoid;

                   /* The backend array OID of the type. */
                   Oid typoid_array;

                   /* The put handler for this type. */
                   PGtypeProc typput;

                   /* The get handler for this type. */
                   PGtypeProc typget;

                   /* If this handler is a sub-class, this will be the 'id' of
                    * the super class type handler.  It is set to -1 if not
                    * a sub-class.
                   int base_id;

                   /* Indicates the number of composite attributes within the
                    * 'attDescs' array.  This is set to 0 for non-composites.
                   int nattrs;

                   /* If non-zero, the 'attDescs' pointer must be freed. */
                   int freeAttDescs;

                   /* The memory behind the 'attDescs' pointer when the number of
                    * attrs is less than 16.  When greater than 16, heap memory
                    * is used and 'freeAttDescs' is set to a non-zero value.
                   PGrecordAttDesc attDescsBuf[16];

                   /* An array of PGrecordAttDesc, one element per record
                    * attribute.  Must be freed if 'freeAttDescs' is non-zero.
                   PGrecordAttDesc *attDescs;
              } PGtypeHandler;

       The  PGtypeArgs  structure  is  passed to all put and get handlers.  It
       contains all values needed by type handlers.

              struct pg_typeargs
                   /* Indicates if this is a put or get operation. */
                   int is_put;

                   /* Formatting information. */
                   const PGtypeFormatInfo *fmtinfo;

                   /* Indicates if a request for a direct pointer was
                    * made, %text*.
                   int is_ptr;

                    * When is_put is non-zero, set this to 1 for binary and 0 for
                    * text format.  It defaults to binary.  When is_put is 0, this
                    * indicates the field type PQftype of get.field_num.
                   int format;

                   /* An argument list.  Arguments should be retrieved with va_arg. */
                   va_list ap;

                   /* The position of this typname within a specifier
                    * string, 1-based.
                   int typpos;

                   /* Type handler for the specifier at typpos. */
                   PGtypeHandler *typhandler;

                    * Report an error from within a handler.  This error message
                    * will show up in PQgeterror.
                    * This always returns -1 so one can report an error and return
                    * -1 from a handler in a single statement:
                    *   return args->errorf(args, "ERROR: %s", strerror(errno));
                    * errorf always prepends a small header
                    * "schema.typname[pos:num] - msg". For example, if the above
                    * failed within the int4 handler and typpos was 5, the
                    * resulting error message would be:
                    *   pg_catalog.int4[pos:5] - ERROR: Invalid argument
                    * errorf does not put any newlines in error message.
                   int (*errorf)(PGtypeArgs *args, const char *format, ...);

                   /* Used by type sub-class handlers.  When is_put is
                    * non-zero, a sub-class prepares type data and then calls
                    * super.  When is_put is zero, a sub-class first
                    * calls super to get the base class's deserialized value
                    * and can then convert it.
                   int (*super)(PGtypeArgs *args, ...);

                   /* This structure is used when is_put is non-zero. */
                        /* The PGparam structure passed to PQputf(). */
                        PGparam *param;

                        /* A buffer used to store the type's output format.  If
                         * more than 'outl' bytes are needed, see 'expandBuffer'.
                         * Normally data is copied to the out buffer, but it can
                         * also be pointed elsewhere: like a const string or static
                         * memory.  When repointing the out buffer, DO NOT use
                         * 'expandBuffer'.  Never use realloc on this buffer.
                        char *out;

                        /* The size in bytes of the 'out' buffer. If expandBuffer
                         * is used, this will reflect the new buffer length.
                        int outl;

                        /* Expands the 'out' buffer to 'new_len'.  If new_len is
                         * less than or equal to the current length 'outl', the
                         * expand request is ignored.  This behaves just like a
                         * realloc, existing data is copied to the new memory.
                         * You should never use realloc on the out buffer.
                         * Returns -1 on error and 0 for success.
                        int (*expandBuffer)(PGtypeArgs *args, int new_len);

                        /* internal use only. */
                        char *__allocated_out;
                   } put;

                   /* This structure is used when is_put is zero. */
                        /* The PGresult passed to PQgetf().
                        PGresult *result;

                        /* The tuple number */
                        int tup_num;

                        /* the tuple field number. */
                        int field_num;
                   } get;

       User-defined types are extended base types in the  backend.   They  are
       not domains or composites.  These types have their own input/output and
       send/recv functions (normally written in  C).   They  normally  include
       their  own operator functions and have an array oid.  For libpqtypes to
       make use of these types, especially for binary puts and  gets,  a  type
       handler  must  be  registered.   This  provides  libpqtypes with a type
       specifer, put and get routines for handling this type.

       User-defined types are registered on a per connection  basis  and  must
       exist  on  the  server.   If  the type does not exist, the registration
       fails.  If no schema name is provided during registration, the server's
       search  path is used to resolve the type's existence and fetch its oid.
       If a schema name is provided during registration, the  search  path  is
       not used.

   User-defined type example
       Assume  there  is  a  user-defined  type  named 'rgb' in the 'graphics'
       schema.  The text output format is always  in  hex:  '#ff0000'  with  a
       leading  pound sign and lowercase hex digits.  The external binary for-
       mat is a sequence of three unsigned bytes: r, g and  b.   To  use  this
       type with libpqtypes, it must be registered.

              /* register the rgb type */
              PGregisterType type = {"graphics.rgb", rgb_put, rgb_get};
              PQregisterTypes(conn, PQT_USERDEFINED, &type, 1, 0);

              /* put an rgb */
              rgb_t rgb = {218, 218, 218};
              PGparam *param = PQparamCreate(conn);
              PQputf(param, "%rgb", &rgb);

              /* get an rgb from tuple 0 field 4 */
              rgb_t rgb;
              PQgetf(result, 0, "%graphics.rgb", 4, &rgb);

              /* -------------------------------

              #define hex2dec(v) (unsigned char)(((v) > '9') ?  ((v) - 'a') + 10 : (v) - '0')

              /* example rgb struct */
              typedef struct
                unsigned char r;
                unsigned char b;
                unsigned char g;
              } rgb_t;

              /* RGB PGtypeProc handler - always puts in binary format */
              int rgb_put(PGtypeArgs *args)
                unsigned char *out;
                rgb_t *rgb = va_arg(args->ap, rgb_t *);

                /* If rgb is NULL, put an SQL NULL value */
                  args->put.out = NULL;
                  return 0;

                /* write the 3 bytes to the args out buffer */
                out = (unsigned char *)args->put.out;
                *out++ = rgb->r;
                *out++ = rgb->g;
                *out   = rgb->b;
                return 3; /* number of bytes the server should expect */

              /* RGB PGtypeProc handler */
              int rgb_get(PGtypeArgs *args)
                rgb_t *rgb = va_arg(args->ap, rgb_t *);
                char *value = PQgetvalue(args->get.result,
                        args->get.tup_num, args->get.field_num);

                  return args->errorf(args, "rgb* cannot be NULL");

                /* text format: ex. '#ff9966' */
                if(PQfformat(args->format) == 0)
                  value++; /* skip the '#' sign */
                  rgb->r = (hex2dec(value[0]) << 4) | hex2dec(value[1]);
                  rgb->g = (hex2dec(value[2]) << 4) | hex2dec(value[3]);
                  rgb->b = (hex2dec(value[4]) << 4) | hex2dec(value[5]);
                  return 0;

                /* binary format */
                rgb->r = (unsigned char)value[0];
                rgb->g = (unsigned char)value[1];
                rgb->b = (unsigned char)value[2];
                return 0;

       Sub-classing a type means extending the put or get routines of a regis-
       tered type handler.  The idea came about from trying to provide a  con-
       vention  for  registering  domains;  which amounts to simple aliases to
       libpqtypes.  Domain/alias registration would look like this:
              PGregisterType type = {"myint4=pg_catalog.int4", NULL, NULL};
              PQregisterTypes(conn, PQT_SUBCLASS, &type, 1, 0);

       The 'typname' member syntax  is:  [schema].type=[base_schema].base_type
       (schema  is  optional).   No spaces are allowed unless contained within
       the schema or type name, which would require double quoting the identi-
       fer.   By  passing  NULL  for  both  the put and get handlers, the base
       type's handlers are used.  Thus,  the  result  of  the  above  is  that
       "%myint4" and "%int4" behave identically.  But what happens if a put or
       get handler is provided during an alias registration?  Is  this  useful
       functionality  to applications?  The answer is sub-classing and yes its

       By providing a put and get handler during alias registration,  one  has
       effectively sub-classed the base type.  This is called sub-class regis-

       By sub-classing a registered type, applications can  now  put  and  get
       data  using  their own data structures.  The sub-class put and get rou-
       tines handle the dirty work of converting application structures to the
       base  type's  structure.   When sub-classing, no oid lookup occurs with
       the server.  The sub-class type is assumed to be application  specific.
       Sub-classes  are  registered on a per connection basis, just like user-
       defined types.  The reason for this is because the  base  type  can  be


       1.  Centralizes  conversions  from application data types to libpq data
       2. Provides an easy all-inclusive interface  for  putting  and  getting
       3. Allows applications to piggy-back off libpqtypes internal binary and
       text convertors
       4. Adds enormous flexiblity: (a few interesting ideas)
         -- %socket: sub-class the inet get routine  and  return  a  connected
         --  %file:  sub-class  the  text get routine and return a FILE* (text
       being a pathname)
         -- %filemd5: sub-class the bytea put routine and  supply  a  pathname
       that is used to
            md5 a file's contents, utlimately putting a 16 byte bytea.

       It  is  impossible  to  consider all of the uses for type sub-classing.
       The above ideas are probably more extreme than common  cases,  such  as
       taking  an  application  struct and converting it to what the base type
       expects.  But, the extreme cases are possible when desired.

   Sub-class example
       Assume you have an application that works with time_t  epoch  values  a
       lot.   It  would  be  useful if you could define a %epoch type handler.
       This avoids having to convert a time_t to  either  a  string  or  to  a
       PGtimestamp  (used  by the timestamp & timestamptz type handlers).  The
       problem is, to use the binary interface you would have to know  how  to
       serialize  a  timestamp  to  send/recv it from the server.  If you sub-
       class timestamptz, you can use PGtypeArgs.super  to  handle  the  dirty

       **NOTE:  %epoch  is  only  an example, it is not part of libpqtypes nor
       being proposed.  The goal here is to demonstrate  how  to  implement  a
       type  sub-class  handler.   It  is  important  to note that %epoch will
       announce itself as a timestamptz to the backend.  So when using %epoch,
       make sure the context allows a timestamptz.

              /* we are going to register this under the 'pqt' schema */
              PGregisterType type = {"pqt.epoch=pg_catalog.timestamptz", epoch_put, epoch_get};
              PQregisterTypes(conn, PQT_SUBCLASS, &type, 1, 0))

              /* putting an epoch */
              struct stat st;
              if(stat("/home/foobar/archive.tgz", &st) == 0)
                   PGparam *param = PQparamCreate(conn);
                   PQputf(param, "%epoch", st.st_mtime);

              /* getting an epoch value, using fully qualified type name */
              struct utimbuf ut = {0, 0};
              PQgetf(result, tup_num, "%pqt.epoch", field_num, &ut.modtime);

              /* -------------------------------

              /* convert a time_t to a PGtimestamp and call args->super() */
              int epoch_put(PGtypeArgs *args)
                   struct tm *tm;
                   PGtimestamp ts;
                   time_t t = va_arg(args->ap, time_t);

                   tm = localtime(&t);
            = 0;
            = tm->tm_year + 1900; /* always 4-digit year */
             = tm->tm_mon;
            = tm->tm_mday;
                   ts.time.hour   = tm->tm_hour;
                   ts.time.min    = tm->tm_min;
                   ts.time.sec    = tm->tm_sec;
                   ts.time.usec   = 0;
                   ts.time.gmtoff = tm->tm_gmtoff;

                   /* Internally, this calls the base type's put routine
                    * (the super class).  In this case, the super class
                    * expects a PGtimestamp as input.  The super function
                    * returns whatever the base type's put routine returns
                    * (which for all puts is the byte count or -1 on error).
                   return args->super(args, &ts);

              /* Calls args->super() to get a PGtimestamp and then converts
               * it to a time_t value.
              int epoch_get(PGtypeArgs *args)
                   PGtimestamp ts;
                   time_t *t = va_arg(args->ap, time_t *);

                        return args->errorf(args, "time_t* cannot be NULL");

                   /* zero user bits */
                   *t = 0;

                   /* Internally, this calls the base type's get routine,
                    * which returns 0 or -1 on error.
                   if(args->super(args, &ts) == -1)
                        return -1; /* args->errorf called by super already */

                   /* Since PGtimestamp contains an epoch member, we can
                    * just copy that value rather than calling mktime().
                   *t = (time_t)ts.epoch;
                   return 0;

       To  get  and put composites, they must be registered.  During registra-
       tion,  information  about  the  composite  type,  likes  its  OID   and
       attributes,  are looked up in the backend.  The composite must exist or
       the registration fails.  Do a `man pqt-composites(3)' for a more infor-
       mation about composites.

       Registering a composite type:

              CREATE TYPE simple AS (a int4, t text);
              PGregisterType type = {"simple", NULL, NULL};
              PQregisterTypes(conn, PQT_COMPOSITE, &type, 1, 0);

       *)  The  put  and  get routines must be NULL, composites cannot be sub-
       *) The provided name cannot resolve to the backend's RECORDOID
       *) The composite must exist at "conn"
       *) If no schema name is provided, the  composite  must  be  within  the
       backend's search path.

       During  registration of a composite, the below information is retreived
       from the backend:

       *) Oid of the composite type
       *) Array Oid of the composite type
       *) Type len of the compsoite type, PQfsize

       For each composite attribute:

       *) Oid of the attribute
       *) Name of the attribute
       *) Type len of the attribute, PQfsize
       *) The typmod of the attribute, PQfmod


       A contribution of eSilo, LLC. for the  PostgreSQL  Database  Management
       System.  Written by Andrew Chernow and Merlin Moncure.

       Report bugs to <>.

       Copyright (c) 2011 eSilo, LLC. All rights reserved.
       This is free software; see the source for copying conditions.  There is
       NO warranty; not even for MERCHANTABILITY or  FITNESS FOR A  PARTICULAR

       PQregisterTypes(), PQregisterResult()

libpqtypes                           2011                      pqt-handlers(3)

libpqtypes home page