pqt-handlers(3)
NAME
pqt-handlers - A manual for implementing libpqtypes type handlers.
DESCRIPTION
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
handler. `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
success. On error, a get routine must return -1.
PGregisterType
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
registration, 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.
PGtypeFormatInfo
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]; /* servers datestyle: like "SQL, MDY" */
/* When non-zero, server uses int64 timestamps */
int integer_datetimes;
} PGtypeFormatInfo;
PGrecordAttDesc
The PGrecordAttDesc structure defines the attributes of a composite.
Internally, libpqtypes keeps track of composite attributes using this
structure.
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;
PGtypeHandler
The PGtypeHandler structure represents all the properties of a type
handler. When a type is registered, this structure is used to catalog
the types 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;
PGtypeArgs
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. */
struct
{
/* 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. */
struct
{
/* The PGresult passed to PQgetf().
PGresult *result;
/* The tuple number */
int tup_num;
/* the tuple field number. */
int field_num;
} get;
};
USER-DEFINED TYPES
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
format 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);
/* -------------------------------
* EXAMPLE RGB IMPLEMENTATION
*/
#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 */
if(!rgb)
{
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);
if(!rgb)
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;
}
TYPE SUB-CLASSING
Sub-classing a type means extending the put or get routines of a
registered type handler. The idea came about from trying to provide a
convention 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
identifer. 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
useful.
By providing a put and get handler during alias registration, one has
effectively sub-classed the base type. This is called sub-class
registration.
By sub-classing a registered type, applications can now put and get
data using their own data structures. The sub-class put and get
routines 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 server-specific.
BENEFITS
1. Centralizes conversions from application data types to libpq data
types
2. Provides an easy all-inclusive interface for putting and getting
values
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
sockfd.
-- %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
work.
**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);
/* -------------------------------
* EXAMPLE EPOCH SUB-CLASS IMPLEMENTATION
*/
/* 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);
ts.date.isbc = 0;
ts.date.year = tm->tm_year + 1900; /* always 4-digit year */
ts.date.mon = tm->tm_mon;
ts.date.mday = 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 types put routine
* (the super class). In this case, the super class
* expects a PGtimestamp as input. The super function
* returns whatever the base types 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 *);
if(!t)
return args->errorf(args, "time_t* cannot be NULL");
/* zero user bits */
*t = 0;
/* Internally, this calls the base types 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;
}
COMPOSITES
To get and put composites, they must be registered. During registration, 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 information 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- classed *) The provided name cannot resolve to the backends RECORDOID *) The composite must exist at "conn" *) If no schema name is provided, the composite must be within the backends 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
EXAMPLES
None.
AUTHOR
A contribution of eSilo, LLC. for the PostgreSQL Database Management System. Written by Andrew Chernow and Merlin Moncure.
REPORTING BUGS
Report bugs to <[email protected]>.
COPYRIGHT
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 PURPOSE.
SEE ALSO
PQregisterTypes(), PQregisterResult()
Opportunity
Personal Opportunity - Free software gives you access to billions of dollars of software at no cost. Use this software for your business, personal use or to develop a profitable skill. Access to source code provides access to a level of capabilities/information that companies protect though copyrights. Open source is a core component of the Internet and it is available to you. Leverage the billions of dollars in resources and capabilities to build a career, establish a business or change the world. The potential is endless for those who understand the opportunity.
Business Opportunity - Goldman Sachs, IBM and countless large corporations are leveraging open source to reduce costs, develop products and increase their bottom lines. Learn what these companies know about open source and how open source can give you the advantage.
Free Software
Free Software provides computer programs and capabilities at no cost but more importantly, it provides the freedom to run, edit, contribute to, and share the software. The importance of free software is a matter of access, not price. Software at no cost is a benefit but ownership rights to the software and source code is far more significant.
Free Office Software - The Libre Office suite provides top desktop productivity tools for free. This includes, a word processor, spreadsheet, presentation engine, drawing and flowcharting, database and math applications. Libre Office is available for Linux or Windows.
Free Books
The Free Books Library is a collection of thousands of the most popular public domain books in an online readable format. The collection includes great classical literature and more recent works where the U.S. copyright has expired. These books are yours to read and use without restrictions.
Source Code - Want to change a program or know how it works? Open Source provides the source code for its programs so that anyone can use, modify or learn how to write those programs themselves. Visit the GNU source code repositories to download the source.
Education
Study at Harvard, Stanford or MIT - Open edX provides free online courses from Harvard, MIT, Columbia, UC Berkeley and other top Universities. Hundreds of courses for almost all major subjects and course levels. Open edx also offers some paid courses and selected certifications.
Linux Manual Pages - A man or manual page is a form of software documentation found on Linux/Unix operating systems. Topics covered include computer programs (including library and system calls), formal standards and conventions, and even abstract concepts.
