CREATE FUNCTION name(arg type) RETURNS type LANGUAGE sql AS $$ ... $$;는 쿼리에서 내장 함수처럼 호출할 수 있는 함수를 정의합니다. 본문은 $$ 표시 사이에 일반 SQL이나 PostgreSQL의 절차적 언어인 PL/pgSQL로 씁니다. CREATE PROCEDURE(PostgreSQL 11+)는 대신 CALL로 실행하는 루틴을 정의합니다.
with_tax(50)은 기본 세율을 써서 60을, with_tax(50, 0.1)은 55를 돌려줍니다. 인자를 이름으로 넘길 수도 있습니다: with_tax(50, rate => 0.1).
PL/pgSQL로 로직 작성하기
함수에 변수, IF, 반복문, 오류 처리가 필요하면 LANGUAGE plpgsql을 쓰세요. 변수는 DECLARE 부분에, 코드는 BEGIN과 END 사이에 씁니다:
4 kg 소포는 기본 요금 5이고, 20 kg 특급 소포는 (5 + 10 * 0.5) * 2 = 20입니다. PL/pgSQL 안에서 RAISE NOTICE 'value: %', cost;는 메시지를 출력하고, RAISE EXCEPTION은 오류를 내며 중단합니다.
일반 SQL로 로직을 표현할 수 있다면 LANGUAGE sql을 쓰세요. 플래너가 단순한 SQL 함수를 호출하는 쿼리 안에 인라인할 수 있습니다.
행 집합 돌려주기
RETURNS TABLE (...)은 행을 돌려주는 함수를 만듭니다. FROM에서 테이블처럼 호출하세요:
orders_of('ana')는 주문 1과 3을 돌려줍니다. RETURNS SETOF orders는 나열한 컬럼 대신 orders 테이블의 행 전체를 돌려줍니다.
PostgreSQL 14부터 SQL 함수는 문자열 대신 표준 SQL 본문을 가질 수 있습니다. PostgreSQL은 이를 파싱된 형태로 저장하고 사용하는 테이블과 컬럼을 기록하므로, 함수가 있는 동안 그것들을 지울 수 없습니다:
CREATE FUNCTION order_count(who text) RETURNS bigint
LANGUAGE sql STABLE
BEGIN ATOMIC
SELECT count(*) FROM orders WHERE customer = who;
END;
저장 프로시저를 만들고 CALL로 실행하기
프로시저에는 반환 타입이 없고 CALL로 실행합니다. 데이터를 바꿀 수 있으며, CALL이 명시적 트랜잭션 블록 안에 있지 않으면 도중에 COMMIT할 수 있습니다. 큰 작업을 배치로 나눠 처리하는 방법입니다.
계좌 1은 70, 계좌 2는 80이 됩니다. 200을 이체하면 계좌 1의 CHECK 제약 조건을 어기게 되고, 그 오류로 두 업데이트가 모두 롤백됩니다.
| 함수 | 프로시저 | |
|---|---|---|
| 실행 방법 | SELECT f(...), 쿼리 어디서나 | CALL p(...)만 |
| 반환 | 값, 행 하나, 또는 행 집합 | 없음(또는 OUT/INOUT 파라미터) |
안에서 COMMIT / ROLLBACK | 불가 | 가능, PL/pgSQL에서, 트랜잭션 블록 안에서 호출하지 않을 때 |
| 도입 시기 | 처음부터 | PostgreSQL 11 |
volatility 표시하기: IMMUTABLE, STABLE, VOLATILE
volatility 표시는 함수에 대해 플래너가 무엇을 가정해도 되는지 알려 줍니다. 기본값은 VOLATILE입니다.
IMMUTABLE: 결과가 인자에만 달려 있고 절대 바뀌지 않습니다(with_tax). 인덱스 식에 쓰는 함수는 이것이어야 합니다.STABLE: 결과가 문장 사이에서는 바뀔 수 있지만 한 문장 안에서는 바뀌지 않습니다. 예를 들어 테이블을 읽는 함수(orders_of)입니다.VOLATILE: 그 밖의 모든 것, 예를 들어random()을 쓰거나 데이터를 바꾸는 함수입니다. PostgreSQL은 행마다 다시 호출합니다.
표시가 틀리면 결과도 틀립니다. 테이블을 읽는 IMMUTABLE 함수는 인덱스나 캐시된 실행 계획에서 오래된 값을 돌려줄 수 있습니다.
함수 교체, 오버로드, 삭제하기
CREATE OR REPLACE FUNCTION은 기존 함수의 본문을 바꾸지만 반환 타입은 바꿀 수 없습니다. 반환 타입을 바꾸려면 먼저 지우세요. PostgreSQL은 함수를 이름과 인자 타입으로 구별하므로 f(int)와 f(text)는 서로 다른 함수입니다. 이름이 같은 두 함수 중 하나를 지우려면 인자 타입을 적으세요:
CREATE OR REPLACE FUNCTION with_tax(price numeric, rate numeric DEFAULT 0.2) ...;
DROP FUNCTION with_tax(numeric, numeric);
DROP PROCEDURE transfer;
PL/pgSQL 공식 문서에 반복문, 커서, 예외, EXECUTE를 쓰는 동적 SQL이 정리되어 있습니다. 행이 바뀔 때 함수를 자동으로 실행하려면 트리거에 연결하세요.
자주 묻는 질문
PostgreSQL에서 함수와 프로시저는 무엇이 다른가요?
함수는 값을 돌려주며 쿼리 안에서 호출합니다(SELECT f(x)). PostgreSQL 11에서 추가된 프로시저는 CALL로 따로 실행하며, 본문 안에서 COMMIT이나 ROLLBACK을 할 수 있습니다. 함수는 항상 호출한 쪽의 트랜잭션 안에서 실행됩니다.
PostgreSQL 함수의 $$는 무슨 뜻인가요?
$$는 달러 인용(dollar quoting)으로, 문자열 리터럴의 시작과 끝을 표시합니다. 그래서 함수 본문에 작은따옴표를 이스케이프 없이 쓸 수 있습니다. $body$ ... $body$처럼 태그를 붙일 수도 있습니다.
PostgreSQL 함수의 코드는 어떻게 보나요?
psql에서 \df는 함수 목록을, \sf name은 함수 정의를 보여 줍니다. SQL로는 SELECT pg_get_functiondef('name'::regproc);가 전체 CREATE OR REPLACE FUNCTION 문장을 돌려줍니다.
PostgreSQL 함수가 여러 행을 돌려줄 수 있나요?
네. RETURNS TABLE (col type, ...)이나 RETURNS SETOF type으로 선언하고, FROM 절에서 테이블처럼 호출하세요: SELECT * FROM f(1);.