gistfile1.sql

CREATE OR REPLACE FUNCTION notifyUpdatediItem() RETURNS TRIGGER AS $$
  DECLARE
  rec RECORD;
  BEGIN
  FOR rec IN (SELECT getSharedItemNotifees(NEW.item_id)) LOOP
      PERFORM pg_notify('SharedItemUpdated', CAST(rec.to_account_id AS text)|| ' ' || CAST(NEW.name_hmac AS text));
    END LOOP;
  END;
$$ LANGUAGE PLPGSQL;

-- From the item_id we get a table of notifees where the shared item has been updated
CREATE OR REPLACE FUNCTION getSharedItemNotifees(integer) RETURNS TABLE(item_session_key_share_id bigint, account_id bigint, to_account_id bigint, item_id bigint) AS $$
SELECT s.item_session_key_share_id, s.account_id, s.to_account_id, k.item_id  
  FROM item_session_key_share s 
  JOIN item_session_key k ON (s.item_session_key_id = k.item_session_key_id)
  WHERE k.item_id = $1 AND k.supercede_time IS NULL;
$$ LANGUAGE SQL;

CREATE TRIGGER UpdatedItemNotify AFTER UPDATE ON item FOR EACH ROW EXECUTE PROCEDURE notifyupdateditem();