Re: INTERVAL data type and libpq - what format?

Tom Lane wrote:
> Sam Mason <sam@samason.me.uk> writes:
>> I don't really know 8.4, but I believe you're saying here that you
>> explicitly want the values to be of basic INTERVAL type here, i.e. not
>> INTERVAL DAY TO HOUR for parameter 3.
>
> Right, you can get the equivalent behavior from psql thus:
>
> regression=# select '-12345'::interval::interval year;
>  interval
> ----------
>  00:00:00
> (1 row)
>
> regression=# select '12 11'::interval::interval year;
> ERROR:  invalid input syntax for type interval: "12 11"
> LINE 1: select '12 11'::interval::interval year;
>                ^
>
> There is not any way to bind a more specific type to a parameter at the
> protocol level.
>
>> I think PG may do the right thing if you
>> don't specify the types when preparing the query, but haven't tested.
>
> Yeah, that should work (though I haven't verified it either).  Another
> common trick is to specify the type in the text of the query by casting
> the parameter symbol:
>
>     PQprepare( ... $2::INTERVAL YEAR ... );
>
> I'd say this is better style than hard-wiring numeric type OIDs into
> your code.

Remember we are implementing a database driver with equivalent features
and an ODBC driver for PostgreSQL, executing queries with ? parameter
placeholders in the SQL text...

Since SQL Parameter types are not known at (4gl language-level) PREPARE
time, we wait for the (4gl) EXECUTE time to do the real PQprepare() with
paramTypes[]... (this is a pity by the way since we can't get any SQL
error at PREPARE time).

It's not that easy for us to add the ::<type> clauses because the conversion
of the ? placeholders to $n is done at PREPARE time, when types are not yet
yet... so this means major rewriting...

But this is all internal stuff you are not interested in, the main question
I would like to ask is:

What versions of PostgreSQL are 100% sure supporting the $n::<type> clauses?

We have to support all PostgreSQL versions, starting from 8.0 ...

Thanks
Seb