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');
系统文章目录:
PostgreSQL 简介与基础(一)
PostgreSQL 基本SQL语法(二)
PostgreSQL 高级SQL查询(三)
PostgreSQL 数据库设计与管理(四)
PostgreSQL 高级功能(五)
PostgreSQL 性能优化与调优(六)
PostgreSQL 高可用性与灾难恢复策略(七)
PostgreSQL 安全性与权限管理(八)
PostgreSQL 高级功能与扩展(九)
PostgreSQL 分区表与并行查询(十)
PostgreSQL 索引优化与性能调优(十一)
PostgreSQL 日志管理与故障排查(十二)
PostgreSQL 高可用性与容错性(十三)
相关文章:
PostgreSQL 高级功能(五)
1. 存储过程与函数 1.1 创建存储过程 存储过程是一组预编译的SQL语句,可以简化复杂的操作。以下是一个简单的存储过程示例: CREATE OR REPLACE FUNCTION add_user(username VARCHAR, email VARCHAR) RETURNS VOID AS $$ BEGININSERT INTO users (use…...
食品企业仓储式批发零售一体化解决方案
食品企业需要有效应对日益复杂的市场挑战和消费者需求的快速变化的挑战并提升市场竞争力,仓储式类的批发零售一体化需求应运而生。这一全新的商业模式不仅整合了传统的批发和零售模式,还优化了供应链管理和客户体验,成为食品行业发展的新引擎…...
chrome插件,修改对应URL的http请求的header头,包括ajax请求
要创建一个可以灵活修改HTTP请求头的Chrome扩展,包括一个用户界面来动态设置头部名称和值,可以按照以下步骤进行。我们会用到 chrome.storage API 来保存用户的设置,并在后台脚本中使用这些设置来修改请求头。 文件结构 my_chrome_extensio…...
C语言 | Leetcode C语言题解之第191题位1的个数
题目: 题解: int hammingWeight(uint32_t n) {int ret 0;while (n) {n & n - 1;ret;}return ret; }...
【C++11(二)】lambda表达式和可变参数模板
一、可变参数模板 C11的新特性可变参数模板 能够让您创建可以接受 可变参数的函数模板和类模板 // Args是一个模板参数包,args是一个函数形参参数包 // 声明一个参数包Args...args,这个参数包中可以包含0到任意个模板参数。 template <class ...Arg…...
昇思25天学习打卡营第2天|张量Tensor
张量Tensor 创建张量张量的属性张量索引张量运算 稀疏张量 总结 简单讲讲张量,数学和物理学界以一种方式定义张量,机器学习上则是以另一种方式定义张量,这里的张量也与神经网络联系紧密,神经网络需要进行大量的数学计算࿰…...
[leetcode]valid-triangle-number. 有效三角形的个数
. - 力扣(LeetCode) class Solution { public:int triangleNumber(vector<int>& nums) {int n nums.size();sort(nums.begin(), nums.end());int ans 0;for (int i 0; i < n; i) {for (int j i 1; j < n; j) {int left j 1, righ…...
java SQL server 多实例的情况
而对于java,对付多个数据库实例就有些要注意的了: 首先,同样连接字符串上加上“\实例名”: jdbc:sqlserver://127.0.0.1\\mssqlserver2008;DatabaseNameLPT; 此处应去掉端口1433。因为连接数据库自命名实例的url中没有端口号1433…...
html--404页面
<!DOCTYPE html> <html> <head> <meta http-equiv"Content-Type" content"text/html; charsetUTF-8"> <meta http-equiv"X-UA-Compatible" content"IEedge,chrome1"> <title>404 错误页面不存在&…...
[word] Word如何删除所有的空行? #职场发展#学习方法
Word如何删除所有的空行? 很多网友从网页复制文字粘贴到word文档后发现段落之间有空行,如果文字不多,手动删除这些空行也没有多少工作量,但是如果文字的字数达到成千上万,一个个手动删除这些空行还是很繁琐的。那么&a…...
【CSS】深入探讨 CSS 的 `calc()` 函数
深入探讨 CSS 的 calc() 函数 calc() 是一个 CSS 函数,用于在样式表中进行数学计算,从而动态地设置 CSS 属性值。它允许开发者在指定长度、百分比、数值等时,进行加减乘除运算。通过 calc() 函数,我们可以实现更灵活和响应式的设…...
MongoDB异地备份数据文件脚本(带日志打印,便于排查)
此脚本是以文件夹的形式备份,非压缩包形式 如需备份成加密压缩包,可用此脚本:MongoDB定时异地备份所有数据库为加密压缩包-CSDN博客 1.可以直接下载本文件使用,将其放到mongo安装目录的bin目录下(可手动执行…...
论文导读 | Manufacturing Service Operations Management近期文章精选
编者按 在本系列文章中,我们梳理了顶刊Manufacturing & Service Operations Management5月份发布有关OR/OM以及相关应用的文章之基本信息,旨在帮助读者快速洞察行业/学界最新动态。 推荐文章1 ● 题目:Robust Drone Delivery with Weath…...
【Linux命令】top linux下的任务管理器
一、概述 top命令是Linux下常用的性能分析工具,能够实时显示系统中各个进程的资源占用状况,类似于Windows的任务管理器。top是一个动态显示过程,即可以通过用户按键来不断刷新当前状态。如果在前台执行该命令,它将独占前台&#…...
2024年在分数限制下,选好专业还是选好学校?
分数限制下,选好专业还是选好学校? 24年高考帷幕落下,一场新的思考与选择悄然来临。对于每一位高考考生,学校和专业都是开启大学新生活的两个前置必选项。但有时候“鱼与熊掌不可兼得”,在分数受限的条件下࿰…...
cropperjs 裁剪/框选图片
1.效果 2.使用组件 <!-- 父级 --><Cropper ref"cropperRef" :imgUrl"url" searchImg"searchImg"></Cropper>3.封装组件 <template><el-dialog :title"title" :visible.sync"dialogVisible" wi…...
ArkTS开发系列之事件(2.8.2手势事件)
上篇回顾:ArkTS开发系列之事件(2.8.1触屏、键鼠、焦点事件) 本篇内容:ArkTS开发系列之事件(2.8.2手势事件) 一、绑定手势方法 1. 常规手势绑定方法 Text(手势).fontSize(44).gesture(TapGesture().onAct…...
【MATLAB源码-第135期】基于matlab的变色龙群优化算法CSA)机器人栅格路径规划,输出做短路径图和适应度曲线。
操作环境: MATLAB 2022a 1、算法描述 变色龙群优化算法(Chameleon Swarm Algorithm,CSA)是一种新颖的群体智能优化算法,受到自然界中变色龙捕食和社交行为的启发。变色龙以其独特的适应能力而著称,能够根…...
使用Python实现深度学习模型:语言模型与文本生成
语言模型是自然语言处理中的核心任务之一,它们用于预测文本中的下一个单词或生成与输入文本相关的新文本。本文将详细介绍如何使用Python实现一个语言模型,并通过这个模型进行文本生成。 我们将使用TensorFlow和Hugging Face的Transformers库来实现这一任务。 1. 语言模型简…...
大数据面试题之Hive(3)
目录 Hive的函数:UDF、UDAF、UDTF的区别? UDF是怎么在Hive里执行的 row_number,rank,dense_rank的区别 Hive count(distinct)有几个reduce,海量数据会有什么问题 HQL:行转列、列转行 一条HQL从代码到执行的过程 了解Hive S…...
微软PowerBI考试 PL300-选择 Power BI 模型框架【附练习数据】
微软PowerBI考试 PL300-选择 Power BI 模型框架 20 多年来,Microsoft 持续对企业商业智能 (BI) 进行大量投资。 Azure Analysis Services (AAS) 和 SQL Server Analysis Services (SSAS) 基于无数企业使用的成熟的 BI 数据建模技术。 同样的技术也是 Power BI 数据…...
CentOS下的分布式内存计算Spark环境部署
一、Spark 核心架构与应用场景 1.1 分布式计算引擎的核心优势 Spark 是基于内存的分布式计算框架,相比 MapReduce 具有以下核心优势: 内存计算:数据可常驻内存,迭代计算性能提升 10-100 倍(文档段落:3-79…...
mysql已经安装,但是通过rpm -q 没有找mysql相关的已安装包
文章目录 现象:mysql已经安装,但是通过rpm -q 没有找mysql相关的已安装包遇到 rpm 命令找不到已经安装的 MySQL 包时,可能是因为以下几个原因:1.MySQL 不是通过 RPM 包安装的2.RPM 数据库损坏3.使用了不同的包名或路径4.使用其他包…...
DeepSeek 技术赋能无人农场协同作业:用 AI 重构农田管理 “神经网”
目录 一、引言二、DeepSeek 技术大揭秘2.1 核心架构解析2.2 关键技术剖析 三、智能农业无人农场协同作业现状3.1 发展现状概述3.2 协同作业模式介绍 四、DeepSeek 的 “农场奇妙游”4.1 数据处理与分析4.2 作物生长监测与预测4.3 病虫害防治4.4 农机协同作业调度 五、实际案例大…...
智能AI电话机器人系统的识别能力现状与发展水平
一、引言 随着人工智能技术的飞速发展,AI电话机器人系统已经从简单的自动应答工具演变为具备复杂交互能力的智能助手。这类系统结合了语音识别、自然语言处理、情感计算和机器学习等多项前沿技术,在客户服务、营销推广、信息查询等领域发挥着越来越重要…...
动态 Web 开发技术入门篇
一、HTTP 协议核心 1.1 HTTP 基础 协议全称 :HyperText Transfer Protocol(超文本传输协议) 默认端口 :HTTP 使用 80 端口,HTTPS 使用 443 端口。 请求方法 : GET :用于获取资源,…...
数学建模-滑翔伞伞翼面积的设计,运动状态计算和优化 !
我们考虑滑翔伞的伞翼面积设计问题以及运动状态描述。滑翔伞的性能主要取决于伞翼面积、气动特性以及飞行员的重量。我们的目标是建立数学模型来描述滑翔伞的运动状态,并优化伞翼面积的设计。 一、问题分析 滑翔伞在飞行过程中受到重力、升力和阻力的作用。升力和阻力与伞翼面…...
WebRTC调研
WebRTC是什么,为什么,如何使用 WebRTC有什么优势 WebRTC Architecture Amazon KVS WebRTC 其它厂商WebRTC 海康门禁WebRTC 海康门禁其他界面整理 威视通WebRTC 局域网 Google浏览器 Microsoft Edge 公网 RTSP RTMP NVR ONVIF SIP SRT WebRTC协…...
P10909 [蓝桥杯 2024 国 B] 立定跳远
# P10909 [蓝桥杯 2024 国 B] 立定跳远 ## 题目描述 在运动会上,小明从数轴的原点开始向正方向立定跳远。项目设置了 $n$ 个检查点 $a_1, a_2, \cdots , a_n$ 且 $a_i \ge a_{i−1} > 0$。小明必须先后跳跃到每个检查点上且只能跳跃到检查点上。同时࿰…...
Vue 实例的数据对象详解
Vue 实例的数据对象详解 在 Vue 中,数据对象是响应式系统的核心,也是组件状态的载体。理解数据对象的原理和使用方式是成为 Vue 专家的关键一步。我将从多个维度深入剖析 Vue 实例的数据对象。 一、数据对象的定义方式 1. Options API 中的定义 在 Options API 中,使用 …...
