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…...
Python|GIF 解析与构建(5):手搓截屏和帧率控制
目录 Python|GIF 解析与构建(5):手搓截屏和帧率控制 一、引言 二、技术实现:手搓截屏模块 2.1 核心原理 2.2 代码解析:ScreenshotData类 2.2.1 截图函数:capture_screen 三、技术实现&…...
国防科技大学计算机基础课程笔记02信息编码
1.机内码和国标码 国标码就是我们非常熟悉的这个GB2312,但是因为都是16进制,因此这个了16进制的数据既可以翻译成为这个机器码,也可以翻译成为这个国标码,所以这个时候很容易会出现这个歧义的情况; 因此,我们的这个国…...
conda相比python好处
Conda 作为 Python 的环境和包管理工具,相比原生 Python 生态(如 pip 虚拟环境)有许多独特优势,尤其在多项目管理、依赖处理和跨平台兼容性等方面表现更优。以下是 Conda 的核心好处: 一、一站式环境管理:…...
Go 语言接口详解
Go 语言接口详解 核心概念 接口定义 在 Go 语言中,接口是一种抽象类型,它定义了一组方法的集合: // 定义接口 type Shape interface {Area() float64Perimeter() float64 } 接口实现 Go 接口的实现是隐式的: // 矩形结构体…...
华为OD机试-食堂供餐-二分法
import java.util.Arrays; import java.util.Scanner;public class DemoTest3 {public static void main(String[] args) {Scanner in new Scanner(System.in);// 注意 hasNext 和 hasNextLine 的区别while (in.hasNextLine()) { // 注意 while 处理多个 caseint a in.nextIn…...
Springcloud:Eureka 高可用集群搭建实战(服务注册与发现的底层原理与避坑指南)
引言:为什么 Eureka 依然是存量系统的核心? 尽管 Nacos 等新注册中心崛起,但金融、电力等保守行业仍有大量系统运行在 Eureka 上。理解其高可用设计与自我保护机制,是保障分布式系统稳定的必修课。本文将手把手带你搭建生产级 Eur…...
Spring AI与Spring Modulith核心技术解析
Spring AI核心架构解析 Spring AI(https://spring.io/projects/spring-ai)作为Spring生态中的AI集成框架,其核心设计理念是通过模块化架构降低AI应用的开发复杂度。与Python生态中的LangChain/LlamaIndex等工具类似,但特别为多语…...
Swagger和OpenApi的前世今生
Swagger与OpenAPI的关系演进是API标准化进程中的重要篇章,二者共同塑造了现代RESTful API的开发范式。 本期就扒一扒其技术演进的关键节点与核心逻辑: 🔄 一、起源与初创期:Swagger的诞生(2010-2014) 核心…...
HarmonyOS运动开发:如何用mpchart绘制运动配速图表
##鸿蒙核心技术##运动开发##Sensor Service Kit(传感器服务)# 前言 在运动类应用中,运动数据的可视化是提升用户体验的重要环节。通过直观的图表展示运动过程中的关键数据,如配速、距离、卡路里消耗等,用户可以更清晰…...
安宝特方案丨船舶智造的“AR+AI+作业标准化管理解决方案”(装配)
船舶制造装配管理现状:装配工作依赖人工经验,装配工人凭借长期实践积累的操作技巧完成零部件组装。企业通常制定了装配作业指导书,但在实际执行中,工人对指导书的理解和遵循程度参差不齐。 船舶装配过程中的挑战与需求 挑战 (1…...
