2

I am looking for an elegant solution to this situation:

  • I have created a trigger function that updates the table supply with the sum of some detail rows, whenever a row is inserted or updated on warehouse_supplies.
  • PostgreSQL insert or update syntax allowed me to share the same function sync_supply_stock() for the insert and update conditions.
  • However, when I try to wire the after delete condition to the function it cannot be reused (although it is logically valid), for the returning object must be old instead of new.
-- The function I want to use for the 3 conditions (insert, update, delete)
create or replace function sync_supply_stock ()
returns trigger
as $$
begin
    -- update the supply whose stock just changed in warehouse_supply with
    -- the sum its stocks on all the warehouses.
    update supply 
    set stock = (select sum(stock) from warehouse_supplies where supply_id = new.supply_id)
    where supply_id = new.supply_id;
    
    return new;
end;
$$ language plpgsql;

-- The (probably) unnecessary copy of the previous function, this time returning old.
create or replace function sync_supply_stock2 ()
returns trigger
as $$
begin
    -- update the supply whose stock just changed in warehouse_supply with
    -- the sum its stocks on all the warehouses.
    update supply 
    set stock = (select sum(stock) from warehouse_supplies where supply_id = old.supply_id)
    where supply_id = old.supply_id;
    
    return old;
end;
$$ language plpgsql;

-- The after insert/update trigger
create trigger on_warehouse_supplies__after_upsert after insert or update
on warehouse_supplies for each row 
execute procedure sync_supply_stock ();

-- The after delete trigger
create trigger on_warehouse_supplies__after_delete after delete
on warehouse_supplies for each row 
execute procedure sync_supply_stock2 ();

Am I missing something or is there any fixing to duplicating sync_supply_stock2() as sync_supply_stock2()?


EDIT

For the benefit of future readers, following @bergi answer and discusion, this is a possible factorized solution

create or replace function sync_supply_stock ()
returns trigger
as $$
declare
    _supply_id int;
begin
    -- read the supply_id column from `new` on insert/update conditions and from `old` on delete conditions
    _supply_id = coalesce(new.supply_id, old.supply_id);
    
    -- update the supply whose stock just changed in of_warehouse_supply with
    -- the sum its stocks on all the warehouses.
    update of_supply 
    set stock = (select sum(stock) from of_warehouse_supplies where supply_id = _supply_id)
    where supply_id = _supply_id;
            
    -- returns `new` on insert/update conditions and `old` on delete conditions 
    return coalesce(new, old);
end;
$$ language plpgsql;

create trigger on_warehouse_supplies__after_upsert after insert or update
on of_warehouse_supplies for each row 
execute procedure sync_supply_stock ();

create trigger on_warehouse_supplies__after_delete after delete
on of_warehouse_supplies for each row 
execute procedure sync_supply_stock ();
1
  • 2
    You can use TG_OP to distinguish whether your trigger function was called on insert, update or delete. Commented Aug 8, 2020 at 14:28

1 Answer 1

3

for the returning object must be old instead of new.

No. The return value is only relevant for BEFORE ROW or INSTEAD OF triggers. From the docs: "The return value of a row-level trigger fired AFTER or a statement-level trigger fired BEFORE or AFTER is always ignored; it might as well be null".

So you can just make your sync_supply_stock trigger function RETURN NULL and it can be used on all operations.

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

2 Comments

Hmmm. There is still a problem with new/old. As I use these objects to get new.supply_id, the delete condition does not work as the new object is null. I will need to switch/case with tg_op.
@coterobarros Ah, I didn't look at that, I thought they were identical. You can use WHERE supply_id = COALESCE(NEW.supply_id, OLD.supply_id) for that

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.