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');