Re: INTERVAL data type and libpq - what format? - Mailing list pgsql-general
From | Sebastien FLAESCH |
---|---|
Subject | Re: INTERVAL data type and libpq - what format? |
Date | |
Msg-id | 4A12A350.5020000@4js.com Whole thread Raw |
In response to | Re: INTERVAL data type and libpq - what format? (Sam Mason <sam@samason.me.uk>) |
Responses |
Re: INTERVAL data type and libpq - what format?
Re: INTERVAL data type and libpq - what format? |
List | pgsql-general |
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. > >
pgsql-general by date: