Re: invalid input syntax for integer: "NULL"

On Tue, 20 Feb 2007, 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.
> The following code is for reproducing:
>
> CREATE TABLE test(
> bh INT8
> );
>
> CREATE OR REPLACE FUNCTION testinsertion(intornull bigint) RETURNS text AS
> $$
> DECLARE
> BEGIN
> RETURN 'INSERT INTO test (bh) VALUES ('||COALESCE(intornull, 'NULL')||')';

I think you'd need something like
COALESCE(CAST(intornull AS TEXT), 'NULL')
in order to make that work. You want the output to effectively be a string
which contains the int to be concatenated with the other strings or the
string 'NULL' to be concatentated with the other strings.