PostgreSQL 高级功能（五）
1. 存储过程与函数
1.1 创建存储过程
存储过程是一组预编译的SQL语句，可以简化复杂的操作。以下是一个简单的存储过程示例：
CREATE OR REPLACE FUNCTION add_user(username VARCHAR, email VARCHAR)
RETURNS VOID AS $$
BEGININSERT INTO users (username, email) VALUES (username, email);
END;
$$ LANGUAGE plpgsql;
调用存储过程：
SELECT add_user('john_doe', 'john@example.com');
1.2 创建函数
函数与存储过程类似，但函数可以返回值。以下是一个计算两数之和的函数示例：
CREATE OR REPLACE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER AS $$
BEGINRETURN a + b;
END;
$$ LANGUAGE plpgsql;
调用函数：
SELECT add_numbers(10, 20);
2. 触发器
触发器是在特定事件发生时自动执行的程序，如插入、更新或删除操作。
2.1 创建触发器
以下示例展示了在 users 表上创建触发器，当插入新用户时，自动记录时间戳到 audit_log 表：
CREATE TABLE audit_log (id SERIAL PRIMARY KEY,username VARCHAR(50),action VARCHAR(50),action_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);CREATE OR REPLACE FUNCTION log_user_action()
RETURNS TRIGGER AS $$
BEGININSERT INTO audit_log (username, action) VALUES (NEW.username, 'INSERT');RETURN NEW;
END;
$$ LANGUAGE plpgsql;CREATE TRIGGER after_user_insert
AFTER INSERT ON users
FOR EACH ROW
EXECUTE FUNCTION log_user_action();
3. 扩展与模块
PostgreSQL 提供了丰富的扩展，可以扩展其功能。
3.1 安装与使用扩展
例如，安装并使用 pgcrypto 扩展进行数据加密：
CREATE EXTENSION pgcrypto;-- 使用 pgcrypto 进行数据加密
INSERT INTO users (username, email, password) VALUES
('alice', 'alice@example.com', crypt('mypassword', gen_salt('bf')));
3.2 常用扩展
- PostGIS： 提供地理空间数据支持。
- pg_trgm： 支持基于三元组的字符串相似性搜索。
- hstore： 键值对存储。
4. 全文搜索
PostgreSQL 提供了强大的全文搜索功能，用于高效地搜索文本数据。
4.1 配置全文搜索
创建表并插入数据：
CREATE TABLE documents (id SERIAL PRIMARY KEY,content TEXT
);INSERT INTO documents (content) VALUES
('PostgreSQL is a powerful, open-source object-relational database system.'),
('It has a proven architecture and runs on all major operating systems.');
4.2 创建全文搜索索引
创建全文搜索索引以提高搜索效率：
CREATE INDEX idx_gin_content ON documents USING GIN (to_tsvector('english', content));
4.3 执行全文搜索
使用 to_tsvector 和 to_tsquery 函数进行全文搜索：
SELECT * FROM documents
WHERE to_tsvector('english', content) @@ to_tsquery('PostgreSQL & powerful');
5. JSON 数据类型与操作
PostgreSQL 支持存储和操作 JSON 数据类型，非常适合处理半结构化数据。
5.1 存储 JSON 数据
创建包含 JSON 数据类型的表：
CREATE TABLE products (id SERIAL PRIMARY KEY,name VARCHAR(100),attributes JSONB
);INSERT INTO products (name, attributes) VALUES
('Laptop', '{"brand": "Dell", "memory": "16GB", "storage": "512GB"}'),
('Smartphone', '{"brand": "Apple", "memory": "4GB", "storage": "64GB"}');
5.2 查询 JSON 数据
使用操作符和函数查询 JSON 数据：
-- 查询 brand 为 Dell 的产品
SELECT * FROM products
WHERE attributes->>'brand' = 'Dell';-- 查询并提取存储大小
SELECT name, attributes->>'storage' AS storage FROM products;
6. 案例实战
6.1 练习题目
- 创建一个存储过程，插入订单数据并更新库存。
- 创建一个触发器，在删除订单时记录日志。
- 使用
pgcrypto加密用户密码。 - 为产品描述字段创建全文搜索索引并执行搜索。
6.2 示例答案
- 创建存储过程：
CREATE OR REPLACE FUNCTION add_order(user_id INTEGER, product_id INTEGER, quantity INTEGER)
RETURNS VOID AS $$
BEGININSERT INTO orders (user_id, product_id, quantity) VALUES (user_id, product_id, quantity);UPDATE products SET stock = stock - quantity WHERE id = product_id;
END;
$$ LANGUAGE plpgsql;
- 创建触发器：
CREATE TABLE order_log (id SERIAL PRIMARY KEY,order_id INTEGER,action VARCHAR(50),action_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);CREATE OR REPLACE FUNCTION log_order_deletion()
RETURNS TRIGGER AS $$
BEGININSERT INTO order_log (order_id, action) VALUES (OLD.id, 'DELETE');RETURN OLD;
END;
$$ LANGUAGE plpgsql;CREATE TRIGGER after_order_delete
AFTER DELETE ON orders
FOR EACH ROW
EXECUTE FUNCTION log_order_deletion();
- 使用
pgcrypto加密用户密码：
CREATE EXTENSION pgcrypto;UPDATE users SET password = crypt('newpassword', gen_salt('bf')) WHERE username = 'alice';
- 创建全文搜索索引并执行搜索：
CREATE INDEX idx_gin_description ON products USING GIN (to_tsvector('english', description));SELECT * FROM products
WHERE to_tsvector('english', description) @@ to_tsquery('powerful & laptop');