CODEWITHSUNDEEP
databasepostgresqlif-statement
Bhavin Varsur
Bhavin Varsur

Reputation: 327

If condition in postgres function

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

Upvotes: 1

Views: 78

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 246308

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)';

Upvotes: 3

Alex
Alex

Reputation: 866

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;

Upvotes: 2

Related Questions