-1

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

1
  • 1
    NULL is a value. For DEFAULT applying the argument must be skipped at all. is there any other way than use coalesce() fefore passing parm? Use variable:=COALESCE(variable,0); in front of function body. Or (in your particular case) immediately in qta calculation.
    – Akina
    Oct 2 '19 at 9:33
0
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

This site is temporarily in read only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .