invalid input syntax for integer: "NULL"

From: "Yonatan Ben-Nes" <yonatan(at)epoch(dot)co(dot)il>
To: pgsql-general(at)postgresql(dot)org
Subject: invalid input syntax for integer: "NULL"
Date: 2007-02-20 21:45:55
Message-ID: 2d0127b80702201345x59301e0fyad04ab73aed96854@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Rhys Stewart 2007-02-20 22:15:38 Re: Installing support for python on windows
Previous Message gustavo halperin 2007-02-20 21:25:39 postgresql vs mysql