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)
