Re: Problem with using snprintf in C-function - Mailing list pgsql-general

2009/10/17 Ilya Urikh <ilya.urikh@gmail.com>:
> Pavel,
>
> Could you provide some example? I don't really understand which placeholder
> I can use.
>

here is one fragment from orafce:
it's for delete statement, but SELECT statement is similar

        ItemPointer tid;
        Oid argtypes[1] = {TIDOID};
        char nulls[1] = {' '};
        Datum values[1];
        void *plan;

        tid = &rettuple->t_data->t_ctid;

        if (!(plan = SPI_prepare("DELETE FROM ora_alerts WHERE ctid = $1",
1, argtypes)))
            ereport(ERROR,
                (errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION),
                 errmsg("SPI_prepare failed")));

        values[0] = ItemPointerGetDatum(tid);

        if (SPI_OK_DELETE != SPI_execute_plan(plan, values, nulls, false, 1))
            ereport(ERROR,
                (errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION),
                errmsg("can't execute sql")));

        SPI_finish();
        return PointerGetDatum(rettuple);

I don't found better samples, but both functions are well documented.

your code should look like:

Datum params[3];
char nulls[3] = {' ',' ',' '} ;
Oid types[3] = {INT8OID, DATEOID, DATEOID};
void *plan;

  params[0] = PG_GETARG_DATUM(0);
  params[1] = PG_GETARG_DATUM(1);
  params[2] = PG_GETARG_DATUM(2);

  nulls[0] = PG_ARGISNULL(0) ? 'n' : ' ';
  ...

plan = SPI_prepare("SELECT ...",3, types);
if (plan == NULL)
  ... raise exception

result = SPI_execute_plan(plan, params, nulls, true, -1);
...

Regards
Pavel Stehule.


> On Sun, Oct 18, 2009 at 1:20 AM, Pavel Stehule <pavel.stehule@gmail.com>
> wrote:
>>
>> 2009/10/17 Ilya Urikh <ilya.urikh@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.
>