From: | Sebastien FLAESCH <sf(at)4js(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: INTERVAL data type and libpq - what format? |
Date: | 2009-05-19 12:17:20 |
Message-ID: | 4A12A350.5020000@4js.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
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.
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | hubert depesz lubaczewski | 2009-05-19 12:22:27 | Re: Get block of N numbers from sequence |
Previous Message | Grzegorz Jaśkiewicz | 2009-05-19 11:59:47 | Re: Get block of N numbers from sequence |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2009-05-19 12:45:50 | Re: INTERVAL data type and libpq - what format? |
Previous Message | Martijn van Oosterhout | 2009-05-19 11:44:07 | Re: Multiple sorts in a query |