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
– Rob Streeting, Commented Sep 30, 2019 at 15:53

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

1

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)

answered Sep 30, 2019 at 16:03

Usagi Miyamoto

6,3471 gold badge21 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Parsu Over a year ago

Hi Usagi, Thank you for your support. I tried your suggestion for another float field(replaced int with float(8)) but i'm getting another error when i try to insert: column is of type double precision but expression is of type character varying Any suggestions ?

Parsu Over a year ago

Hey Usagi, I managed to resolve the issue. Thank you for your timely response !

Collectives™ on Stack Overflow

Handle NULL values for integer field in a function

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related