Handle NULL values for integer field in a function

Question

I have created a function to insert/update record in a table. Data is passed to function as arguments. Facing problem in handling Integer/float/double fields. For example, i have declared Empno field as Int(as per target table structure). So i thought when a null value comes i convert into '0' using cast function. But its not even accepting NULL values since its declared as Int. should i change the declaration to varchar and then convert ? What should i do ? Please help

Error i'm getting is operator does not exist: text integer 42883

Sample CODE:

create function test (text[])  
declare  
companycode varchar(4);  
empno varchar(10);  
begin  
L_1 := $1[1];  
L_2 :=cast(NULLIF($1[2],0) as int);  
update and insert statement ...  
end;

Hi, could you please post the code for the function you've written? — Rob Streeting

Usagi Miyamoto Usagi Miyamoto · Accepted Answer · 2019-09-30T16:03:05

NULLIF() changes the specified value to NULL, not vica-versa. Use IFNULL() or COALESCE() instead.

Also Change the order of the operations to CAST first, something like this:

COALESCE($1[2]::int, 0)

Handle NULL values for integer field in a function

1 Answers