Re: Pgsql dynamic statements and null values

Ragnar Hafstað wrote:
> On Tue, 2005-03-08 at 16:30 -0600, Guy Rouillier wrote:
>> We use a dynamic statement in a pgsql stored function to insert rows
>> into a table determined at run time. After much debugging, I've
>> discovered that a null incoming argument will cause the dynamic
>> statement to evaluate to null. The error message emitted is "unable
>> to execute null statement."
>
> can't you use COALESCE() ?

Thanks Ragnar and Martijn for the replies. NULLIF doesn't seem applicable here as I already have a null value coming in, so I'm not comparing it to anything. I had tried COALESCE before my original post and it produced the same result: cannot execute null statement. However, your prompting motivated me to try a couple more alternatives. Of the many I tried, here is one that works:

coalesce(quote_literal(inval), 'NULL')

Nice to know, but given the verbosity, I think I'll stick with my check_null(inval). A worthwhile exercise, though, since I can now reduce that function to this one line.

--
Guy Rouillier