PostgreSQL 고급 SQL 및 함수
1️⃣ 고급 SQL 기능
1. CTE (Common Table Expressions)와 Recursive Query
CTE(Common Table Expression)는 복잡한 쿼리를 쉽게 구성할 수 있도록 도와주는 기능입니다.
✅ 기본 CTE 사용 예제
WITH recent_orders AS (
SELECT customer_id, order_id, order_date
FROM orders
WHERE order_date >= NOW() - INTERVAL '30 days'
)
SELECT * FROM recent_orders;✅ 재귀 CTE 사용 예제
WITH RECURSIVE employee_hierarchy AS (
SELECT id, name, manager_id
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id
FROM employees e
INNER JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;2. JSON/JSONB 데이터 처리
PostgreSQL에서는 JSON과 JSONB 형식을 지원하여 구조화된 데이터를 효율적으로 저장하고 처리할 수 있습니다.
✅ JSON 데이터 조회 예제
SELECT data->>'name' AS name, data->>'email' AS email
FROM users
WHERE data->>'status' = 'active';✅ JSONB 데이터 업데이트 예제
UPDATE users
SET data = jsonb_set(data, '{phone}', '"123-456-7890"')
WHERE id = 1;3. 배열(Array) 데이터 타입 활용
PostgreSQL에서는 배열 타입을 사용하여 다중 값을 저장할 수 있습니다.
✅ 배열 데이터 삽입
INSERT INTO students (name, scores)
VALUES ('John Doe', ARRAY[85, 90, 78]);✅ 배열 요소 검색
SELECT * FROM students WHERE 90 = ANY(scores);4. 윈도우 함수 (Window Functions)
윈도우 함수는 집계 함수와 달리 행 단위로 연산을 수행할 수 있습니다.
✅ 기본 윈도우 함수 예제
SELECT name, department, salary,
RANK() OVER(PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;5. Full-Text Search 기능
PostgreSQL의 Full-Text Search(FTS)는 문자열 검색을 최적화하는 기능입니다.
✅ 기본 FTS 검색
SELECT * FROM articles
WHERE to_tsvector(content) @@ to_tsquery('database & performance');2️⃣ 트랜잭션 및 동시성 제어
1. 트랜잭션 관리 (BEGIN, COMMIT, ROLLBACK)
PostgreSQL에서는 ACID(Atomicity, Consistency, Isolation, Durability) 특성을 보장하는 트랜잭션을 지원합니다.
✅ 트랜잭션 예제
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;✅ ROLLBACK 예제
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
ROLLBACK;2. SAVEPOINT 및 부분 롤백
SAVEPOINT는 트랜잭션 내에서 특정 시점으로 롤백할 수 있도록 도와줍니다.
✅ SAVEPOINT 활용 예제
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
SAVEPOINT save1;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;
ROLLBACK TO SAVEPOINT save1;
COMMIT;3. Isolation Levels (Read Committed, Repeatable Read, Serializable)
PostgreSQL은 다양한 격리 수준을 제공하여 동시성 제어를 지원합니다.
✅ Isolation Level 설정 예제
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;3️⃣ 스토어드 프로시저 및 트리거
1. PL/pgSQL 기초 및 함수 작성법
PL/pgSQL은 PostgreSQL에서 제공하는 프로시저 언어입니다.
✅ 기본 PL/pgSQL 함수 예제
CREATE FUNCTION add_numbers(a INT, b INT) RETURNS INT AS $$
BEGIN
RETURN a + b;
END;
$$ LANGUAGE plpgsql;2. 트리거 (BEFORE, AFTER, INSTEAD OF)
트리거는 특정 이벤트 발생 시 자동으로 실행되는 함수입니다.
✅ 트리거 예제
CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
NEW.modified_at = NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER before_update_trigger
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_modified_column();3. Event Trigger 활용
Event Trigger는 데이터베이스 이벤트(DDL 변경 등)에 반응하도록 설정할 수 있습니다.
✅ Event Trigger 예제
CREATE EVENT TRIGGER log_table_creation
ON ddl_command_start
WHEN TAG IN ('CREATE TABLE')
EXECUTE FUNCTION log_event();마지막 수정일자