Pavel,
Could you provide some example? I don't really understand which placeholder
I can use.
On Sun, Oct 18, 2009 at 1:20 AM, Pavel Stehule <pavel.steh...@gmail.com>wrote:
> 2009/10/17 Ilya Urikh <ilya.ur...@gmail.com>:
> > 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);
> >
>
> Hello
>
> my reply is little bit offtopic. Why you convert date values to
> string? You can use placeholders and execute query with parameters in
> native format. It's more simply and safe.
>
> Regards
> Pavel Stehule
>
>
>
>
> > 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.
> >
>
--
Best regards,
Ilya Urikh.
