[SQL] PostgreSQL 함수, 프로시저, Job 스케줄링

감자·2026년 1월 28일
MicrosoftDataSchoolsql

microsoft_dataschool

목록 보기
15/28
post-thumbnail

01. 사용자 정의 함수 (User Defined Function)

개념

사용자가 직접 로직을 정의하여 SQL 문 내에서 호출할 수 있는 함수임. 복잡한 연산을 캡슐화하거나 재사용성을 높이는 데 사용됨.

  • SQL 함수: 단순한 SQL 쿼리의 집합.
  • PL/pgSQL 함수: 변수 선언, 반복문, 조건문 등 프로그래밍 언어의 기능을 포함한 절차적 언어.

1) 기본 함수 생성 (SQL vs PL/pgSQL)

SQL 버전 (단순 반환)

CREATE OR REPLACE FUNCTION fms.one()
    RETURNS int4
    LANGUAGE sql
AS $$
    select 1;
$$;

-- 실행
select fms.one();

SQL 버전 (매개변수 연산)

CREATE OR REPLACE FUNCTION fms.add_num(x integer, y integer)
    RETURNS integer
    LANGUAGE sql
AS $$
    select x + y;
$$;

-- 실행
select fms.add_num(3, 4);

PL/pgSQL 버전 (BEGIN-END 블록 사용)

CREATE OR REPLACE FUNCTION fms.add_num_plpgsql(x integer, y integer)
    RETURNS int4
AS $$
begin
    return x + y;
end;
$$ LANGUAGE plpgsql;

-- 실행
select fms.add_num_plpgsql(5, 6);

2) 테이블 조회 함수

SQL 버전

CREATE OR REPLACE FUNCTION fms.get_chickinfo(chick_no varchar)
    RETURNS integer
AS $$
    select egg_weight from fms.chick_info 
    where chick_info.chick_no = get_chickinfo.chick_no
$$ LANGUAGE sql;

-- 실행
select fms.get_chickinfo('A2510001');

PL/pgSQL 버전

CREATE OR REPLACE FUNCTION fms.get_chickinfo_plpgsql(chick_no varchar)
    RETURNS integer
AS $$
begin
    return (
        select egg_weight from fms.chick_info
        where chick_info.chick_no = get_chickinfo_plpgsql.chick_no
    );
end;
$$ LANGUAGE plpgsql;

-- 실행
select fms.get_chickinfo_plpgsql('A2510001');

02. 제어문 활용 (IF, LOOP)

1) 조건문 (IF / CASE)

SQL 버전 (CASE WHEN)

CREATE OR REPLACE FUNCTION fms.weight_pass(weight integer)
    RETURNS text
AS $$
    select case
        when weight >= 40 then '합격'
        else '불합격'
    end;
$$ LANGUAGE sql;

-- 실행
select fms.weight_pass(42);

PL/pgSQL 버전 (IF THEN)

create or replace function fms.weight_pass_plpg(weight integer)
returns text
as $$
declare
    result text;  -- 변수 선언
begin
    if weight >= 40 then result := '합격';
    else result := '불합격';
    end if;
    return result;
end;
$$ language plpgsql;

-- 실행
select fms.weight_pass_plpg(41);

2) 반복문 (LOOP)

create or replace function fms.print_loop(n integer)
returns void
as $$
declare
    i integer := 1;
begin
    while i <= n loop
        raise notice 'Loop: %', i; -- 메시지 출력
        i := i + 1;
    end loop;
end;
$$ language plpgsql;

-- 실행
select fms.print_loop(5);

03. 테이블 반환 함수 (Table Function)

단일 값이 아닌 테이블 형태(여러 행과 열)를 반환하는 함수임.

기본 예제 ($1 파라미터 활용)

-- 매개변수 이름을 지정하지 않고(IN integer) $1로 참조 가능
create or replace function fms.multivalue(in integer)
returns table (f1 int, f2 text)
as $$
begin
    return QUERY
    select $1, cast($1 as text) || ' is text';
end;
$$ language plpgsql;

-- 실행
select fms.multivalue(4);

실전 예제 (뷰 로직을 함수로 변환)
특정 농장 코드를 입력받아 해당 농장의 출하 요약 정보를 반환함.

create or replace function fms.func_farm_ship_summary(x varchar)
returns table (farm varchar, customer varchar, cnt bigint)
as $$
begin
    return QUERY
        select ch.farm::varchar,
               sh.customer::varchar,
               count(sh.chick_no)
        from fms.ship_result sh
        join fms.prod_result pr on sh.chick_no = pr.chick_no 
        join fms.chick_info ch on ch.chick_no = sh.chick_no
        where ch.farm = func_farm_ship_summary.x
        group by ch.farm, sh.customer;
end;
$$ language plpgsql;

-- 실행
select * from fms.func_farm_ship_summary('A');

[도전 미션] 닭 코드 해독 함수
코드 문자열을 분석하여 농장, 출생년도, 성별을 분리해 반환함.

create or replace function fms.decode_chick_info(code varchar)
returns table (farm text, years text, gender text)
as $$
    select left(code, 1),
           '20' || substring(code, 2, 2),
           case 
               when substring(code, 4, 1) = '0' then 'F'
               when substring(code, 4, 1) = '1' then 'M'
           end;
$$ language sql;

-- 실행
select * from fms.decode_chick_info('A2510001');

04. 스토어드 프로시저 (Stored Procedure)

개념

함수와 유사하지만 리턴값이 필수적이지 않으며, 트랜잭션 제어(COMMIT, ROLLBACK)가 가능하다는 점이 가장 큰 차이점임. 주로 INSERT, UPDATE 등의 데이터 조작 작업에 사용됨.

1) 기본 프로시저 (데이터 백업)

breeds_prod 뷰의 데이터를 실제 테이블 breeds_prod_tbl로 복사하는 작업을 수행함.

테이블 준비

CREATE TABLE fms.breeds_prod_tbl (
    prod_date date NOT NULL,
    breeds_nm character(20) NOT NULL,
    total_sum bigint NOT NULL,
    save_time timestamp without time zone NOT NULL
);
COMMENT ON TABLE fms.breeds_prod_tbl IS '품종별 생산실적';

-- 데이터 수동 입력 테스트
insert into fms.breeds_prod_tbl(prod_date, breeds_nm, total_sum, save_time)
(
    select prod_date, breeds_nm, total_sum, current_timestamp as save_time
    from fms.breeds_prod 
    where prod_date = '2025-01-31'
);

프로시저 생성 및 호출

create or replace procedure fms.proc_breeds_prod()
as $$
    insert into fms.breeds_prod_tbl
    (prod_date, breeds_nm, total_sum, save_time)
    (
        select prod_date, breeds_nm, total_sum, current_timestamp as save_time
        from fms.breeds_prod 
        where prod_date = '2025-01-31'
    );
$$ language sql;

-- 프로시저 호출 (CALL)
call fms.proc_breeds_prod();

2) [도전 미션] 변경 이력 관리 프로시저

데이터 변경 시 기존 값을 별도 로그 테이블에 저장하고, 변경 사항을 반영하는 로직을 구현함.

로그 테이블 생성

CREATE TABLE IF NOT EXISTS fms.prod_log (
    log_id SERIAL PRIMARY KEY,
    chick_no VARCHAR(20) NOT NULL,
    prod_date DATE NOT NULL,
    old_weight NUMERIC,
    new_weight NUMERIC,
    memo TEXT,
    logged_at TIMESTAMP
);

프로시저 구현 (데이터 유무 확인 및 분기 처리)

CREATE OR REPLACE PROCEDURE fms.update_and_log_prod_weight(
    p_chick_no VARCHAR,      -- 닭 번호
    p_target_date DATE,      -- 생산 일자
    p_new_weight NUMERIC     -- 변경할 새 무게
)
LANGUAGE plpgsql 
AS $$
DECLARE
    v_old_weight NUMERIC;    -- 기존 무게를 담을 변수
BEGIN
    -- 1. 기존 무게 조회
    SELECT raw_weight INTO v_old_weight
    FROM fms.prod_result
    WHERE chick_no = p_chick_no 
      AND prod_date = p_target_date;

    -- 2. 데이터 존재 여부 확인 (IF FOUND)
    IF FOUND THEN
        -- 정상 케이스: 로그 기록 후 업데이트
        INSERT INTO fms.prod_log (
            chick_no, prod_date, old_weight, new_weight, memo, logged_at
        ) VALUES (
            p_chick_no, p_target_date, v_old_weight, p_new_weight, '정상 업데이트', current_timestamp
        );
        
        UPDATE fms.prod_result
        SET raw_weight = p_new_weight
        WHERE chick_no = p_chick_no AND prod_date = p_target_date;
    ELSE
        -- 예외 케이스: 데이터가 없는 경우 오류 로그만 기록
        INSERT INTO fms.prod_log (
            chick_no, prod_date, old_weight, new_weight, memo, logged_at
        ) VALUES (
            p_chick_no, p_target_date, NULL, NULL, '대상 없음: ' || p_chick_no, current_timestamp
        );
    END IF;
END;
$$;

-- 실행 테스트
call fms.update_and_log_prod_weight('C2310014','2025-01-25', 1500); -- 성공 케이스
call fms.update_and_log_prod_weight('B2510016','2025-02-02', 1500); -- 실패 케이스

05. Job 스케줄링 (pgAgent)

개념

데이터베이스 내에서 특정 작업(프로시저 호출, SQL 실행 등)을 주기적으로 자동 실행하기 위해 사용하는 스케줄러 도구임. PostgreSQL에서는 주로 pgAgent를 사용함.

pgAdmin에서 생성된 스크립트 분석

아래 코드는 GUI 도구인 pgAdmin에서 Job을 설정했을 때 내부적으로 생성되는 SQL 스크립트임.

1. Job 생성
작업의 기본 정보(이름, 설명, 활성화 여부 등)를 정의함.

DO $$
DECLARE
    jid integer;
    scid integer;
BEGIN
    -- pgagent.pga_job 테이블에 새 작업 추가
    INSERT INTO pgagent.pga_job(
        jobjclid, jobname, jobdesc, jobhostagent, jobenabled
    ) VALUES (
        1::integer, 
        'proc_breeds_prod_job'::text, 
        '품종별 생산실적 입력 프로시저 스케줄링'::text, 
        ''::text, 
        true
    ) RETURNING jobid INTO jid;  -- 생성된 Job ID를 변수 jid에 저장

2. Step 생성
Job이 실행할 실제 동작(SQL 코드)을 정의함.

    -- pgagent.pga_jobstep 테이블에 단계 추가
    INSERT INTO pgagent.pga_jobstep (
        jstjobid, jstname, jstenabled, jstkind,
        jstconnstr, jstdbname, jstonerror,
        jstcode, jstdesc
    ) VALUES (
        jid,                      -- 위에서 받은 Job ID 연결
        'step'::text, 
        true, 
        's'::character(1),        -- 's'는 SQL 스텝을 의미
        ''::text, 
        'postgres'::name,         -- 실행할 DB명
        'f'::character(1),        -- 에러 발생 시 처리(fail)
        'CALL fms.proc_breeds_prod();'::text,  -- 실행할 프로시저 호출
        ''::text
    );

3. Schedule 생성
Job이 언제 실행될지 시간을 정의함.

    -- pgagent.pga_schedule 테이블에 스케줄 추가
    INSERT INTO pgagent.pga_schedule(
        jscjobid, jscname, jscdesc, jscenabled,
        jscstart, jscend, jscminutes, jschours, jscweekdays, jscmonthdays, jscmonths
    ) VALUES (
        jid, 
        'everyminute'::text, 
        ''::text, 
        true,
        '2026-01-28 14:40:00+09'::timestamp with time zone, 
        '2026-01-28 14:45:00+09'::timestamp with time zone,
        -- 분 단위 설정 (모든 분 True)
        '{t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t}'::bool[]::boolean[],
        -- 시간 단위 설정 (모든 시간 False -> 특정 시간 미지정)
        '{f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f}'::bool[]::boolean[],
        -- 요일, 월일, 월 설정 (모두 False)
        '{f,f,f,f,f,f,f}'::bool[]::boolean[],
        '{f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f}'::bool[]::boolean[],
        '{f,f,f,f,f,f,f,f,f,f,f,f}'::bool[]::boolean[]
    ) RETURNING jscid INTO scid;
END
$$;
profile
감자
제 별명이 감자입니다
이전 포스트

[SQL] 심화 문법 완전 정복: Subquery, View, Pivot, Function

다음 포스트

[SQL] DB 오브젝트와 쿼리 최적화: Trigger, Index, Window Function

0개의 댓글