If condition in postgres function

Question

CREATE FUNCTION public.feerange (txamount decimal,amount decimal,latefeemax decimal)
    RETURNS INTEGER $feerange$
    DECLARE total INTEGER;
    DECLARE amountsum DECIMAL;
    BEGIN
    
    amountsum= amount + latefeemax;
    if txamount <= amountsum AND txamount >= amount
        THEN total=1
        else
           total=0
     RETURN total
    
    END; 
    $feerange $ LANGUAGE plpgsql;

while creating this function I'm getting an error of if condition

but I don't think there anything wrong.

the error is I'm getting is

/* ERROR: syntax error at or near "if" LINE 1: if txamount <= amountsum AND txamount >= amount

What is wrong with this? Help me to do this! Thank you !!

Laurenz Albe · Accepted Answer · 2021-05-26 13:48:20Z

3

You could greatly simplify that as

CREATE FUNCTION public.feerange (
   txamount decimal,
   amount decimal,
   latefeemax decimal
) RETURNS integer
   LANGUAGE sql AS
'SELECT CAST (txamount <= amount + latefeemax AND txamount >= amount AS integer)';

answered May 26, 2021 at 13:48

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Comments

Alex · Accepted Answer · 2021-05-26 12:28:30Z

2

You forgot end if; and ; in few places

CREATE FUNCTION public.feerange (txamount decimal,amount decimal,latefeemax decimal)
    RETURNS INTEGER
as
$$
    DECLARE total INTEGER;
    DECLARE amountsum DECIMAL;
BEGIN
    amountsum:= amount + latefeemax;
    if txamount <= amountsum AND txamount >= amount
        THEN total:=1;
        else
           total = 0;
    end if;
    RETURN total;
END;
$$ LANGUAGE plpgsql;

answered May 26, 2021 at 12:28

Alex

8669 silver badges20 bronze badges

Collectives™ on Stack Overflow

If condition in postgres function

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related