[GENERAL] Problem with using snprintf in C-function

[Ilya Urikh]

Hi,

I have a problem with snprintf function which insert the variables to string
with error.
This code I wrote for PostgreSQL 8.3.7 on Fedora 10 and it worked fine. But
now I use CentOS 5.3 and PostgreSQL 8.3.8.

Function:
Datum calculateAccount(PG_FUNCTION_ARGS) {
    int64   accountId = PG_GETARG_INT64(0);
    DateADT startDate = PG_GETARG_DATEADT(1);
    DateADT endDate = PG_GETARG_DATEADT(2);

    char    command[QUERY_MAX_SIZE];
    char    startDateStr[MAXDATELEN + 3];
    char    endDateStr[MAXDATELEN + 3];

    snprintf(startDateStr, sizeof (startDateStr), "'%s'",
DatumGetCString(DirectFunctionCall1(date_out, startDate)));
    snprintf(endDateStr, sizeof (endDateStr), "'%s'",
DatumGetCString(DirectFunctionCall1(date_out, endDate)));
    elog(INFO, startDateStr);
    elog(INFO, endDateStr);
    snprintf(command, sizeof (command),
            "SELECT serviceId, periodStartDate, periodEndDate\
             FROM   accountServiceBaseView\
             WHERE  accountId = %ld AND\
                    periodStartDate <= %s AND\
                    periodEndDate >= %s;",
            accountId, startDateStr, endDateStr);
    elog(INFO, command);

    PG_RETURN_BOOL(true);
}

Result:
select calculateaccount(123, '01-01-2009', '01-02-2009');
INFO:  '2009-01-01'
INFO:  '2009-02-01'
INFO:  SELECT serviceId, periodStartDate, periodEndDate FROM
accountServiceBaseView WHERE  accountId = 123 AND periodStartDate <= (null)
AND periodEndDate >= '2009-01-01';


If I change the order of parameters to "periodStartDate <= %s AND
periodEndDate >= %s AND accountId = %ld", all works fine. Unfortunately this
method can not be applied to other functions of my module.


-- 
Best regards,
Ilya Urikh.