if inside for Loop in postgresql

Question

I have two tables as below.

TABLE 1       TABLE 2
--------     --------
id            id
date          table1_id
total         subtotal
balance

table 1                     table 2
--------                    ---------
id  total  balance          id table1_id subtotal paid
1    20      10              1      1      5       5  
2    30      30              2      1      15      5
                             3      2      10      0
                             4      2      10      0
                             5      2      10      0

I have to add paid column in table2. so can anyone help me to add values to newly added column for existing data. I tried to wrote procedure as below but as postgres will not allow if in for loop so am unable to do it.

CREATE OR REPLACE FUNCTION public.add_amountreceived_inbillitem() RETURNS void AS
$BODY$
DECLARE 
rec RECORD;
inner_rec RECORD;
distributebalance numeric;
tempvar numeric;
BEGIN
FOR rec IN select * from table1 
LOOP 
distributebalance = rec.balance;
FOR inner_rec IN(select * from table2 where table1_id = rec.id order by id limit 1) 
 tempvar = distributebalance - inner_rec.subtotal;
 if (distributebalance >0 and tempvar>=0) THEN 
    update table2 set paid = inner_rec.subtotal where id = inner_rec.id ;
    distributebalance =distributebalance-inner_rec.subtotal;
 else if( distributebalance >0 and tempvar<0 )THEN 
    update table2 set paid = distributebalance where id = inner_rec.id;
END IF;
END LOOP; 
END LOOP;
END; $BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

Thanks In advance :)

404 · Accepted Answer · 2018-08-08 15:34:32Z

2

Postgres does allow for IF statements in loops.

The issue is that by writing ELSE IF you've started a new IF statement, so you now have 2 opening IFs but only one closing END IF. A "proper" elseif in plpgsql is ELSIF or ELSEIF. So just delete the space between those words and it should work.

answered Aug 8, 2018 at 15:34

404

8,7522 gold badges33 silver badges53 bronze badges

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

Collectives™ on Stack Overflow

if inside for Loop in postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related