Triggers in postgres

Question

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

Is there a debugger for PostgreSQL functions?
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 ();

mu is too short · Accepted Answer · 2011-12-19 06:13:11Z

2

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;

edited Dec 19, 2011 at 6:13

answered Dec 19, 2011 at 5:59

mu is too short

436k71 gold badges863 silver badges822 bronze badges

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

1 Comment

evilgeniuz Over a year ago

Thank you for answering. I have made mistake in another place and have already found it. I will look up with your debugging advice.

Walter · Accepted Answer · 2013-04-17 19:11:12Z

0

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.

answered Apr 17, 2013 at 19:11

Walter

1

Collectives™ on Stack Overflow

Triggers in postgres

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related