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!).
I can manage to bind only basic INTERVAL values for all sort of INTERVAL
columns, no problem...
I did further tests using the "PnnnYnnnM ..." ISO format and that is
working much better.
However I would expect at least 2 classes of INTERVALs to be specified
in libpq parameters:
INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND(n)
Also: I still have the overflow issue with types like INTERVAL SECOND.
=> discussed in another thread "INTERVAL SECOND limited to 59 seconds?"
Thanks a lot!
Seb
Sam Mason wrote:
On Tue, May 19, 2009 at 10:08:37AM +0200, Sebastien FLAESCH wrote:
I try to use the new 8.4 INTERVAL type with libpq, but get crazy with
the input formatting rules...
I think you're giving the database conflicting instructions and it's
getting confused.
fprintf(stdout,"++ Preparing INSERT ...\n");
paramTypes[0] = 23; /* INT4 */
paramTypes[1] = 1186; /* INTERVAL */
paramTypes[2] = 1186; /* INTERVAL */
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.
Thus when you do:
paramValues[0] = "1";
paramValues[1] = "-12345 years";
paramValues[2] = " 123 11:00";
r = PQexecPrepared(c, "s1", 3, paramValues, NULL, NULL, 0);
It's interpreting " 123 11:00" correctly as a basic INTERVAL value and
then casting it to your more constrained version as you're saving in the
table.
However, when you do:
paramValues[0] = "2";
paramValues[1] = "-12345";
paramValues[2] = " 123 11";
r = PQexecPrepared(c, "s1", 3, paramValues, NULL, NULL, 0);
You get an error because " 123 11" isn't a valid literal of an
(undecorated) INTERVAL type. I think PG may do the right thing if you
don't specify the types when preparing the query, but haven't tested.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
