database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
ㆍPostgreSQL
Firebird
Oracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
PostgreSQL Devel 8596 게시물 읽기
 News | Q&A | Columns | Tutorials | Devel | Files | Links
No. 8596
full text search를 호출하는 트리거
작성자
김상기(ioseph)
작성일
2010-01-06 14:30
조회수
7,971

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

 

-- 게시물 트리거
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;
$$;

[Top]
No.
제목
작성자
작성일
조회
9594한글 두벌식 자판 영타 변환 [1]
김상기
2015-09-22
3713
9488testsearch-ja 모듈을 이용한 우리말 full text search [1]
김상기
2014-11-25
6013
8596full text search를 호출하는 트리거
김상기
2010-01-06
7971
8595full text search 또 한 번 바꾸어 보았습니다.
김상기
2010-01-05
7818
61241차원 배열 요소 삭제 함수 - anyarray, anyelement 사용 예제 [4]
김상기
2005-06-01
11866
6047지정한 prepared query 가 있는지 조회하는 함수 [2]
김상기
2005-04-13
12534
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2019 DSN, All rights reserved.
작업시간: 0.067초, 이곳 서비스는
	PostgreSQL v11.5로 자료를 관리합니다