gistfile1.txt

begin;
create schema test;
set search_path=test;

create table products (
  id serial primary key,
  quantity_in_stock integer
);

create table inventory_movements (
  product_id integer references products on delete cascade,
  quantity integer not null
);

-- Problem: need to update products(quantity_in_stock) when
-- an inventory movement is created or deleted.
-- Updates on inventory_movements aren't allowed.


-- Approach 1: Have one trigger function.
-- Annoyance: Have to declare an integer, if statements.
create function update_quantity_1() returns trigger as $$
declare
  the_product_id integer;
begin
  if TG_OP = 'DELETE' then
    the_product_id := OLD.id;
  else
    the_product_id := NEW.id;
  end if;

  update products set quantity_in_stock = (
    select sum(inventory_movements)
    from inventory_movements
    where inventory_movements.product_id = the_product_id
  ) where products.id = the_product_id;

  return NULL;
end $$ language plpgsql;

create trigger a_update_quantity_1
after insert or update or delete on inventory_movements
for each row execute procedure update_quantity_1();




-- Approach 2: Get rid of if statements.
-- Annoyance: have to define three functions and two triggers.
create function update_quantity_2(product_id integer) returns void as $$
  update products set quantity_in_stock = (
    select sum(inventory_movements.quantity)
    from inventory_movements
    where inventory_movements.product_id = $1
  ) where products.id = $1;
$$ language sql;

create function update_quantity_2_insert() returns trigger as $$ begin
  perform update_quantity_2(NEW.product_id);
  return NULL;
end $$ language plpgsql;

create function update_quantity_2_delete() returns trigger as $$ begin
  perform update_quantity_2(OLD.product_id);
  return NULL;
end $$ language plpgsql;

create trigger a_update_quantity_2_1
after insert on inventory_movements
for each row execute procedure update_quantity_2_insert();

create trigger a_update_quantity_2_2
after delete on inventory_movements
for each row execute procedure update_quantity_2_delete();


-- Approach 3: Use an if statement and an extra function
-- Annoyance: Still an if statement and an extra function
-- I think this is my favorite one.
create function update_quantity_3(product_id integer) returns void as $$
  update products set quantity_in_stock = (
    select sum(inventory_movements.quantity)
    from inventory_movements
    where inventory_movements.product_id = $1
  ) where products.id = $1;
$$ language sql;

create function update_quantity_3() returns trigger as $$ begin
if TG_OP = 'DELETE' then
  perform update_quantity_2(OLD.product_id);
else
  perform update_quantity_2(NEW.product_id);
end if;
return NULL;
end $$ language plpgsql;


create trigger a_update_quantity_3
after insert or delete on inventory_movements
for each row execute procedure udpate_quantity_3();




-- What I'd *really* like to do:
create function update_quantity_4() returns trigger as $$ begin
  update products set quantity_in_stock = (
    select sum(inventory_movements.quantity)
    from inventory_movements
    where inventory_movements.product_id = CHANGED.id
  ) where products.id = CHANGED.id;
  return NULL;
end $$ language plpgsql;

create trigger a_update_quantity_4a
after insert or delete on inventory_movements
for each row execute procedure update_quantity_4();


-- Or, even better:
create trigger a_update_quantity_4b
after insert or delete on inventory_movements
for each row do $$ begin
    update products set quantity_in_stock = (
      select sum(inventory_movements.quantity)
      from inventory_movements
      where inventory_movements.product_id = CHANGED.id
    ) where products.id = CHANGED.id;
    return NULL;
  end
$$ language plpgsql;