Re: INTERVAL data type and libpq - what format?

On Tue, May 19, 2009 at 10:12 AM, Sam Mason <sam(at)samason(dot)me(dot)uk> wrote:
> On Tue, May 19, 2009 at 02:17:20PM +0200, Sebastien FLAESCH wrote:
>> 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...
>
> Tom sent a message, but it seems to have got lost somewhere.  The
> suggestion was to leave the paramTypes empty and just write the prepared
> statement as:
>
>  INSERT INTO tbl (k,i1,i2) VALUES ($1::INT,$2::INTERVAL YEAR,$3::INTERVAL);
>
> or similar.  That way PG will be able to infer that $1 will be a literal
> of integer type, $2 will be of INTERVAL YEAR and so on.  In fact for
> queries such as this I don't think you even need to put those casts in
> there as PG will be able to figure out what you mean automatically (i.e.
> it does a limited form of type inference).
>
>> 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!).
>
> Yes, stay away from binary types if at all possible!
>

For the record, I disagree with this sentiment strongly. I would
rather see the client side library be buffed up so you have an
opportunity to deal with the data structures the way the server sees
them. The more complex the type is (like date time types), the
bigger the win both in terms of performance and feature exposure. I
understand though that cross database support is impossible though.

merlin