Re: binary representation of datatypes

On Tue, Oct 21, 2008 at 4:21 AM, Matthieu Imbert
<matthieu(dot)imbert(at)ens-lyon(dot)fr> wrote:
> Dear postgresql hackers,
>
> I would like to be able to get results from SQL commands directly in a
> binary format, instead of a textual one. Actually, I want to be able to
> get timestamps with their full precision (microsecond).
>
> googling around i found some threads on this mailing list about this:
> http://archives.postgresql.org/pgsql-interfaces/2007-05/msg00047.php
> http://archives.postgresql.org/pgsql-interfaces/2007-06/msg00000.php
> http://archives.postgresql.org/pgsql-interfaces/2007-03/msg00007.php
>
> From these threads and from postgresql source code, i figured out how to
> get timestamp data the way i need it:
>
> - make a PQexecParams asking for results in binary format.
> - convert the returned 64 bits integer from network representation to
> host representation (reverse the order of the bytes or do nothing,
> depending on the endianness of the platform)
> - the resulting 64 bits integer is the number of microseconds since
> 2000-01-01
> - convert this number of microseconds as needed.
> (my test code currently only handles the case where timestamps are int64)
>
> This works great but i have a few questions:
> - Is the binary representation of data (especially timestamps) subject
> to change in the future?
> - wouldn't it be a good think if functions for dealing with this binary
> representation are made available to client code (for example:
> pq_getmsgint64 in src/backend/libpq/pqformat.c or timestamp2tm in
> src/interfaces/ecpg/pgtypeslib/timestamp.c). Doing so would ensure that
> client code does not have to reimplement things already correctly done
> in postgres (with all special cases, and correct error handling), and
> would not be broken if the internals change. Moreover it would remove
> from client code the burden to handle both cases of timestamp as int64
> or timestamp as double.
>
> In short, what i would like (as a libpq client code writer), is a
> function which given an opaque binary representation of a timestamp
> returns me the timestamp as a number of microseconds since 2000-01-01,
> and a function which given a timestamp as a number of microseconds since
> 2000-01-01 returns me a structure similar to pg_tm, but without loss of
> information (with microseconds). Of course, this would be needed not
> only for timestamps but also for other types.
>
> If this is not possible, at least what i would like is to be sure that
> the code i write for converting timestamp binary representation will not
> be broken by future postgresql release, and is portable.

you really want to look at libpqtypes. It does exactly what you want,
as well as provides easy to follow binary handlers for every basic
type.

http://pgfoundry.org/projects/libpqtypes/
http://libpqtypes.esilo.com/

merlin