Re: INTERVAL data type and libpq - what format?

Thank you guys for your input, I really appreciate.

It's a while I haven't posted on this list and be happy to get fast and
accurate answers...

As I wrote in a previous mail, I maintain a database driver for our 4GL runtime
system, allowing Informix 4gl applications to use PostgreSQL.

In this context, we have unknown SQL statements to prepare/execute, so it's
note easy to patch the SQL on the fly to add some cast clauses as Sam
suggested...

Regarding your suggestion to use libpqtypes.h:

As a dev tool provider, we cannot force our customers to rely on add-ons
or extensions. Our driver must work with a standard PostgreSQL database.

By the way,

I would also feel more comfortable if the type ids to be passed to the
paramTypes[] array would be provided in a public header file.

I don't understand why this is not published...

Many thanks,
Seb

Merlin Moncure wrote:
> On Tue, May 19, 2009 at 8:17 AM, Sebastien FLAESCH <sf(at)4js(dot)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
>