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
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 |