형식

• https://www.postgresql.org/docs/current/sql-createprocedure.html

CREATE [ OR REPLACE ] PROCEDURE name
(
    [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ]
)
{ LANGUAGE lang_name
  | TRANSFORM { FOR TYPE type_name } [, ... ]
  | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
  | SET configuration_parameter { TO value | = value | FROM CURRENT }
  | AS 'definition'
  | AS 'obj_file', 'link_symbol'
  | sql_body
}

예제

기본

CREATE PROCEDURE insert_data
(
    a integer,
    b integer
)
AS $$
    INSERT INTO tbl VALUES (a);
    INSERT INTO tbl VALUES (b);
$$
LANGUAGE SQL;         -- 또는 'plpgsql'

DECLARE, CURSOR, FOR LOOP, RAISE NOTICE

CREATE OR REPLACE PROCEDURE TEST_CURSOR_LOOP()
AS $$
    DECLARE TARGET_CURSOR record;
BEGIN

    FOR TARGET_CURSOR IN
        SELECT
            id
        FROM
            student
        WHERE
            grade = 1
    LOOP
        RAISE NOTICE 'TARGET ID %', TARGET_CURSOR;
        UPDATE
           student_2020
        SET
            grade = 2
        WHERE
            id = TARGET_CURSOR.id;
    END LOOP;

END; $$
LANGUAGE 'plpgsql'

DECLARE, CURSOR, FOR LOOP, IF, ELSEIF, ELSE

DROP PROCEDURE IF EXISTS MERGE_ADM_SIGBJD;
CREATE PROCEDURE MERGE_ADM_SIGBJD ()
AS $$

    DECLARE done BOOLEAN DEFAULT FALSE; -- 행의 끝 여부
    DECLARE skipCurrent BOOLEAN DEFAULT FALSE;
    
    DECLARE code CHAR(10);
    DECLARE addr VARCHAR(100);
    DECLARE abol CHAR(4);
    
    DECLARE lv SMALLINT;
    DECLARE upCode CHAR(10);
    DECLARE addrNm CHAR(20);
    
    DECLARE cur CURSOR FOR
    SELECT SIG_BJD_CD, ADDR_NM, EXIST_YN FROM RAW_ADM_SIGBJD;
    
BEGIN
    OPEN cur;
    LOOP
        FETCH FROM cur
         INTO code, addr, abol;
        
        -- 모든 데이터를 읽은 경우 : 종료
        EXIT WHEN NOT FOUND;
        
        -- 주소 문자열 좌우 공백 제거
        addr := TRIM(addr);
        
        ---------------------------------------
        -- 시도
        IF SUBSTRING(code, 3, 3) = '000' THEN
            lv := 1;
            upCode := '0000000000';
            addrNm := addr;
        
        -- 시군구 (2어절로 이루어졌을 수도 있음)
        ELSEIF SUBSTRING(code, 6, 3) = '000' THEN
            lv := 2;
            upCode := RPAD(LEFT(code, 2), 10, '0');
            addrNm := PRUNE_LEFT_PHRASE(addr);
        
        -- 읍면동
        ELSEIF SUBSTRING(code, 9, 2) = '00' THEN
            lv := 3;
            upCode := RPAD(LEFT(code, 5), 10, '0');
            addrNm := RIGHT_PHRASE(addr);
        
        -- 리
        ELSE
            lv := 4;
            upCode := RPAD(LEFT(code, 8), 10, '0');
            addrNm := RIGHT_PHRASE(addr);
            
        END IF;
        
    END LOOP;
    
    COMMIT;
END; $$
LANGUAGE plpgsql;