CODEWITHSUNDEEP
sqlpostgresqltriggersplpgsql
evilgeniuz
evilgeniuz

Reputation: 376

Triggers in postgres

I wrote a simple trigger in PL/pgSQL for PostgreSQL but it doesn't work as I've planned. I have two questions:

  1. Is there a debugger for PostgreSQL functions?
  2. Where am I wrong? I need to check idprod here.
CREATE OR REPLACE FUNCTION add_towh() RETURNS TRIGGER AS $$
DECLARE
    idsupp integer;
    idprod integer;
    whamount integer;
BEGIN

    SELECT PRODUCT INTO idsupp from SUPPLIERS where ID = NEW.SUPPLIER;
    SELECT ID INTO idprod from PRODUCTS where ID = idsupp;

    if (idprod > 0) then
        select amount into whamount from warehouses where product = idprod;
        update warehouses set amount = whamount * new.amount;
    else
        insert into warehouses (product,amount) values(idprod, new.amount);     
    end if;
    RETURN NEW; 
END;

$$ LANGUAGE plpgsql;

CREATE TRIGGER addtowh
AFTER INSERT ON orders FOR EACH ROW EXECUTE PROCEDURE add_towh ();

Upvotes: 2

Views: 437

Answers (2)

Walter
Walter

Reputation: 1

For debugging, you can also do:

RAISE DEBUG 'some debug message and variable value: %', some_variable;

You can then set your log level to debug to output the debug. You can keep these debug statement's in so that if you're doing some investigation in the future, these debug statements could potentially still be useful.

Upvotes: 0

mu is too short
mu is too short

Reputation: 434665

Your if (idprod > 0) would probably better written as if idprod is not null but idprod > 0 should work.

I'm guessing that the error is right here:

update warehouses set amount = whamount * new.amount;

You don't have a WHERE clause on that UPDATE so you'll up changing every row in the table. You could use this instead:

select amount into whamount from warehouses where product = idprod;
update warehouses set amount = whamount * new.amount where product = idprod;

or better, do it with a single UPDATE:

update warehouses set amount = amount * new.amount where product = idprod;

If you go with the latter then you wouldn't need whamount in your DECLARE section anymore.

Also, I think you could replace your first two SELECTs with a single SELECT. These two:

SELECT PRODUCT INTO idsupp from SUPPLIERS where ID = NEW.SUPPLIER;
SELECT ID INTO idprod from PRODUCTS where ID = idsupp;

could be replaced with just this:

select p.id into idprod
from products p
join suppliers s on p.id = s.product
where s.id = new.supplier;

and then you wouldn't need idsupp either.

As far as debugging goes, the only thing I know of is printf-style debugging with raise notice; for example:

raise notice 'idprod is "%"', idprod;

Upvotes: 2

Related Questions