이곳 게시판 게시물 등록, 삭제, 수정 시 작동하는 트리거입니다.

-- 게시물 트리거
CREATE OR REPLACE FUNCTION t_fti() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
        fullstr text;
        i integer;
        ftitable text;
        upmenuid text;
        v_criteria text;
        v_subcritid integer;
        oldlastid integer;
        oldexist integer;
BEGIN
ftitable := TG_ARGV[0];
upmenuid := split_part(ftitable,'_',2);
-- 게시물이 작성될 때 할일
-- 해당 사용자 posts 갯수 증가
-- 해당 메뉴 total 증가
-- 해당 메뉴 maxpage 증가
-- 해당 게시물의 pid crows 증가
-- full text index 삽입
IF TG_OP = 'INSERT' THEN
        SELECT criteria INTO v_criteria FROM menus WHERE menuid = upmenuid::integer;
        SELECT menuid INTO v_subcritid FROM menus WHERE criteria = v_criteria AND subcrit = NEW.topic;

        IF NEW.usernum > 0 THEN
                UPDATE users SET posts = posts + 1, posttime = NEW.cdate WHERE num = NEW.usernum;
                fullstr := NEW.name || ' ' || NEW.uid || ' ' || NEW.subject || ' ' || NEW.content;
        ELSE
                fullstr := NEW.name || ' ' || NEW.subject || ' ' || NEW.content;
        END IF;

        -- 갯수 및 lastid 갱신
        IF NEW.pid = 0 THEN
                UPDATE menus SET total = total + 1, lastid = NEW.id, maxpage = maxpage + 1 WHERE menuid = upmenuid::integer;
        ELSE
                execute('UPDATE bd_' || upmenuid || ' SET crows = crows + 1 WHERE id = ' || NEW.pid);
                UPDATE menus SET total = total + 1, lastid = NEW.id WHERE menuid =  upmenuid::integer;
        END IF;

        IF upmenuid <> v_subcritid::text THEN
                IF NEW.pid = 0 THEN
                        UPDATE menus SET total = total + 1, lastid = NEW.id, maxpage = maxpage + 1 WHERE menuid = v_subcritid;
                ELSE
                        UPDATE menus SET total = total + 1, lastid = NEW.id WHERE menuid = v_subcritid;
                END IF;
        END IF;

        i := fti_insert(ftitable, NEW.id, null, fullstr);
        RETURN NEW;
ELSIF TG_OP = 'DELETE' THEN
        SELECT criteria INTO v_criteria FROM menus WHERE menuid = upmenuid::integer;
        SELECT menuid INTO v_subcritid FROM menus WHERE criteria = v_criteria AND subcrit = OLD.topic;

        IF OLD.usernum > 0 THEN
                UPDATE users SET posts = posts - 1, posttime = abstime(now())::int WHERE num = OLD.usernum;
        END IF;

        IF OLD.pid = 0 THEN
                -- lastid 를 구해서 그놈이 마지막 id 였다면, 새 마지막 id를 지정한다.
                SELECT lastid INTO oldlastid FROM menus WHERE menuid = v_subcritid;
                IF oldlastid >= OLD.id THEN
                        execute('SELECT id FROM bd_' || upmenuid || ' WHERE id <= ' || oldlastid || ' AND topic = ''' || OLD.topic  || ''' AND pid = 0 ORDER BY id DESC LIMIT 1') into oldexist;
                        IF oldexist > 0 THEN
                                UPDATE menus SET total = total - 1, maxpage = maxpage - 1, lastid = oldexist WHERE menuid = v_subcritid;
                        ELSE
                                UPDATE menus SET total = total - 1, maxpage = maxpage - 1, lastid = null WHERE menuid = v_subcritid;
                        END IF;
                ELSE
                        UPDATE menus SET total = total - 1, maxpage = maxpage - 1 WHERE menuid = v_subcritid;
                END IF;
        ELSE
                -- pid 의 crow -1
                execute 'UPDATE bd_' || upmenuid || ' SET crows = crows - 1 WHERE id = ' || OLD.pid;
                UPDATE menus SET total = total - 1 WHERE menuid = v_subcritid;
        END IF;

        IF upmenuid <> v_subcritid::text THEN
                -- upmenu 도 update
                IF OLD.pid = 0 THEN
                        UPDATE menus SET total = total - 1, maxpage = maxpage - 1 WHERE menuid = upmenuid::integer;
                ELSE
                        UPDATE menus SET total = total - 1 WHERE menuid = upmenuid::integer;
                END IF;
        END IF;

        i := fti_delete(ftitable, OLD.id, null);
        RETURN OLD;
ELSIF TG_OP = 'UPDATE' THEN
        IF OLD.id <> NEW.id THEN
                RAISE EXCEPTION 'Do not allow to change board number(Primary key)';
                RETURN NULL;
        END IF;
        IF (OLD.uid || ' ' || OLD.name || ' ' || OLD.subject || ' ' || OLD.content) <> (NEW.uid || ' ' || NEW.name || ' ' || NEW.subject || ' ' || NEW.content) THEN
                i := fti_delete(ftitable, OLD.id, null);
                IF NEW.usernum > 0 THEN
                        fullstr := NEW.name || ' ' || NEW.uid || ' ' || NEW.subject || ' ' || NEW.content;
                ELSE
                        fullstr := NEW.name || ' ' || NEW.subject || ' ' || NEW.content;
                END IF;
                i := fti_insert(ftitable, NEW.id, null, fullstr);
        END IF;
        RETURN NEW;
ELSE
        RETURN NULL;
END IF;
END;
$$;


-- 댓글 트리거
CREATE OR REPLACE FUNCTION c_fti() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
  fullstr text;
  i integer;
BEGIN
IF TG_OP = 'INSERT' THEN
        IF NEW.usernum > 0 THEN
                UPDATE users SET posts = posts + 1, posttime = NEW.cdate WHERE num = NEW.usernum;
                fullstr := NEW.name || ' ' || NEW.uid || ' ' || NEW.content;
        ELSE
                fullstr := NEW.name || ' ' || NEW.content;
        END IF;
        execute('UPDATE ' || NEW.ptable || ' SET comments = comments + 1, commdate = ' || NEW.cdate || ' WHERE id = ' || NEW.pid);
        i := fti_insert(NEW.ptable || '_fti', NEW.pid, NEW.cid, fullstr);
        RETURN NEW;
ELSIF TG_OP = 'DELETE' THEN
        IF OLD.usernum > 0 THEN
                update users set posts = posts - 1, posttime = abstime(now())::int where num = OLD.usernum;
        END IF;
        execute('update ' || OLD.ptable || ' set comments = comments - 1, commdate = abstime(now())::int where id = ' || OLD.pid);
        i := fti_delete(OLD.ptable || '_fti', OLD.pid, OLD.cid);
        return OLD;
ELSIF TG_OP = 'UPDATE' THEN
        IF (OLD.uid || ' ' || OLD.name || ' ' || OLD.content) <> (NEW.uid || ' ' || NEW.name || ' ' || NEW.content) THEN
                i := fti_delete(OLD.ptable || '_fti', OLD.pid, OLD.cid);
                IF NEW.usernum > 0 THEN
                        fullstr := NEW.name || ' ' || NEW.uid || ' ' || NEW.content;
                ELSE
                        fullstr := NEW.name || ' ' || NEW.content;
                END IF;
                i := fti_insert(NEW.ptable || '_fti', NEW.pid, NEW.cid, fullstr);
        END IF;
        RETURN NEW;
ELSE
        RETURN NULL;
END IF;
END;
$$;