On 02/20/2007 03:45:55 PM, Yonatan Ben-Nes wrote:
> Hi everyone,
>
> I'm trying to write a PL/pgSQL function which execute an insert, I
> encounter
> a problem when I try to insert NULL value into an integer field.
> RETURN 'INSERT INTO test (bh) VALUES ('||COALESCE(intornull,
> 'NULL')||')';
> And if I try to change the COALESCE second value at the function to
> NULL
> (instead of 'NULL') it works if a value is being passed to the
> integer field
> but doesn't work if a NULL Is passed:
NULL, without the quotes, is the proper way to write
NULL as a literal value. When you put quotes around it it's a
string. So that's why you get a type exception.
COALESCE chooses the first value that's not NULL. So if you
pass it NULL you may as well not supply the second argument.
You probably want a plpgsql IF statement or a CASE expression.
Karl <kop@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein