Thread: invalid input syntax for integer: "NULL"

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

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

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.