[GENERAL] invalid input syntax for integer: "NULL"

[Yonatan Ben-Nes]

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