CODEWITHSUNDEEP
functionpostgresqltriggersplpgsql
Ehpansei
Ehpansei

Reputation: 47

PL/pgSQL trigger function not working properly

I'm having trouble with this function in my program.

The purpose of the function is to implement a trigger that checks if a user (utente) has enough money on his account (saldo) in order to add himself to a ride (boleia).

What I can gather from the behavior of my function, both the user (Utente) and the ride (Boleia) are found, but their attributes, specifically his balance (saldo) aren't being accessed correctly. This is because every time I try to add a user to a ride, the exception 'O Utente não tem saldo suficiente', which means that the user doesn't have enough money is thrown.

I can't think of anything I might be doing wrong. Both the Utente and Boleia tables are being correctly inserted, with every attribute also being inserted, but I can't seem to be able to access them in my function.

Here is the code to the function:

create or replace function assocpass_trigger_proc() returns trigger
as $$
declare
    x record;
    y record;
    balancetemp numeric;
begin
    select into x * from Utente where(nick=new.nick_passenger);
    select into y * from Boleia
    where(nick=new.nick_planner and date_time=new.date_time);
    select x.balance into balancetemp;
    if(found and (balancetemp>=y.cost_passenger)) then
       update Utente set balance = balancetemp-y.cost_passenger
       where Utente.nick = new.nick_passenger;
       insert into InscricaoP(nick_passenger,nick_planner,data_hora) 
       values(new.nick_passenger, new.nick_planner, new.date_time);
    elseif(found and (x.saldo<y.custo_passageiro)) then 
       raise exception 'O Utente não tem saldo suficiente';
    else
       raise exception 'A Boleia não existe'; 
    end if;
end
$$ language plpgsql;

And here is the insertion of the relation:

insert into InscricaoP(nick_planner,date_time,nick_passenger)
values('zero','15/06','cinco');

Both nick_planner and nick_passenger come from the "user" (Utilizador) entity Date_time and cost_passenger come from the "ride" (Boleia) entity.

Upvotes: 2

Views: 441

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656301

The special variable FOUND is set after each of your SELECT INTO commands. If you only check after the last one, you fail to test properly.

I think your function could work like this:

CREATE OR REPLACE FUNCTION assocpass_trigger_proc()
  RETURNS trigger AS
$func$
BEGIN

UPDATE utente u
SET    balance = balance - b.cost_passenger
FROM   boleia b
WHERE  u.nick = NEW.nick_passenger
AND    b.nick = NEW.nick_planner
AND    b.date_time = NEW.date_time
AND    u.balance >= b.cost_passenger;

IF FOUND THEN
    INSERT INTO inscricaop(nick_passenger, nick_planner, data_hora) 
    VALUES (NEW.nick_passenger, NEW.nick_planner, NEW.date_time);

ELSIF EXISTS (
    SELECT 1
    FROM   utente u, boleia b
    WHERE  u.nick = NEW.nick_passenger
    AND    b.nick = NEW.nick_planner
    AND    b.date_time = NEW.date_time
    AND    u.saldo < b.custo_passageiro) THEN

    RAISE EXCEPTION 'O Utente não tem saldo suficiente';
ELSE
    RAISE EXCEPTION 'A Boleia não existe'; 
END IF;

END
$func$ LANGUAGE plpgsql;

Upvotes: 3

David Aldridge
David Aldridge

Reputation: 52336

I would approach this by adding a check constraint to the utente table such that the balance has to be greater then or equal to zero, and try to debit the account by the cost of the ride. If there are insufficient funds a constraint violation error will be raised.

Upvotes: 0

Related Questions