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
