2009/10/17 Ilya Urikh <ilya.urikh@xxxxxxxxx>:
> 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@xxxxxxxxx>
> wrote:
>>
>> 2009/10/17 Ilya Urikh <ilya.urikh@xxxxxxxxx>:
>> > 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.
>
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
