Thread: Help with prepared statement

Help with prepared statement

From
Bendik Rognlien Johansen
Date:
Hello,
I am having some difficulty using a prepared statement.

PREPARE fooplan(int4, int4) AS SELECT * FROM records WHERE id = $1
AND fax = $2;

Normally this works fine like this:
EXECUTE fooplan(42985, 12345678);

However, sometimes I need to match NULL values like this:

EXECUTE fooplan(42985, NULL);

This fails of course since "fax = NULL" is not the same as "fax IS
NULL".

Is there any way around this?


Thanks!



Re: Help with prepared statement

From
Bendik Rognlien Johansen
Date:
Thank you that solved my problem!


On May 23, 2005, at 6:12 PM, Jan B. wrote:

> I have a suggestion for a workaround:
> What about using the following expression for comparison:
>
> WHERE (id=$1 OR (id ISNULL AND $1 ISNULL))
>
> Probably it will have a negative impact on performance.
>
> Jan Behrens
>
>
> Bendik Rognlien Johansen wrote:
>
>> Hello,
>> I am having some difficulty using a prepared statement.
>> PREPARE fooplan(int4, int4) AS SELECT * FROM records WHERE id =
>> $1  AND fax = $2;
>> Normally this works fine like this:
>> EXECUTE fooplan(42985, 12345678);
>> However, sometimes I need to match NULL values like this:
>> EXECUTE fooplan(42985, NULL);
>> This fails of course since "fax = NULL" is not the same as "fax
>> IS  NULL".
>> Is there any way around this?
>> Thanks!
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 3: if posting/reading through Usenet, please send an appropriate
>>      subscribe-nomail command to majordomo@postgresql.org so that
>> your
>>      message can get through to the mailing list cleanly
>>
>
>