Re: [GENERAL] Normalize INTERVAL ouput format in a db driver
I could manage to identify/describe interval fields by testing the different values returned by PQfmod(). Could someone confirm that PQfmod() returns will not change in future versions? I have seen in the docs that there is a deprecated compile-time option that defines how time, timestamp and intervals are stored (using double or int64), I guess this should not affect the value returned by PQfmod()... right? Thanks! Seb Sebastien FLAESCH wrote: Further, little libpq question: When using INTERVAL types, can I rely on PQfmod(), PQfsize() to determine the exact definition of the INTERVAL precision? = what YEAR/MONTH/DAY/HOUR/MINUTE/SECOND fields where used to create the column. I get different values for the type modifier, but how to interpret this? Can someone point me to the documentation or source code where I can find more details about this? I found this: http://www.postgresql.org/docs/8.4/static/libpq-exec.html#LIBPQ-EXEC-SELECT-INFO But there are not much details in PQfmod() description... Thanks! Seb Sebastien FLAESCH wrote: Hi all, Just testing 8.4rc2 INTERVALs... According to the doc, INTERVAL output format is controlled by SET intervalstyle. I am writing an interface/driver and need a solution to fetch/convert interval values independently from the current format settings... I could force my driver to implicitly set the intervalstyle to iso_8601, but I would prefer to leave this in the hands of the programmer... Imagine you have to write and ODBC interface/driver with libpq that must support the SQLINTERVAL C structure, how would you deal with PostgreSQL intervals? Is it possible to query the current intervalstyle? Thanks! Seb -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] INTERVAL documentation bug?
I believe the documentation (Data Types section) is missing one INTERVAL field: HOUR TO SECOND http://www.postgresql.org/docs/8.4/static/datatype-datetime.html The interval type has an additional option, which is to restrict the set of stored fields by writing one of these phrases: YEAR MONTH DAY HOUR MINUTE SECOND YEAR TO MONTH DAY TO HOUR DAY TO MINUTE DAY TO SECOND HOUR TO MINUTE MINUTE TO SECOND It's actually supported: test1= create table tab1 ( iv interval hour to second ); CREATE TABLE test1= insert into tab1 values ( 'PT444H59M59S' ); INSERT 0 1 test1= select * from tab1; iv --- 444:59:59 (1 row) Seb -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Normalize INTERVAL ouput format in a db driver
Albe Laurenz wrote: Sebastien FLAESCH wrote: According to the doc, INTERVAL output format is controlled by SET intervalstyle. I am writing an interface/driver and need a solution to fetch/convert interval values independently from the current format settings... I could force my driver to implicitly set the intervalstyle to iso_8601, but I would prefer to leave this in the hands of the programmer... Imagine you have to write and ODBC interface/driver with libpq that must support the SQLINTERVAL C structure, how would you deal with PostgreSQL intervals? Is it possible to query the current intervalstyle? You can use SHOW intervalstyle to get the current setting. Would it be an option to use the to_char(interval, text) function to convert the interval value to a string you can understand? That would make you independent of server parameters. Another way to go is to retrieve the interval values in binary format. That would make you dependent on the setting of integer_datetimes, but it might still be easier. Yours, Laurenz Albe Thanks for the tip, I found SHOW after sending my initial mail. I will however go by forcing a given intervalstyle in a first time, this is not critical (I cannot use the internal binary format). My main concern now is to describe properly the type of interval which is used in a SELECT list, with the PQfmod() and PQfsize() libpq functions, I need some doc/spec here... Thanks a lot! Seb -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Normalize INTERVAL ouput format in a db driver
Hi all, Just testing 8.4rc2 INTERVALs... According to the doc, INTERVAL output format is controlled by SET intervalstyle. I am writing an interface/driver and need a solution to fetch/convert interval values independently from the current format settings... I could force my driver to implicitly set the intervalstyle to iso_8601, but I would prefer to leave this in the hands of the programmer... Imagine you have to write and ODBC interface/driver with libpq that must support the SQLINTERVAL C structure, how would you deal with PostgreSQL intervals? Is it possible to query the current intervalstyle? Thanks! Seb -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Normalize INTERVAL ouput format in a db driver
Further, little libpq question: When using INTERVAL types, can I rely on PQfmod(), PQfsize() to determine the exact definition of the INTERVAL precision? = what YEAR/MONTH/DAY/HOUR/MINUTE/SECOND fields where used to create the column. I get different values for the type modifier, but how to interpret this? Can someone point me to the documentation or source code where I can find more details about this? I found this: http://www.postgresql.org/docs/8.4/static/libpq-exec.html#LIBPQ-EXEC-SELECT-INFO But there are not much details in PQfmod() description... Thanks! Seb Sebastien FLAESCH wrote: Hi all, Just testing 8.4rc2 INTERVALs... According to the doc, INTERVAL output format is controlled by SET intervalstyle. I am writing an interface/driver and need a solution to fetch/convert interval values independently from the current format settings... I could force my driver to implicitly set the intervalstyle to iso_8601, but I would prefer to leave this in the hands of the programmer... Imagine you have to write and ODBC interface/driver with libpq that must support the SQLINTERVAL C structure, how would you deal with PostgreSQL intervals? Is it possible to query the current intervalstyle? Thanks! Seb -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INTERVAL SECOND limited to 59 seconds?
This is of course unexpected to me (one day becomes an hour)... Actually I would even expect an error, because there are missing interval parts. To represent a valid day to second interval, you should write '1 00:00:00' ... '1' would be a valid day to day interval. Always providing all interval units would clarify the user code (avoid complex rules to get defaults), IMHO. Just to compare with IFX interval literals: == select interval( 1, day to second ) from systables where tabid=1; 201: A syntax error has occurred. Error in line 1 Near character position 37 select interval( 1 ) day to second from systables where tabid=1; 1262: Non-numeric character in datetime or interval. Error in line 1 Near character position 36 select interval ( 1 11:22:33 ) day to second from systables where tabid=1; (constant) 1 11:22:33 1 row(s) retrieved. select interval ( 1 ) day to day from systables where tabid=1; (constant) 1 1 row(s) retrieved. == Seb Ron Mayer wrote: Finally got around to looking at this thread. Looks like the original questions from the thread got resolved, but I found this behaviour surprising: regression=# select interval '1' day to second; interval -- @ 1 hour (1 row) Should this be 1 second? If so I can send a patch. regression=# select version(); version --- PostgreSQL 8.4beta2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.2.4 (Ubuntu 4.2.4-1ubuntu3), 32-bit (1 row) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Need for official 0.83 release
Hi all, Is there any plan to make 0.83 stable release? Maybe some roadmap / plans on the website would help, just to give an idea of the future versions (availability, features). Thanks! Seb -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need for official 0.83 release
Oups sorry wrong mailing list. Wanted to ask about next FreeTDS release. I apologies, Seb PS: It's just to take data from an SQL Server and store it with PostgreSQL, I promise ;-) Sebastien FLAESCH wrote: Hi all, Is there any plan to make 0.83 stable release? Maybe some roadmap / plans on the website would help, just to give an idea of the future versions (availability, features). Thanks! Seb -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INTERVAL SECOND limited to 59 seconds?
Thank you Tom for looking at this. I would be pleased to help on testing the fix when available. My plan is to store Informix INTERVALs (coming from the 4gl applications we support) into PostgreSQL INTERVALs, and I have a bunch of tests for that... I believe Informix INTERVALs (and related operators and functions) are not 100% SQL99, but they are close... Thanks a lot! Seb Tom Lane wrote: Sebastien FLAESCH s...@4js.com writes: I would expect that an INTERVAL SECOND can store more that 59 seconds. I took a look into the SQL spec and I think that we do indeed have a spec compliance issue here. SQL99 section 4.7 saith Within a value of type interval, the first field is constrained only by the interval leading field precision of the associated interval qualifier. Table 8, Valid values for fields in INTERVAL values, specifies the constraints on subsequent field values. [ Table 8 says about what you'd expect, eg 0..23 for HOUR ] Values in interval fields other than SECOND are integers and have precision 2 when not the first field. SECOND, however, can be defined to have an interval fractional seconds precision that indicates the number of decimal digits maintained following the decimal point in the seconds value. When not the first field, SECOND has a precision of 2 places before the decimal point. So in other words, 999 seconds is a valid value for a field of type INTERVAL SECOND, *and should come out the same way*, not as 00:16:39, and certainly not as 00:00:39. It might be a relatively easy fix to not truncate the input value incorrectly. I haven't looked, but I think we should look now, because 8.4 has already changed the behavior in this area and it would be good not to change it twice. The focus of the 8.4 work was to make sure that we would correctly interpret the values of spec-compliant interval literals, but this example shows we are not there yet. We are fairly far away from being able to make it print out as the spec would suggest, because interval_out simply doesn't have access to the information that the field is constrained to be INTERVAL SECOND rather than some other kind of interval. We also have got no concept at all of interval leading field precision, only of interval fractional seconds precision, so constraining the leading field to only a certain number of integral digits isn't possible either. I don't foresee anything getting done about either of those points for 8.4. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INTERVAL data type and libpq - what format?
Tom Lane wrote:
Ron Mayer rm...@cheapcomplexdevices.com writes:
Sam Mason wrote:
You get an error because 123 11 isn't a valid literal of an
(undecorated) INTERVAL type.
Hmm. should it be?
Well, we do allow it if it's *explicitly* stated to be a day to hour
interval:
regression=# select interval '123 11' day to hour;
interval
---
123 days 11:00:00
(1 row)
What's at issue here is what should happen without that context.
I'm inclined to think this is ambiguous enough that accepting it
silently isn't such a great idea. I'm also not convinced that the
SQL spec says we must --- the syntax for interval literal does
not appear to allow omitting the fields specification.
In a related example,
regression=# select interval '123 11' day;
interval
--
134 days
(1 row)
we seem to be adding the 123 and 11 together. This is, um,
surprising behavior ... I'd be inclined to think throwing an
error is more appropriate.
regards, tom lane
FYI, with Informix, you don't pass a string in interval literals;
You pass digits directly and the interval qualifiers are mandatory:
===
select interval('123 11') from systables where tabid=1;
201: A syntax error has occurred.
Error in line 1
Near character position 27
select interval(123 11) from systables where tabid=1;
201: A syntax error has occurred.
Error in line 1
Near character position 25
select interval(123 11) day to hour from systables where tabid=1;
1261: Too many digits in the first field of datetime or interval.
Error in line 1
Near character position 37
select interval(123 11) day(3) to hour from systables where tabid=1;
(constant)
123 11
1 row(s) retrieved.
===
Seb
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] INTERVAL data type and libpq - what format?
Hello,
I try to use the new 8.4 INTERVAL type with libpq, but get crazy with
the input formatting rules...
I use PQprepare() / PQexecPrepared() with parameter list, binding the
INTERVAL values with the 1186 pg_type and passing a string buffer with
values like:
12345 for an INTERVAL YEAR
The INSERT works without error, but when selecting rows from the table
in psql, I get 00:00:00 values ?!?!
When inserting the value 12345 from the psql command tool it works...
I must be doing something wrong, but I could not find any documentation
on using INTERVAL in libpq...
Can someone from the hackers just tell me if it's supposed to work and
if yes what format is expected by the client library?
Attached, you have a test case to reproduce the problem.
Thanks a lot!
Seb
/*
Version:8.4.beta1
Created by: s...@4js.com
Problem with INTERVAL input format
--
After executing this program, 2 rows are present in the table.
Only the first has the expected values...
Why does the second insert fail to insert 123 11 in INTERVAL DAY TO HOUR?
Diagnostic info:
SQL State: 22007
Message : invalid input syntax for type interval: 123 11
Why does the third row show 00:00:00 in first INTERVAL YEAR column?
[...@fox problems]$ psql test1 -U pgsuser
psql (8.4beta1)
Type help for help.
test1= select * from t1;
k | i1 |i2
---+--+---
1 | -12345 years | 123 days 11:00:00
3 | 00:00:00 | 123 days 11:00:00
(2 rows)
When inserting rows with psql, the format used by the C program are supported:
test1= insert into t1 values ( 4, '-12345', '123 11' );
INSERT 0 1
test1= select * from t1 where k=4;
k | i1 |i2
---+--+---
4 | -12345 years | 123 days 11:00:00
(1 row)
So what am I doing wrong here?
*/
#include stdio.h
#include libpq-fe.h
static int checkResult(PGresult * r)
{
if (r == NULL)
return 0;
switch (PQresultStatus(r)) {
case PGRES_COMMAND_OK:
case PGRES_TUPLES_OK:
return 1;
default:
return 0;
}
}
static void getErrorInfo(PGresult * r)
{
if (r == NULL)
return;
fprintf(stderr, Diagnostic info:\n);
fprintf(stderr, SQL State: %s\n, PQresultErrorField(r,
PG_DIAG_SQLSTATE));
fprintf(stderr, Message : %s\n, PQresultErrorField(r,
PG_DIAG_MESSAGE_PRIMARY));
}
int main(int argc, char **argv)
{
PGresult *r;
PGconn *c;
Oid paramTypes[10];
const char *paramValues[10];
fprintf(stdout,++ Connecting...\n);
c = PQconnectdb(dbname='test1' user='pgsuser' password='fourjs');
if (c == NULL) {
fprintf(stderr, Could not connect.\n);
exit(1);
}
fprintf(stdout,++ Creating table t1 ...\n);
r = PQexec(c, DROP TABLE t1);
PQclear(r);
r = PQexec(c, CREATE TABLE t1 ( k INT, i1 INTERVAL YEAR, i2 INTERVAL DAY
TO HOUR));
if (!checkResult(r)) {
fprintf(stderr, Could not create table 1.\n);
getErrorInfo(r);
exit(1);
}
PQclear(r);
fprintf(stdout,++ Preparing INSERT ...\n);
paramTypes[0] = 23; /* INT4 */
paramTypes[1] = 1186; /* INTERVAL */
paramTypes[2] = 1186; /* INTERVAL */
r = PQprepare(c, s1,
INSERT INTO t1 VALUES ( $1, $2, $3 ),
3, (const Oid *) paramTypes);
if (!checkResult(r)) {
fprintf(stderr, Could not prepare stmt 1.\n);
getErrorInfo(r);
exit(1);
}
PQclear(r);
/* This is working */
fprintf(stdout,++ Executing INSERT (1) ...\n);
paramValues[0] = 1;
paramValues[1] = -12345 years;
paramValues[2] = 123 11:00;
r = PQexecPrepared(c, s1, 3, paramValues, NULL, NULL, 0);
if (!checkResult(r)) {
fprintf(stderr, Could not exec stmt 1.\n);
getErrorInfo(r);
exit(1);
}
PQclear(r);
/* This is NOT working */
fprintf(stdout,++ Executing INSERT (2) ...\n);
paramValues[0] = 2;
paramValues[1] = -12345;
paramValues[2] = 123 11;
r = PQexecPrepared(c, s1, 3, paramValues, NULL, NULL, 0);
if (!checkResult(r)) {
fprintf(stderr, Could not exec stmt 2.\n);
getErrorInfo(r);
/*exit(1);*/
}
PQclear(r);
/* This is NOT working */
fprintf(stdout,++ Executing INSERT (3) ...\n);
paramValues[0] = 3;
paramValues[1] = -12345;
paramValues[2] = 123 11:00;
r = PQexecPrepared(c, s1, 3, paramValues, NULL, NULL, 0);
if (!checkResult(r)) {
fprintf(stderr, Could not exec stmt 3.\n);
getErrorInfo(r);
exit(1);
}
PQclear(r);
PQfinish(c);
}
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] INTERVAL SECOND limited to 59 seconds?
Hello,
Can someone explain this:
test1= create table t1 ( k int, i interval second );
CREATE TABLE
test1= insert into t1 values ( 1, '-67 seconds' );
INSERT 0 1
test1= insert into t1 values ( 2, '999 seconds' );
INSERT 0 1
test1= select * from t1;
k | i
---+---
1 | -00:00:07
2 | 00:00:39
(2 rows)
I would expect that an INTERVAL SECOND can store more that 59 seconds.
Same question for INTERVAL MINUTE TO SECOND (but here we get an overflow error):
test1= create table t2 ( k int, i interval minute to second );
CREATE TABLE
test1= insert into t2 values ( 2, ':59' );
ERROR: interval field value out of range: :59
LINE 1: insert into t2 values ( 2, ':59' );
^
test1= insert into t2 values ( 2, '999:59' );
ERROR: interval field value out of range: 999:59
LINE 1: insert into t2 values ( 2, '999:59' );
^
test1= insert into t2 values ( 2, '99:59' );
ERROR: interval field value out of range: 99:59
LINE 1: insert into t2 values ( 2, '99:59' );
^
test1= insert into t2 values ( 1, '59:59' );
INSERT 0 1
test1= insert into t2 values ( 2, '-123:59' );
INSERT 0 1
test1= select * from t2;
k | i
---+---
1 | 00:59:59
2 | -00:59:00
(2 rows)
It's ok when using DAYs:
test1= create table t3 ( k int, i interval day to second );
CREATE TABLE
test1= insert into t3 values ( 1, '- 18:59:59' );
INSERT 0 1
test1= insert into t3 values ( 1, '999 18:59:59' );
INSERT 0 1
test1= select * from t3;
k | i
---+---
1 | - days +18:59:59
1 | 999 days 18:59:59
(2 rows)
Thanks a lot!
Seb
/*
Version:8.4.beta1
Created by: s...@4js.com
Problem with INTERVAL input format
--
After executing this program, 2 rows are present in the table.
Only the first has the expected values...
Why does the second insert fail to insert 123 11 in INTERVAL DAY TO HOUR?
Diagnostic info:
SQL State: 22007
Message : invalid input syntax for type interval: 123 11
Why does the third row show 00:00:00 in first INTERVAL YEAR column?
[...@fox problems]$ psql test1 -U pgsuser
psql (8.4beta1)
Type help for help.
test1= select * from t1;
k | i1 |i2
---+--+---
1 | -12345 years | 123 days 11:00:00
3 | 00:00:00 | 123 days 11:00:00
(2 rows)
When inserting rows with psql, the format used by the C program are supported:
test1= insert into t1 values ( 4, '-12345', '123 11' );
INSERT 0 1
test1= select * from t1 where k=4;
k | i1 |i2
---+--+---
4 | -12345 years | 123 days 11:00:00
(1 row)
So what am I doing wrong here?
*/
#include stdio.h
#include libpq-fe.h
static int checkResult(PGresult * r)
{
if (r == NULL)
return 0;
switch (PQresultStatus(r)) {
case PGRES_COMMAND_OK:
case PGRES_TUPLES_OK:
return 1;
default:
return 0;
}
}
static void getErrorInfo(PGresult * r)
{
if (r == NULL)
return;
fprintf(stderr, Diagnostic info:\n);
fprintf(stderr, SQL State: %s\n, PQresultErrorField(r,
PG_DIAG_SQLSTATE));
fprintf(stderr, Message : %s\n, PQresultErrorField(r,
PG_DIAG_MESSAGE_PRIMARY));
}
int main(int argc, char **argv)
{
PGresult *r;
PGconn *c;
Oid paramTypes[10];
const char *paramValues[10];
fprintf(stdout,++ Connecting...\n);
c = PQconnectdb(dbname='test1' user='pgsuser' password='fourjs');
if (c == NULL) {
fprintf(stderr, Could not connect.\n);
exit(1);
}
fprintf(stdout,++ Creating table t1 ...\n);
r = PQexec(c, DROP TABLE t1);
PQclear(r);
r = PQexec(c, CREATE TABLE t1 ( k INT, i1 INTERVAL YEAR, i2 INTERVAL DAY
TO HOUR));
if (!checkResult(r)) {
fprintf(stderr, Could not create table 1.\n);
getErrorInfo(r);
exit(1);
}
PQclear(r);
fprintf(stdout,++ Preparing INSERT ...\n);
paramTypes[0] = 23; /* INT4 */
paramTypes[1] = 1186; /* INTERVAL */
paramTypes[2] = 1186; /* INTERVAL */
r = PQprepare(c, s1,
INSERT INTO t1 VALUES ( $1, $2, $3 ),
3, (const Oid *) paramTypes);
if (!checkResult(r)) {
fprintf(stderr, Could not prepare stmt 1.\n);
getErrorInfo(r);
exit(1);
}
PQclear(r);
/* This is working */
fprintf(stdout,++ Executing INSERT (1) ...\n);
paramValues[0] = 1;
paramValues[1] = -12345 years;
paramValues[2] = 123 11:00;
r = PQexecPrepared(c, s1, 3, paramValues, NULL, NULL, 0);
if (!checkResult(r)) {
fprintf(stderr, Could not exec stmt 1.\n);
getErrorInfo(r);
exit(1);
}
PQclear(r);
/* This is NOT working */
fprintf(stdout,++ Executing INSERT (2) ...\n);
paramValues[0] = 2;
paramValues[1] = -12345;
paramValues[2] = 123 11;
r = PQexecPrepared(c, s1, 3, paramValues, NULL, NULL, 0);
Re: [GENERAL] INTERVAL SECOND limited to 59 seconds?
I think it should be clarified in the documentation... Actually I would like to use this new INTERVAL type to store IBM/Informix INTERVALs, which can actually be used like this with DATETIME types: create table t1 ( k int, dt1 datetime hour to minute, dt2 datetime hour to minute, i interval hour(5) to minute ); Table created. insert into t1 values ( 1, '14:45', '05:10', '-145:10' ); 1 row(s) inserted. select dt1 - dt2 from t1; (expression) 9:35- INTERVAL expression 1 row(s) retrieved. select 15 * ( dt1 - dt2 ) from t1; (expression) 143:45- INTERVAL expression 1 row(s) retrieved. The PostgreSQL documentation says: The interval type has an additional option, which is to restrict the set of stored fields by writing one of these phrases: YEAR MONTH DAY HOUR MINUTE SECOND YEAR TO MONTH DAY TO HOUR DAY TO MINUTE DAY TO SECOND HOUR TO MINUTE MINUTE TO SECOND Does that mean that the [field] option of the INTERVAL type is just there to save storage space? Confusing... Seb Richard Huxton wrote: Sebastien FLAESCH wrote: Hello, Can someone explain this: test1= create table t1 ( k int, i interval second ); CREATE TABLE test1= insert into t1 values ( 1, '-67 seconds' ); INSERT 0 1 test1= insert into t1 values ( 2, '999 seconds' ); INSERT 0 1 test1= select * from t1; k | i ---+--- 1 | -00:00:07 2 | 00:00:39 (2 rows) I would expect that an INTERVAL SECOND can store more that 59 seconds. I didn't even know we had an interval second type. It's not entirely clear to me what such a value means. Anyway - what's happening is that it's going through interval first. So - '180 seconds' will yield '00:03:00' and the seconds part of that is zero. The question I suppose is whether that's correct or not. An interval can clearly store periods longer than 59 seconds. It's reasonable to ask for an interval to be displayed as 61 seconds. If interval second means the seconds-only part of an interval though, then it's doing the right thing. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INTERVAL SECOND limited to 59 seconds?
Actually it's not limited to the usage of INTERVAL SECOND, I am writing a PostgreSQL driver for our 4GL virtual machine... I need to store all possible Informix INTERVAL types such as: INTERVAL MONTH(8) TO MONTH INTERVAL DAY(8) TO MINUTE INTERVAL SECOND TO FRACTION(5) ... etc ... ... If PostgreSQL is not able to store months 11, hours 23 and minutes or seconds 59, it looks like I will need to deal with PostgreSQL's INTERVAL YEAR TO MONTH INTERVAL DAY TO SECOND(5) ... and make conversions, to store all possible Informix INTERVALs... Seb -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INTERVAL data type and libpq - what format?
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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INTERVAL data type and libpq - what format?
Thank you guys for your input, I really appreciate.
It's a while I haven't posted on this list and be happy to get fast and
accurate answers...
As I wrote in a previous mail, I maintain a database driver for our 4GL runtime
system, allowing Informix 4gl applications to use PostgreSQL.
In this context, we have unknown SQL statements to prepare/execute, so it's
note easy to patch the SQL on the fly to add some cast clauses as Sam
suggested...
Regarding your suggestion to use libpqtypes.h:
As a dev tool provider, we cannot force our customers to rely on add-ons
or extensions. Our driver must work with a standard PostgreSQL database.
By the way,
I would also feel more comfortable if the type ids to be passed to the
paramTypes[] array would be provided in a public header file.
I don't understand why this is not published...
Many thanks,
Seb
Merlin Moncure wrote:
On Tue, May 19, 2009 at 8:17 AM, Sebastien FLAESCH s...@4js.com wrote:
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!).
You might want to take a look at 'libpqtypes'. It exposes the
internal formats binary formats in easy to use structures.
e.g. (in libpqtypes.h)
typedef struct
{
int years;
int mons;
int days;
int hours;
int mins;
int secs;
int usecs;
} PGinterval;
I was curious, and decided to see what happens when you inserted an
interval with the following code snippet:
PGinterval i;
memset(i, 0, sizeof(i));
i.secs = 100;
PQputf(p, %interval, i);
PQparamExec(c, p, insert into foo values ($1), 0);
select * from foo;
i
---
277:46:40
also, libpqtypes always sends in binary which is much faster for the
date/time types.
http://libpqtypes.esilo.com/
merlin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INTERVAL data type and libpq - what format?
Tom Lane wrote: Sam Mason s...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Suggestion for parameterized queries
Richard Huxton wrote: Sebastien FLAESCH wrote: Hi, Parameterized queries (PREPARE/EXECUTE), is a great thing, but it would even be better if the DECLARE CURSOR could use a PREPAREd statement, to declare a server-side cursor with a parameterized query, to use the benefits of DECLAREd cursors (no fetching of all the result set on the client, use the binary mode, use the scrollable option or the FOR UPDATE option). For now we cannot use DECLARE CURSOR in our PostgreSQL driver because of this limitation I could build the SQL statement with literal values, but since you have now parametrized queries I would prefer to use that... You can define a function that returns a cursor, if that is of any use to you. Thanks for the idea, but actually we need that as a basic SQL feature, because we write a database driver. How to write a generic function that can execute any SELECT statement? I can imagine that one param of the function would be the SQL text, but what about passing a variable list of parameters? Seb ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] Suggestion for parameterized queries
Hi, Parameterized queries (PREPARE/EXECUTE), is a great thing, but it would even be better if the DECLARE CURSOR could use a PREPAREd statement, to declare a server-side cursor with a parameterized query, to use the benefits of DECLAREd cursors (no fetching of all the result set on the client, use the binary mode, use the scrollable option or the FOR UPDATE option). Something like: PREPARE s1 (type,..) AS SELECT DECLARE c1 (value,...) CURSOR FROM s1 FETCH c1 or have a new OPEN instruction to pass parameters: PREPARE s1 (type,..) AS SELECT DECLARE c1 CURSOR FROM s1 OPEN c1 USING (value,...) FETCH c1 That would be an incredible improvement to make PostgreSQL compete with other databases like Informix, Oracle, Db2 UDB, SQL Server (I wrote some drivers for these database servers). For now we cannot use DECLARE CURSOR in our PostgreSQL driver because of this limitation I could build the SQL statement with literal values, but since you have now parametrized queries I would prefer to use that... Thank you! Sebastien FLAESCH Database driver writer at www.4js.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] Suggestion for parameterized queries
Hi, Parameterized queries (PREPARE/EXECUTE), is a great thing, but it would even be better if the DECLARE CURSOR could use a PREPAREd statement, to declare a server-side cursor with a parameterized query, to use the benefits of DECLAREd cursors (no fetching of all the result set on the client, use the binary mode, use the scrollable option or the FOR UPDATE option). Something like: PREPARE s1 (type,..) AS SELECT DECLARE c1 (value,...) CURSOR FROM s1 FETCH c1 or have a new OPEN instruction to pass parameters: PREPARE s1 (type,..) AS SELECT DECLARE c1 CURSOR FROM s1 OPEN c1 USING (value,...) FETCH c1 That would be an incredible improvement to make PostgreSQL compete with other databases like Informix, Oracle, Db2 UDB, SQL Server (I wrote some drivers for these database servers). For now we cannot use DECLARE CURSOR in our PostgreSQL driver because of this limitation I could build the SQL statement with literal values, but since you have now parametrized queries I would prefer to use that... Thank you! Sebastien FLAESCH Database driver writer at www.4js.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
