PostgreSQL 高级特性

视图

基本视图

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

-- 使用视图
SELECT * FROM active_users;

-- 更新视图（受限于简单视图）
UPDATE active_users SET age = 30 WHERE username = 'zhangsan';

可更新视图

-- 创建可更新视图
CREATE OR REPLACE VIEW user_summary 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 RULE user_summary_update AS
    ON UPDATE TO user_summary
    DO INSTEAD
    UPDATE users SET username = NEW.username WHERE id = OLD.id;

物化视图

-- 创建物化视图（存储查询结果）
CREATE MATERIALIZED VIEW user_stats AS
SELECT 
    status,
    COUNT(*) AS user_count,
    AVG(age) AS avg_age
FROM users
GROUP BY status;

-- 刷新物化视图
REFRESH MATERIALIZED VIEW user_stats;
REFRESH MATERIALIZED VIEW CONCURRENTLY user_stats;

-- 查看物化视图
SELECT * FROM user_stats;

触发器

基本触发器

-- 创建触发器函数
CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
CREATE TRIGGER update_users_timestamp
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_timestamp();

-- 删除触发器
DROP TRIGGER update_users_timestamp ON users;

审计触发器

-- 创建审计日志表
CREATE TABLE audit_log (
    id SERIAL PRIMARY KEY,
    table_name VARCHAR(50),
    action VARCHAR(10),
    old_data JSONB,
    new_data JSONB,
    changed_at TIMESTAMPTZ DEFAULT NOW()
);

-- 创建审计函数
CREATE OR REPLACE FUNCTION audit_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO audit_log (table_name, action, new_data)
        VALUES (TG_TABLE_NAME, TG_OP, to_jsonb(NEW));
        RETURN NEW;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO audit_log (table_name, action, old_data, new_data)
        VALUES (TG_TABLE_NAME, TG_OP, to_jsonb(OLD), to_jsonb(NEW));
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO audit_log (table_name, action, old_data)
        VALUES (TG_TABLE_NAME, TG_OP, to_jsonb(OLD));
        RETURN OLD;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- 应用审计触发器
CREATE TRIGGER audit_users
    AFTER INSERT OR UPDATE OR DELETE ON users
    FOR EACH ROW
    EXECUTE FUNCTION audit_trigger();

条件触发器

-- 仅在特定条件时触发
CREATE TRIGGER audit_salary_change
    AFTER UPDATE OF salary ON employees
    FOR EACH ROW
    WHEN (OLD.salary IS DISTINCT FROM NEW.salary)
    EXECUTE FUNCTION audit_trigger();

存储过程

PL/pgSQL 函数

-- 创建函数
CREATE OR REPLACE FUNCTION get_user_count(status_param VARCHAR)
RETURNS INTEGER AS $$
DECLARE
    count_val INTEGER;
BEGIN
    SELECT COUNT(*) INTO count_val
    FROM users
    WHERE status = status_param;
    RETURN count_val;
END;
$$ LANGUAGE plpgsql;

-- 调用函数
SELECT get_user_count('active');

-- 带默认参数
CREATE OR REPLACE FUNCTION get_users(limit_val INTEGER DEFAULT 10)
RETURNS SETOF users AS $$
BEGIN
    RETURN QUERY SELECT * FROM users LIMIT limit_val;
END;
$$ LANGUAGE plpgsql;

事务控制

-- 函数中的事务控制
CREATE OR REPLACE FUNCTION transfer_funds(
    from_user_id INTEGER,
    to_user_id INTEGER,
    amount DECIMAL
) 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 AND balance >= amount;
    
    IF NOT FOUND THEN
        RAISE EXCEPTION '余额不足或用户不存在';
    END IF;
    
    UPDATE accounts 
    SET balance = balance + amount 
    WHERE user_id = to_user_id;
    
    RETURN TRUE;
EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE '转账失败: %', SQLERRM;
        RETURN FALSE;
END;
$$ LANGUAGE plpgsql;

游标

CREATE OR REPLACE FUNCTION process_users_batch()
RETURNS VOID AS $$
DECLARE
    user_record RECORD;
    cursor_users CURSOR FOR SELECT * FROM users WHERE processed = false;
BEGIN
    OPEN cursor_users;
    LOOP
        FETCH cursor_users INTO user_record;
        EXIT WHEN NOT FOUND;
        
        -- 处理逻辑
        RAISE NOTICE 'Processing user: %', user_record.username;
        
        UPDATE users SET processed = true WHERE id = user_record.id;
    END LOOP;
    CLOSE cursor_users;
END;
$$ LANGUAGE plpgsql;

序列

-- 创建序列
CREATE SEQUENCE my_sequence START 1 INCREMENT 1;

-- 使用序列
SELECT nextval('my_sequence');
SELECT currval('my_sequence');
SELECT lastval();

-- 设置序列值
SELECT setval('my_sequence', 100);

-- 删除序列
DROP SEQUENCE my_sequence;

继承

-- 创建父表
CREATE TABLE items (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2)
);

-- 创建子表
CREATE TABLE books (
    author VARCHAR(100)
) INHERITS (items);

-- 查询包含父表和子表的数据
SELECT * FROM items;
SELECT * FROM books;
SELECT * FROM ONLY items;  -- 只查询父表

-- 查看继承关系
SELECT * FROM pg_tables WHERE tablename LIKE '%items%';

分区表

范围分区

-- 创建分区表
CREATE TABLE orders (
    id SERIAL,
    created_at DATE,
    total DECIMAL(10,2)
) PARTITION BY RANGE (created_at);

-- 创建分区
CREATE TABLE orders_2024_q1 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

CREATE TABLE orders_2024_q2 PARTITION OF orders
    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

CREATE TABLE orders_2024_q3 PARTITION OF orders
    FOR VALUES FROM ('2024-07-01') TO ('2024-10-01');

CREATE TABLE orders_2024_q4 PARTITION OF orders
    FOR VALUES FROM ('2024-10-01') TO ('2025-01-01');

-- 默认分区（捕获未匹配的数据）
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

列表分区

CREATE TABLE users (
    id SERIAL,
    country VARCHAR(50),
    name VARCHAR(100)
) PARTITION BY LIST (country);

CREATE TABLE users_asia PARTITION OF users
    FOR VALUES IN ('China', 'Japan', 'Korea', 'India');

CREATE TABLE users_europe PARTITION OF users
    FOR VALUES IN ('Germany', 'France', 'UK');

CREATE TABLE users_other PARTITION OF users DEFAULT;

哈希分区

CREATE TABLE users_hash (
    id SERIAL,
    name VARCHAR(100)
) PARTITION BY HASH (id);

CREATE TABLE users_hash_0 PARTITION OF users_hash
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);

CREATE TABLE users_hash_1 PARTITION OF users_hash
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);

CREATE TABLE users_hash_2 PARTITION OF users_hash
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);

CREATE TABLE users_hash_3 PARTITION OF users_hash
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

规则系统

-- 创建重写规则
CREATE RULE users_insert AS
    ON INSERT TO users_view
    DO INSTEAD
    INSERT INTO users (username, email) VALUES (NEW.username, NEW.email);

-- 创建视图作为表
CREATE VIEW users_view AS SELECT * FROM users;

-- 规则与视图结合
CREATE OR REPLACE RULE users_update AS
    ON UPDATE TO users_view
    DO INSTEAD
    UPDATE users SET username = NEW.username, email = NEW.email WHERE id = OLD.id;

通告（规则表达式）

-- 简单通告
NOTIFY my_channel;

-- 带消息
NOTIFY my_channel, 'This is a message';

-- 接收通告（在 psql 中）
LISTEN my_channel;

锁

-- 行级锁
SELECT * FROM users WHERE id = 1 FOR UPDATE;

-- 共享锁
SELECT * FROM users WHERE id = 1 FOR SHARE;

-- 咨询锁
SELECT pg_advisory_lock(12345);
SELECT pg_advisory_unlock(12345);

-- 尝试获取咨询锁（不阻塞）
SELECT pg_try_advisory_lock(12345);