Message-ID: <4A1269E4.1060708@4js.com>
Date: Tue, 19 May 2009 10:12:20 +0200
From: Sebastien FLAESCH <sf@4js.com>
Organization: Four J's Development Tools
User-Agent: Thunderbird 2.0.0.9 (X11/20071031)
MIME-Version: 1.0
To: pgsql-interfaces@postgresql.org
Subject: INTERVAL data type and libpq - what format?
Content-Type: multipart/mixed; boundary="------------070407080205070508030302"

This is a multi-part message in MIME format.
--------------070407080205070508030302
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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


--------------070407080205070508030302
Content-Type: text/plain;
 name="libpqtest1.c"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="libpqtest1.c"

/*
Version:    8.4.beta1
Created by: sf@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?

[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);
}


--------------070407080205070508030302--