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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.