데이터베이스 오브젝트와 쿼리 최적화 기본(2)

함수

https://postgresql.kr/docs/13/plpgsql.html

CREATE [OR REPLACE] FUNCTION 함수이름(파라미터 목록)
RETURNS 반환타입 AS $$
DECLARE
-- 변수 선언
BEGIN
-- 함수 로직
RETURN 결과값;
END;
$$ LANGUAGE plpgsql;

• LANGUAGE에는 plpgsql, sql (그 외는 extension 설치 후) 지정 가능
• sql로 지정시 기본적인 쿼리문 사용 가능
  • if, 반복문 등 복잡한 부분은 plpgsql로

예시: 입력 값 없이 정수 반환

CREATE OR REPLACE FUNCTION one()
	RETURNS int4
AS $$
	select 1;
$$ LANGUAGE SQL;

--plpgsql
CREATE OR REPLACE FUNCTION one_plpgsql()
	RETURNS int4
AS $$
begin
	return 1;
end 
$$ LANGUAGE plpgsql;

예시: 입력 값 받아 연산 후 반환

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

예시: 테이블에서 값 조회

-- sql
CREATE OR REPLACE FUNCTION get_prod_info_sql(serial_no varchar) RETURNS NUMERIC AS 
$$
	SELECT target_weight FROM product_info WHERE product_info.serial_no = get_prod_info_sql.serial_no; 
$$ LANGUAGE SQL;

-- plpgsql
CREATE OR REPLACE FUNCTION get_prod_info(serial_no varchar) RETURNS NUMERIC AS $$
BEGIN
	RETURN (SELECT target_weight FROM product_info WHERE product_info.serial_no = get_prod_info.serial_no);
END;
$$ LANGUAGE plpgsql;

예시: 조건문

• SQL로는 CASE문과 같이 기본 SQL 문법만 사용 가능

CREATE OR REPLACE FUNCTION weight_pass_sql(weight integer) RETURNS text AS 
$$
	SELECT case when weight >= 40 then '합격' else '불합격' end; 
$$ LANGUAGE SQL;

--
CREATE OR REPLACE FUNCTION weight_pass(weight integer) RETURNS text AS 
$$
declare 
	result text;
BEGIN
	if weight >= 40 then 
		result := '합격'; 
	else 
		result := '불합격';
	end if;
	return result;
END 
$$ LANGUAGE plpgsql;

예시: 반복문

CREATE OR REPLACE FUNCTION 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 print_loop(5);

예시: 여러개의 값 리턴

CREATE OR REPLACE FUNCTION 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 multivalue(3);

예시: 뷰를 함수화

CREATE OR REPLACE FUNCTION view_line_delivery(p_line char)
RETURNS TABLE(
    line_id char,
    client_name varchar,
    product_count bigint
)
AS $$
BEGIN
    RETURN QUERY
    SELECT
        p.line_id,
        d.client_name,
        COUNT(p.serial_no)
    FROM product_info p
    JOIN qa_result q ON p.serial_no = q.serial_no
    JOIN delivery_log d ON d.serial_no = p.serial_no
    WHERE q.qa_status = 'P'
      AND p.line_id = p_line
    GROUP BY p.line_id, d.client_name;
END;
$$ LANGUAGE plpgsql;


SELECT * FROM view_line_delivery('B');

CREATE OR REPLACE FUNCTION decode_product_info(serial_no varchar)
RETURNS void
AS $$
DECLARE
    v_product_id  char;
    v_year        int;
    v_factory_nm  text;
BEGIN
    v_product_id := LEFT(serial_no, 1);
    v_year       := ('20' || SUBSTRING(serial_no, 2, 2))::int;

    SELECT
        CASE factory_code
            WHEN 'A' THEN '기흥공장'
            WHEN 'B' THEN '평택공장'
        END
    INTO v_factory_nm
    FROM product_info
    WHERE product_info.serial_no = decode_product_info.serial_no;

    RAISE NOTICE '(''%'' , % , ''%'')',
        v_product_id,
        v_year,
        v_factory_nm;
END;
$$ LANGUAGE plpgsql;

SELECT decode_product_info('P2600002');
-- ('P' , 2026 , '기흥공장')

뷰 vs 함수

프로시저

일련의 SQL명령과 로직을 데이터베이스에 저장해두고, 필요할 때마다 호출하여 실행할 수 있는 코드 블록

• 프로시저는 반환값이 필수가 아님
• CALL문으로 호출
• 내부에서 트랜잭션 제어 가능
• 여러 SQL문 실행, 복잡한 작업, 트랜잭션 관리에 적합

형식

CREATE [OR REPLACE] PROCEDURE 프로시저_이름(파라미터_목록)
LANGUAGE plpgsql
AS $$
BEGIN
-- SQL 로직
END;
$$;

DBeaver

사용 가능한 프로시저 확인

SELECT * FROM pg_available_extensions WHERE comment like '%procedural language';

프로시저 만들기

CREATE OR REPLACE PROCEDURE model_prod_proc()
AS $$
INSERT INTO model_prod_tbl(prod_date, model_nm, total_sum, save_time)
(
	SELECT prod_date, model_nm, total_sum, CURRENT_TIMESTAMP AS save_time
	FROM model_prod
	WHERE prod_date = '2026-02-04'
);
$$ LANGUAGE SQL;

프로시저 사용

CALL model_prod_proc();

잡(JOB)

특정 작업(쿼리, 함수, 프로시저 등)을 예약된 시간이나 주기로 자동 실행하는 기능을 의미

• 데이터베이스 유지 관리, 데이터 백업, 정기 보고서 생성, 데이터 정리 등 반복적인 작업을 자동화

PostgreSQL 잡 관리 방법

pg_cron 확장

• PostgreSQL 내에서 cron 스타일로 작업을 예약할 수 있는 확장 모듈
• 잡의 등록, 삭제, 상태 확인 등은 cron.job, cron.job_run_details 테이블에서 관리

pgAgent

• GUI를 통한 관리와 다양한 반복 옵션 제공
• pgAdmin에서 잡 생성, 실행 ,주기 설정, 상태 확인 가능

주요 활용 예시

• 정기적으로 데이터 백업 또는 아카이빙
• 오래된 데이터 자동 삭제
• 통계 데이터 집계 및 저장
• 데이터베이스 유지 관리

동작 구조

• 잡 스케줄러(pg_cron, pgAgent) 백그라운드에서 동작
• 예약된 시간에 지정된 SQL, 함수, 프로시저 실행
• 실행 결과와 상태는 전용 테이블(cron.job_run_details, pgagent.pga_joblog)에 기록
• 성공/실패, 실행 시간, 메시지 등의 확인 가능

잡 스케줄링 위한 pgAgent 설치

설치 확인

• 스키마에 pgagent 스키마 생성됨

잡 등록

• 특정 요일, 날짜, 시간 등 작업 설정 가능

1. create

1-2. Steps

• Edit 선택

• connection String: 리모트일때 입력

• code 입력

1-3. Schedules

• 시간 설정

• repeat 설정

1-4. save

DO $$
DECLARE
    jid integer;
    scid integer;
BEGIN
-- Creating a new job
INSERT INTO pgagent.pga_job(
    jobjclid, jobname, jobdesc, jobhostagent, jobenabled
) VALUES (
    1::integer, 'model_prod_proc_job'::text, '모델별 생산실적 입력 프로시저 스케줄링'::text, ''::text, true
) RETURNING jobid INTO jid;

-- Steps
-- Inserting a step (jobid: NULL)
INSERT INTO pgagent.pga_jobstep (
    jstjobid, jstname, jstenabled, jstkind,
    jstconnstr, jstdbname, jstonerror,
    jstcode, jstdesc
) VALUES (
    jid, 'step'::text, true, 's'::character(1),
    ''::text, 'postgres'::name, 'f'::character(1),
    'CALL practice.model_prod_proc();'::text, ''::text
) ;

-- Schedules
-- Inserting a 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:50:00+09'::timestamp with time zone, '2026-01-28 14:55:00+09'::timestamp with time zone,
    -- Minutes
    '{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[],
    -- Hours
    '{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[],
    -- Week days
    '{f,f,f,f,f,f,f}'::bool[]::boolean[],
    -- Month days
    '{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[],
    -- Months
    '{f,f,f,f,f,f,f,f,f,f,f,f}'::bool[]::boolean[]
) RETURNING jscid INTO scid;
END
$$;

실행 확인 - pga_jobsteplog

함수 호출 결과 파일로 저장하기

DO $body$
DECLARE
	file_path text;
BEGIN
	file_path := 'C:/Users/Public/view_line_delivery_' || to_char(now(),'YYYYMMDD_HH24MISS') || '.csv';
	EXECUTE format(
		'COPY (SELECT * from practice.view_line_delivery(''A'')) TO %L CSV HEADER', file_path
	);
END;
$body$;

프로시저 연습

(제품번호, 날짜, 중량) 을 입력받아 해당하는 제품-날짜 레코드가 테이블에 있는 경우 중량을 업데이트 하고 별도의 로그 테이블에 기록을 남기며, 번호나 날짜가 일치하지 않을 경우 로그 테이블에 오류로그와 함께 기록하는 프로시저 작성

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

CREATE OR REPLACE PROCEDURE practice.update_and_log_current_weight(
    s_no   varchar,
    p_date date,
    weight integer
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_old_weight integer;
    v_prod_start_date date;
BEGIN
    -- serial_no 존재 여부 확인
    SELECT target_weight, prod_start_date
    INTO v_old_weight, v_prod_start_date
    FROM practice.product_info
    WHERE serial_no = s_no;

    -- 대상 없음
    IF NOT FOUND THEN
        INSERT INTO practice.prod_log (
            serial_no,
            prod_date,
            new_weight,
            memo
        )
        VALUES (
            s_no,
            p_date,
            weight,
            format('대상 없음: %s', s_no)
        );

    -- 날짜 불일치
    ELSIF v_prod_start_date != p_date THEN
        INSERT INTO practice.prod_log (
            serial_no,
            prod_date,
            new_weight,
            memo
        )
        VALUES (
            s_no,
            p_date,
            weight,
            format('날짜 불일치: %s', p_date)
        );

    -- 정상 업데이트
    ELSE
        UPDATE practice.product_info
        SET
            target_weight = weight
        WHERE serial_no = s_no;

        INSERT INTO practice.prod_log (
            serial_no,
            prod_date,
            old_weight,
            new_weight,
            memo
        )
        VALUES (
            s_no,
            p_date,
            v_old_weight,
            weight,
            '정상 업데이트'
        );
    END IF;
END;
$$;

-- 실행
CALL practice.update_and_log_current_weight('P2600002', '2026-01-22', 800);
CALL practice.update_and_log_current_weight('P2600002', '2026-11-22', 700);
CALL practice.update_and_log_current_weight('P2222222', '2022-02-22', 500);