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.
best regards,
--
Matthieu Imbert