trigger and plpgsq help needed

["SUNDAY A. OLUTAYO" <olutayo@xxxxxxxxxx>]

Dear all,

I have issue with the red portion function below;

This IF last_id IS NULL THEN suppose to test if last_id is NULL, that is the select query did not found it
then execute the rest of the red sql but it always fail to insert the NEW.amount into amount, every other things fine.

Kindly help out.



CREATE OR REPLACE FUNCTION invoice_trigger_func() RETURNS trigger AS
$$

DECLARE
last_id integer;
current_balance_id integer;
oldbalance numeric(10,2);
newbalance numeric(10,2);

BEGIN

    SELECT lastbal_id INTO last_id FROM patient
        WHERE patient_id = NEW.patient_id;
        IF last_id IS NULL THEN
           INSERT INTO balance (invoice_id, patient_id, amount)
            VALUES (NEW.invoice_id, NEW.patient_id, NEW.amount);
           UPDATE patient SET lastbal_id = (SELECT currval('balance_balance_id_seq'))
            WHERE patient_id = NEW.patient_id;
            ELSE
           SELECT amount INTO oldbalance FROM  balance
            WHERE balance_id = last_id;
           INSERT INTO balance (invoice_id, patient_id, amount)
            VALUES (NEW.invoice_id, NEW.patient_id, oldbalance + NEW.amount);
           UPDATE patient SET lastbal_id = (SELECT currval('balance_balance_id_seq'))
            WHERE patient_id = NEW.patient_id;
            END IF;
    RETURN NEW;
    
END;
$$ LANGUAGE plpgsql;

Thanks,

Sunday Olutayo