INTERVAL data type and libpq - what format?

[Sebastien FLAESCH <sf@xxxxxxx>]

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: sf@xxxxxxx

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?

[sf@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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general