Re: INTERVAL data type and libpq - what format?

On Tue, May 19, 2009 at 8:17 AM, Sebastien FLAESCH <sf@4js.com> wrote:
> Yes, good point.
>
> I realize now that I would have expected libpq to give me a way to specify
> the exact decoration or precision of INTERVAL parameters...
>
> As you can do with ODBC's SQLBindParameter(), where you specify the C type,
> SQL type, precision/scale or length ...
> I believe this is important when it comes to data type conversion (for ex,
> when you want to insert a numeric/date/time into a char or the other way).
> => sort of cast, actually...
>
> I known libpq functions like PQexecParams() or PQexecPrepared() have a
> paramFormats[] parameter to specify if the buffer will hold a string
> or the binary representation of the value... but that would not help
> much (I don't want to deal with internal structures!).

You might want to take a look at 'libpqtypes'.  It exposes the
internal formats binary formats in easy to use structures.

e.g. (in libpqtypes.h)
typedef struct
{
    int years;
    int mons;
    int days;
    int hours;
    int mins;
    int secs;
    int usecs;
} PGinterval;

I was curious, and decided to see what happens when you inserted an
interval with the following code snippet:

PGinterval i;

memset(&i, 0, sizeof(i));
i.secs = 1000000;

PQputf(p, "%interval", &i);
PQparamExec(c, p, "insert into foo values ($1)", 0);

select * from foo;
     i
-----------
 277:46:40

also, libpqtypes always sends in binary which is much faster for the
date/time types.

http://libpqtypes.esilo.com/

merlin