null value in postgres functions

Question

I am new in postgres and i found this issue with this simple function

CREATE OR REPLACE FUNCTION "public"."TEST"("disponibile" int2=0, "extra" int2=0, "ordini" int2=0, "prenotata" int2=0)
  RETURNS "pg_catalog"."int2" AS $BODY$
DECLARE qta SMALLINT;   
BEGIN
    -- Routine body goes here...
    qta := disponibile + extra - ordini - prenotata;
    IF qta < 0 THEN
      qta := 0;     
    END IF;     
    RETURN qta;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

it fail when input parameter is null, but i was expecting that default input value = 0 can avoid that.

is there any other way than use coalesce() fefore passing parm?

thx in advance

Answer 1

CREATE OR REPLACE FUNCTION "public"."TEST"("disponibile" int2, 
                                           "extra" int2, 
                                           "ordini" int2, 
                                           "prenotata" int2)
  RETURNS "pg_catalog"."int2" AS $BODY$
DECLARE qta SMALLINT;   
BEGIN
    -- Routine body goes here...
    qta := COALESCE(disponibile, 0)
         + COALESCE(extra, 0)
         - COALESCE(ordini, 0)
         - COALESCE(prenotata, 0);
    IF qta < 0 THEN
      qta := 0;     
    END IF;     
    RETURN qta;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

or simply

CREATE OR REPLACE FUNCTION "public"."TEST"("disponibile" int2, 
                                           "extra" int2, 
                                           "ordini" int2, 
                                           "prenotata" int2)
  RETURNS "pg_catalog"."int2" AS $BODY$
BEGIN
    RETURN GREATEST(   COALESCE(disponibile, 0)
                     + COALESCE(extra, 0)
                     - COALESCE(ordini, 0)
                     - COALESCE(prenotata, 0) , 0);
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100