CREATE VIEW v_order_summary AS
SELECT
    o.id           AS order_id,
    o.status,
    o.created_at,
    u.id           AS user_id,
    u.name         AS user_name,
    u.email        AS user_email,
    COUNT(oi.id)   AS item_count,
    SUM(oi.quantity * oi.unit_price) AS total_amount
FROM orders o
JOIN users u ON o.user_id = u.id
LEFT JOIN order_items oi ON o.id = oi.order_id
WHERE o.deleted_at IS NULL
GROUP BY o.id, o.status, o.created_at, u.id, u.name, u.email;

SELECT * FROM v_order_summary
WHERE user_id = 42
ORDER BY created_at DESC;

CREATE OR REPLACE VIEW v_order_summary AS
SELECT
    o.id AS order_id,
    o.status,
    o.created_at,
    u.name AS user_name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.deleted_at IS NULL;

DROP VIEW IF EXISTS v_order_summary;

DROP VIEW IF EXISTS v_order_summary CASCADE;

실행 완료 또는 조회 결과가 표시됩니다.

CREATE VIEW v_users_public AS
SELECT
    id,
    name,
    created_at,
    LEFT(email, 2) || '***@***' AS email_masked
FROM users
WHERE deleted_at IS NULL;

GRANT SELECT ON v_users_public TO analyst_role;
REVOKE SELECT ON users FROM analyst_role;

CREATE VIEW v_dept_employees AS
SELECT *
FROM employees
WHERE department_id = current_setting('app.current_department_id')::INT;

CREATE MATERIALIZED VIEW mv_monthly_revenue AS
SELECT
    DATE_TRUNC('month', o.created_at) AS month,
    p.category,
    COUNT(DISTINCT o.id) AS order_count,
    COUNT(DISTINCT o.user_id) AS customer_count,
    SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.status = 'completed'
GROUP BY 1, 2
WITH DATA;

CREATE UNIQUE INDEX ON mv_monthly_revenue (month, category);

REFRESH MATERIALIZED VIEW mv_monthly_revenue;

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_revenue;

CREATE OR REPLACE PROCEDURE proc_cancel_order(
    p_order_id BIGINT,
    p_reason   VARCHAR(500)
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_status VARCHAR(20);
BEGIN
    SELECT status INTO v_status
    FROM orders WHERE id = p_order_id;

    IF v_status IS NULL THEN
        RAISE EXCEPTION '주문을 찾을 수 없습니다: %', p_order_id;
    END IF;

    IF v_status = 'completed' THEN
        RAISE EXCEPTION '완료된 주문은 취소할 수 없습니다';
    END IF;

    UPDATE orders
    SET status = 'cancelled',
        cancelled_at = NOW(),
        cancel_reason = p_reason
    WHERE id = p_order_id;

    UPDATE inventory i
    SET stock = i.stock + oi.quantity
    FROM order_items oi
    WHERE oi.order_id = p_order_id
      AND i.product_id = oi.product_id;

    INSERT INTO order_logs (order_id, action, note, created_at)
    VALUES (p_order_id, 'CANCELLED', p_reason, NOW());

    COMMIT;
END;
$$;

CALL proc_cancel_order(12345, '고객 요청으로 취소');

CREATE OR REPLACE FUNCTION fn_calculate_discount(
    p_amount    NUMERIC,
    p_user_tier VARCHAR(20)
)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN CASE p_user_tier
        WHEN 'gold'   THEN p_amount * 0.85
        WHEN 'silver' THEN p_amount * 0.90
        ELSE               p_amount * 0.95
    END;
END;
$$;

SELECT
    product_name,
    price,
    fn_calculate_discount(price, 'gold') AS gold_price
FROM products;

CREATE OR REPLACE FUNCTION fn_audit_log()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO audit_logs (
        table_name, operation, old_data, new_data, changed_at, changed_by
    )
    VALUES (
        TG_TABLE_NAME,
        TG_OP,
        row_to_json(OLD),
        row_to_json(NEW),
        NOW(),
        current_setting('app.current_user_id', true)
    );
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_users_audit
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION fn_audit_log();

CALL proc_archive_old_orders(cutoff_date := '2023-01-01');

[뷰를 조회할 때]  SELECT * FROM v_order_summary WHERE user_id = 42
   │
   ① 파싱 — 구문 검사, 구문 트리 생성
   │
   ② 재작성(rewrite) — 뷰 정의를 바깥 쿼리에 병합(inline)
   │     Materialized View면 재작성 없이 디스크에 저장된 결과를 읽음
   │
   ③ 계획 수립 — 옵티마이저가 인덱스·조인 방법 결정
   │     단순 뷰: 바깥 WHERE(user_id=42)를 base 테이블로 밀어넣어 인덱스 사용
   │     윈도우·DISTINCT 낀 뷰: 못 밀어넣어 base 전체 계산(아래 심화 참고)
   │
   ④ 실행 — 기반 테이블에서 행 읽기 (일반 뷰는 조회마다 재실행)
   ▼
  결과

[프로시저를 호출할 때]  CALL proc_cancel_order(42, '고객 요청')
   │
   ① 파싱 — 본문의 여러 SQL을 구문 트리로
   │
   ② 컴파일 — 최초 호출에서 본문 문장별 실행 계획을 준비
   │
   ③ 계획 캐시 — 세션에 계획을 저장(첫 파라미터 기준 generic plan)
   │
   ④ 재사용 — 다음 CALL부터 캐시된 계획을 그대로 사용
   ▼
  결과 (매 호출마다 파싱·계획을 다시 하지 않아 빠름)