图片无版权网站,广西响应式网站制作,网站开发合同.doc,wordpress postviews1. 存储过程与函数
1.1 创建存储过程
存储过程是一组预编译的SQL语句#xff0c;可以简化复杂的操作。以下是一个简单的存储过程示例#xff1a;
CREATE OR REPLACE FUNCTION add_user(username VARCHAR, email VARCHAR)
RETURNS VOID AS $$
BEGININSERT INTO users (use…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, johnexample.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, aliceexample.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);系统文章目录
PostgreSQL 简介与基础一
PostgreSQL 基本SQL语法二
PostgreSQL 高级SQL查询三
PostgreSQL 数据库设计与管理四
PostgreSQL 高级功能五
PostgreSQL 性能优化与调优六
PostgreSQL 高可用性与灾难恢复策略七
PostgreSQL 安全性与权限管理八
PostgreSQL 高级功能与扩展九
PostgreSQL 分区表与并行查询十
PostgreSQL 索引优化与性能调优十一
PostgreSQL 日志管理与故障排查十二
PostgreSQL 高可用性与容错性十三
