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…...
iOS 26 携众系统重磅更新,但“苹果智能”仍与国行无缘
美国西海岸的夏天,再次被苹果点燃。一年一度的全球开发者大会 WWDC25 如期而至,这不仅是开发者的盛宴,更是全球数亿苹果用户翘首以盼的科技春晚。今年,苹果依旧为我们带来了全家桶式的系统更新,包括 iOS 26、iPadOS 26…...
《用户共鸣指数(E)驱动品牌大模型种草:如何抢占大模型搜索结果情感高地》
在注意力分散、内容高度同质化的时代,情感连接已成为品牌破圈的关键通道。我们在服务大量品牌客户的过程中发现,消费者对内容的“有感”程度,正日益成为影响品牌传播效率与转化率的核心变量。在生成式AI驱动的内容生成与推荐环境中࿰…...
Spring Boot面试题精选汇总
🤟致敬读者 🟩感谢阅读🟦笑口常开🟪生日快乐⬛早点睡觉 📘博主相关 🟧博主信息🟨博客首页🟫专栏推荐🟥活动信息 文章目录 Spring Boot面试题精选汇总⚙️ **一、核心概…...
涂鸦T5AI手搓语音、emoji、otto机器人从入门到实战
“🤖手搓TuyaAI语音指令 😍秒变表情包大师,让萌系Otto机器人🔥玩出智能新花样!开整!” 🤖 Otto机器人 → 直接点明主体 手搓TuyaAI语音 → 强调 自主编程/自定义 语音控制(TuyaAI…...
CRMEB 框架中 PHP 上传扩展开发:涵盖本地上传及阿里云 OSS、腾讯云 COS、七牛云
目前已有本地上传、阿里云OSS上传、腾讯云COS上传、七牛云上传扩展 扩展入口文件 文件目录 crmeb\services\upload\Upload.php namespace crmeb\services\upload;use crmeb\basic\BaseManager; use think\facade\Config;/*** Class Upload* package crmeb\services\upload* …...
JVM暂停(Stop-The-World,STW)的原因分类及对应排查方案
JVM暂停(Stop-The-World,STW)的完整原因分类及对应排查方案,结合JVM运行机制和常见故障场景整理而成: 一、GC相关暂停 1. 安全点(Safepoint)阻塞 现象:JVM暂停但无GC日志,日志显示No GCs detected。原因:JVM等待所有线程进入安全点(如…...
云原生玩法三问:构建自定义开发环境
云原生玩法三问:构建自定义开发环境 引言 临时运维一个古董项目,无文档,无环境,无交接人,俗称三无。 运行设备的环境老,本地环境版本高,ssh不过去。正好最近对 腾讯出品的云原生 cnb 感兴趣&…...
JVM 内存结构 详解
内存结构 运行时数据区: Java虚拟机在运行Java程序过程中管理的内存区域。 程序计数器: 线程私有,程序控制流的指示器,分支、循环、跳转、异常处理、线程恢复等基础功能都依赖这个计数器完成。 每个线程都有一个程序计数…...
【分享】推荐一些办公小工具
1、PDF 在线转换 https://smallpdf.com/cn/pdf-tools 推荐理由:大部分的转换软件需要收费,要么功能不齐全,而开会员又用不了几次浪费钱,借用别人的又不安全。 这个网站它不需要登录或下载安装。而且提供的免费功能就能满足日常…...
GruntJS-前端自动化任务运行器从入门到实战
Grunt 完全指南:从入门到实战 一、Grunt 是什么? Grunt是一个基于 Node.js 的前端自动化任务运行器,主要用于自动化执行项目开发中重复性高的任务,例如文件压缩、代码编译、语法检查、单元测试、文件合并等。通过配置简洁的任务…...
