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…...
(LeetCode 每日一题) 3442. 奇偶频次间的最大差值 I (哈希、字符串)
题目:3442. 奇偶频次间的最大差值 I 思路 :哈希,时间复杂度0(n)。 用哈希表来记录每个字符串中字符的分布情况,哈希表这里用数组即可实现。 C版本: class Solution { public:int maxDifference(string s) {int a[26]…...
【Oracle APEX开发小技巧12】
有如下需求: 有一个问题反馈页面,要实现在apex页面展示能直观看到反馈时间超过7天未处理的数据,方便管理员及时处理反馈。 我的方法:直接将逻辑写在SQL中,这样可以直接在页面展示 完整代码: SELECTSF.FE…...
前端倒计时误差!
提示:记录工作中遇到的需求及解决办法 文章目录 前言一、误差从何而来?二、五大解决方案1. 动态校准法(基础版)2. Web Worker 计时3. 服务器时间同步4. Performance API 高精度计时5. 页面可见性API优化三、生产环境最佳实践四、终极解决方案架构前言 前几天听说公司某个项…...
vue3 字体颜色设置的多种方式
在Vue 3中设置字体颜色可以通过多种方式实现,这取决于你是想在组件内部直接设置,还是在CSS/SCSS/LESS等样式文件中定义。以下是几种常见的方法: 1. 内联样式 你可以直接在模板中使用style绑定来设置字体颜色。 <template><div :s…...
[10-3]软件I2C读写MPU6050 江协科技学习笔记(16个知识点)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16...
学习STC51单片机31(芯片为STC89C52RCRC)OLED显示屏1
每日一言 生活的美好,总是藏在那些你咬牙坚持的日子里。 硬件:OLED 以后要用到OLED的时候找到这个文件 OLED的设备地址 SSD1306"SSD" 是品牌缩写,"1306" 是产品编号。 驱动 OLED 屏幕的 IIC 总线数据传输格式 示意图 …...
HTML前端开发:JavaScript 常用事件详解
作为前端开发的核心,JavaScript 事件是用户与网页交互的基础。以下是常见事件的详细说明和用法示例: 1. onclick - 点击事件 当元素被单击时触发(左键点击) button.onclick function() {alert("按钮被点击了!&…...
【开发技术】.Net使用FFmpeg视频特定帧上绘制内容
目录 一、目的 二、解决方案 2.1 什么是FFmpeg 2.2 FFmpeg主要功能 2.3 使用Xabe.FFmpeg调用FFmpeg功能 2.4 使用 FFmpeg 的 drawbox 滤镜来绘制 ROI 三、总结 一、目的 当前市场上有很多目标检测智能识别的相关算法,当前调用一个医疗行业的AI识别算法后返回…...
基于matlab策略迭代和值迭代法的动态规划
经典的基于策略迭代和值迭代法的动态规划matlab代码,实现机器人的最优运输 Dynamic-Programming-master/Environment.pdf , 104724 Dynamic-Programming-master/README.md , 506 Dynamic-Programming-master/generalizedPolicyIteration.m , 1970 Dynamic-Programm…...
使用 SymPy 进行向量和矩阵的高级操作
在科学计算和工程领域,向量和矩阵操作是解决问题的核心技能之一。Python 的 SymPy 库提供了强大的符号计算功能,能够高效地处理向量和矩阵的各种操作。本文将深入探讨如何使用 SymPy 进行向量和矩阵的创建、合并以及维度拓展等操作,并通过具体…...
