invalid input syntax for integer: "NULL"

["Yonatan Ben-Nes" <yonatan@xxxxxxxxxxx>]

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')||')';
END;
$$ LANGUAGE plpgsql;


When I run: SELECT testinsertion(5);  OR  SELECT testinsertion(NULL);

ERROR:  invalid input syntax for integer: "NULL"
CONTEXT:  SQL statement "SELECT  'INSERT INTO test (bh) VALUES ('||COALESCE( $1 , 'NULL')||')'"
PL/pgSQL function "testinsertion" line 4 at return



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:

SELECT testinsertion(5);
          testinsertion
----------------------------------
 INSERT INTO test (bh) VALUES (5)
(1 row)

SELECT testinsertion(NULL);
 testinsertion
---------------

(1 row)

Thanks a lot in advance,
  Yonatan Ben-Nes