-- 创建简单视图
CREATE VIEW active_users AS
SELECT id, username, email, created_at
FROM users
WHERE status = 'active';

-- 查询视图
SELECT * FROM active_users WHERE username LIKE 'A%';

-- 可更新视图
CREATE VIEW user_orders AS
SELECT 
    u.id,
    u.username,
    COUNT(o.id) AS order_count,
    COALESCE(SUM(o.total), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;

-- 插入数据到视图（通过规则）
CREATE OR REPLACE RULE "user_orders_insert" AS
    ON INSERT TO user_orders
    DO INSTEAD 
    INSERT INTO users (username) VALUES (NEW.username);

-- 创建只读视图
CREATE VIEW read_only_users AS
SELECT * FROM users
WITH READ ONLY;

-- 创建物化视图
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT 
    DATE_TRUNC('month', created_at) AS month,
    COUNT(*) AS order_count,
    SUM(total) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;

-- 在物化视图上创建索引
CREATE INDEX ON monthly_sales (month);

-- 刷新物化视图
REFRESH MATERIALIZED VIEW monthly_sales;

-- 并行刷新（不阻塞查询）
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales;

-- 查看物化视图信息
SELECT * FROM pg_matviews WHERE matviewname = 'monthly_sales';

-- 修改视图
CREATE OR REPLACE VIEW active_users AS
SELECT id, username, email, phone, created_at
FROM users
WHERE status = 'active';

-- 删除视图
DROP VIEW IF EXISTS old_view;

-- 重命名视图
ALTER VIEW old_name RENAME TO new_name;

-- 查看视图定义
SELECT pg_get_viewdef('view_name', true);

CREATE OR REPLACE FUNCTION function_name(params)
RETURNS return_type AS $$
DECLARE
    -- 变量声明
BEGIN
    -- 函数逻辑
    RETURN result;
END;
$$ LANGUAGE plpgsql;

-- 返回用户订单数量
CREATE OR REPLACE FUNCTION get_user_order_count(user_id INTEGER)
RETURNS INTEGER AS $$
DECLARE
    order_cnt INTEGER;
BEGIN
    SELECT COUNT(*) INTO order_cnt
    FROM orders
    WHERE user_id = get_user_order_count.user_id;
    
    RETURN order_cnt;
END;
$$ LANGUAGE plpgsql;

-- 调用函数
SELECT get_user_order_count(1);
SELECT username, get_user_order_count(id) AS order_count FROM users;

-- 返回表行类型
CREATE OR REPLACE FUNCTION get_user_info(user_id INTEGER)
RETURNS users AS $$
DECLARE
    user_record users;
BEGIN
    SELECT * INTO user_record
    FROM users
    WHERE id = get_user_info.user_id;
    
    RETURN user_record;
END;
$$ LANGUAGE plpgsql;

-- 调用
SELECT (get_user_info(1)).username;
SELECT (get_user_info(1)).*;

-- 返回表
CREATE OR REPLACE FUNCTION get_orders_by_status(order_status VARCHAR)
RETURNS TABLE (
    order_id INTEGER,
    user_id INTEGER,
    total DECIMAL(10, 2),
    status VARCHAR
) AS $$
BEGIN
    RETURN QUERY
    SELECT o.id, o.user_id, o.total, o.status
    FROM orders o
    WHERE o.status = get_orders_by_status.order_status;
END;
$$ LANGUAGE plpgsql;

-- 调用
SELECT * FROM get_orders_by_status('completed');

CREATE OR REPLACE FUNCTION calculate_discount(price NUMERIC, discount_rate NUMERIC)
RETURNS NUMERIC AS $$
DECLARE
    final_price NUMERIC;
    discount_amount NUMERIC;
BEGIN
    -- 类型转换
    discount_amount := price * discount_rate::NUMERIC;
    final_price := price - discount_amount;
    
    -- 使用 %TYPE
    DECLARE
        user_email users.email%TYPE;
        user_record users%ROWTYPE;
    BEGIN
        SELECT * INTO user_record FROM users WHERE id = 1;
        user_email := user_record.email;
    END;
    
    RETURN final_price;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION get_user_tier(total_spent NUMERIC)
RETURNS TEXT AS $$
BEGIN
    IF total_spent >= 10000 THEN
        RETURN 'VIP';
    ELSIF total_spent >= 5000 THEN
        RETURN 'Gold';
    ELSIF total_spent >= 1000 THEN
        RETURN 'Silver';
    ELSE
        RETURN 'Bronze';
    END IF;
END;
$$ LANGUAGE plpgsql;

-- CASE 表达式
CREATE OR REPLACE FUNCTION get_month_name(month_num INTEGER)
RETURNS TEXT AS $$
BEGIN
    RETURN CASE month_num
        WHEN 1 THEN 'January'
        WHEN 2 THEN 'February'
        WHEN 3 THEN 'March'
        WHEN 4 THEN 'April'
        WHEN 5 THEN 'May'
        WHEN 6 THEN 'June'
        WHEN 7 THEN 'July'
        WHEN 8 THEN 'August'
        WHEN 9 THEN 'September'
        WHEN 10 THEN 'October'
        WHEN 11 THEN 'November'
        WHEN 12 THEN 'December'
        ELSE 'Unknown'
    END CASE;
END;
$$ LANGUAGE plpgsql;

-- FOR 循环（整数范围）
CREATE OR REPLACE FUNCTION generate_series(start_num INTEGER, end_num INTEGER)
RETURNS TABLE(n INTEGER) AS $$
BEGIN
    FOR n IN start_num..end_num LOOP
        RETURN NEXT;
    END LOOP;
    RETURN;
END;
$$ LANGUAGE plpgsql;

-- WHILE 循环
CREATE OR REPLACE FUNCTION factorial(n INTEGER)
RETURNS BIGINT AS $$
DECLARE
    result BIGINT := 1;
    i INTEGER := 1;
BEGIN
    WHILE i <= n LOOP
        result := result * i;
        i := i + 1;
    END LOOP;
    RETURN result;
END;
$$ LANGUAGE plpgsql;

-- FOR 循环（查询结果）
CREATE OR REPLACE FUNCTION get_user_summary()
RETURNS TABLE(username TEXT, order_count BIGINT) AS $$
BEGIN
    FOR username, order_count IN 
        SELECT u.username, COUNT(o.id)
        FROM users u
        LEFT JOIN orders o ON u.id = o.user_id
        GROUP BY u.username
    LOOP
        RETURN NEXT;
    END LOOP;
    RETURN;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION transfer_funds(
    from_user_id INTEGER,
    to_user_id INTEGER,
    amount NUMERIC
)
RETURNS BOOLEAN AS $$
BEGIN
    -- 开始事务
    IF amount <= 0 THEN
        RAISE EXCEPTION '金额必须大于 0';
    END IF;
    
    -- 扣除转出账户
    UPDATE accounts 
    SET balance = balance - amount 
    WHERE user_id = from_user_id;
    
    IF NOT FOUND THEN
        RAISE EXCEPTION '转出账户不存在';
    END IF;
    
    -- 增加转入账户
    UPDATE accounts 
    SET balance = balance + amount 
    WHERE user_id = to_user_id;
    
    IF NOT FOUND THEN
        -- 回滚（隐式）
        RAISE EXCEPTION '转入账户不存在';
    END IF;
    
    RETURN TRUE;
EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE '交易失败: %', SQLERRM;
        RETURN FALSE;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION safe_divide(a NUMERIC, b NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    IF b = 0 THEN
        RAISE EXCEPTION '除数不能为零';
    END IF;
    
    RETURN a / b;
EXCEPTION
    WHEN raise_exception THEN
        RAISE NOTICE '发生错误: %', SQLERRM;
        RETURN NULL;
    WHEN OTHERS THEN
        RAISE NOTICE '未知错误: %', SQLERRM;
        RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- 审计日志触发器
CREATE OR REPLACE FUNCTION audit_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO audit_log (table_name, operation, old_data, new_data, user_name)
        VALUES (TG_TABLE_NAME, TG_OP, NULL, row_to_json(NEW), current_user);
        RETURN NEW;
        
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO audit_log (table_name, operation, old_data, new_data, user_name)
        VALUES (TG_TABLE_NAME, TG_OP, row_to_json(OLD), row_to_json(NEW), current_user);
        RETURN NEW;
        
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO audit_log (table_name, operation, old_data, new_data, user_name)
        VALUES (TG_TABLE_NAME, TG_OP, row_to_json(OLD), NULL, current_user);
        RETURN OLD;
    END IF;
    
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- 在表上创建触发器
CREATE TRIGGER users_audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION audit_trigger_function();

-- 行级触发器（BEFORE）
CREATE OR REPLACE FUNCTION before_user_update()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at := CURRENT_TIMESTAMP;
    NEW.updated_by := current_user;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_before_update
BEFORE UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION before_user_update();

-- 条件触发器
CREATE TRIGGER users_salary_audit
AFTER UPDATE OF salary ON employees
FOR EACH ROW EXECUTE FUNCTION audit_trigger_function();

-- 查看触发器
\d users

-- 禁用触发器
ALTER TABLE users DISABLE TRIGGER users_audit_trigger;

-- 启用触发器
ALTER TABLE users ENABLE TRIGGER users_audit_trigger;

-- 删除触发器
DROP TRIGGER IF EXISTS users_audit_trigger ON users;

-- 创建自定义聚合：字符串连接
CREATE AGGREGATE string_agg_delim (TEXT, TEXT) (
    SFUNC = CONCAT_WS,
    STYPE = TEXT,
    INITCOND = ''
);

-- 使用
SELECT string_agg_delim(DISTINCT email, ', ') FROM users;

-- 创建复合类型
CREATE TYPE inventory_item AS (
    product_name TEXT,
    supplier_name TEXT,
    quantity INTEGER
);

-- 在函数中使用
CREATE OR REPLACE FUNCTION get_inventory(product_id INTEGER)
RETURNS inventory_item AS $$
DECLARE
    item inventory_item;
BEGIN
    SELECT p.name, s.name, inv.quantity
    INTO item
    FROM products p
    JOIN inventory inv ON p.id = inv.product_id
    JOIN suppliers s ON inv.supplier_id = s.id
    WHERE p.id = product_id;
    
    RETURN item;
END;
$$ LANGUAGE plpgsql;

-- 调用
SELECT (get_inventory(1)).product_name;