Forum OpenACS Q&A: Response to Slow SubSelect in BBoard Forum

Collapse
Posted by MaineBob OConnor on
Ok, I've wandered around the pg docs and have finally groked it and I have something that works..... Posted here to help anyone else get the nack for messing with triggers and functions. Here it is:
DROP FUNCTION latest_thread();
CREATE FUNCTION latest_thread() RETURNS OPAQUE
AS 'DECLARE
   rec      record;

BEGIN
  SELECT into rec * from bboard_latest
    WHERE root_msg_id = new.root_msg_id;
  IF NOT FOUND -- is root not in table?
  THEN INSERT INTO bboard_latest (root_msg_id,msg_id)
    VALUES (new.root_msg_id, new.msg_id);
  ELSE UPDATE bboard_latest
    SET msg_id = new.msg_id
    WHERE root_msg_id = new.root_msg_id;
  END IF;
  RETURN NEW;
END;
' LANGUAGE 'plpgsql';

DROP TRIGGER latest_thread on bbtest;
CREATE TRIGGER latest_thread
AFTER INSERT ON bbtest
FOR EACH ROW
EXECUTE PROCEDURE latest_thread ();

-- now test it
select * from bboard_latest;
select * from bbtest;
insert into bbtest (root_msg_id,msg_id) values ('123456','123456');

-Bob