当前位置: 首页 > news >正文

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_tsvectorto_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 练习题目

  1. 创建一个存储过程,插入订单数据并更新库存。
  2. 创建一个触发器,在删除订单时记录日志。
  3. 使用 pgcrypto 加密用户密码。
  4. 为产品描述字段创建全文搜索索引并执行搜索。

6.2 示例答案

  1. 创建存储过程:
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;
  1. 创建触发器:
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();
  1. 使用 pgcrypto 加密用户密码:
CREATE EXTENSION pgcrypto;UPDATE users SET password = crypt('newpassword', gen_salt('bf')) WHERE username = 'alice';
  1. 创建全文搜索索引并执行搜索:
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是一个模板参数包&#xff0c;args是一个函数形参参数包 // 声明一个参数包Args...args&#xff0c;这个参数包中可以包含0到任意个模板参数。 template <class ...Arg…...

昇思25天学习打卡营第2天|张量Tensor

张量Tensor 创建张量张量的属性张量索引张量运算 稀疏张量 总结 简单讲讲张量&#xff0c;数学和物理学界以一种方式定义张量&#xff0c;机器学习上则是以另一种方式定义张量&#xff0c;这里的张量也与神经网络联系紧密&#xff0c;神经网络需要进行大量的数学计算&#xff0…...

[leetcode]valid-triangle-number. 有效三角形的个数

. - 力扣&#xff08;LeetCode&#xff09; 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&#xff0c;对付多个数据库实例就有些要注意的了&#xff1a; 首先&#xff0c;同样连接字符串上加上“\实例名”&#xff1a; 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如何删除所有的空行&#xff1f; 很多网友从网页复制文字粘贴到word文档后发现段落之间有空行&#xff0c;如果文字不多&#xff0c;手动删除这些空行也没有多少工作量&#xff0c;但是如果文字的字数达到成千上万&#xff0c;一个个手动删除这些空行还是很繁琐的。那么&a…...

【CSS】深入探讨 CSS 的 `calc()` 函数

深入探讨 CSS 的 calc() 函数 calc() 是一个 CSS 函数&#xff0c;用于在样式表中进行数学计算&#xff0c;从而动态地设置 CSS 属性值。它允许开发者在指定长度、百分比、数值等时&#xff0c;进行加减乘除运算。通过 calc() 函数&#xff0c;我们可以实现更灵活和响应式的设…...

MongoDB异地备份数据文件脚本(带日志打印,便于排查)

此脚本是以文件夹的形式备份&#xff0c;非压缩包形式 如需备份成加密压缩包&#xff0c;可用此脚本&#xff1a;MongoDB定时异地备份所有数据库为加密压缩包-CSDN博客 1.可以直接下载本文件使用&#xff0c;将其放到mongo安装目录的bin目录下&#xff08;可手动执行&#xf…...

论文导读 | Manufacturing Service Operations Management近期文章精选

编者按 在本系列文章中&#xff0c;我们梳理了顶刊Manufacturing & Service Operations Management5月份发布有关OR/OM以及相关应用的文章之基本信息&#xff0c;旨在帮助读者快速洞察行业/学界最新动态。 推荐文章1 ● 题目&#xff1a;Robust Drone Delivery with Weath…...

【Linux命令】top linux下的任务管理器

一、概述 top命令是Linux下常用的性能分析工具&#xff0c;能够实时显示系统中各个进程的资源占用状况&#xff0c;类似于Windows的任务管理器。top是一个动态显示过程&#xff0c;即可以通过用户按键来不断刷新当前状态。如果在前台执行该命令&#xff0c;它将独占前台&#…...

2024年在分数限制下,选好专业还是选好学校?

分数限制下&#xff0c;选好专业还是选好学校&#xff1f; 24年高考帷幕落下&#xff0c;一场新的思考与选择悄然来临。对于每一位高考考生&#xff0c;学校和专业都是开启大学新生活的两个前置必选项。但有时候“鱼与熊掌不可兼得”&#xff0c;在分数受限的条件下&#xff0…...

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手势事件)

上篇回顾&#xff1a;ArkTS开发系列之事件&#xff08;2.8.1触屏、键鼠、焦点事件&#xff09; 本篇内容&#xff1a;ArkTS开发系列之事件&#xff08;2.8.2手势事件&#xff09; 一、绑定手势方法 1. 常规手势绑定方法 Text(手势).fontSize(44).gesture(TapGesture().onAct…...

【MATLAB源码-第135期】基于matlab的变色龙群优化算法CSA)机器人栅格路径规划,输出做短路径图和适应度曲线。

操作环境&#xff1a; MATLAB 2022a 1、算法描述 变色龙群优化算法&#xff08;Chameleon Swarm Algorithm&#xff0c;CSA&#xff09;是一种新颖的群体智能优化算法&#xff0c;受到自然界中变色龙捕食和社交行为的启发。变色龙以其独特的适应能力而著称&#xff0c;能够根…...

使用Python实现深度学习模型:语言模型与文本生成

语言模型是自然语言处理中的核心任务之一,它们用于预测文本中的下一个单词或生成与输入文本相关的新文本。本文将详细介绍如何使用Python实现一个语言模型,并通过这个模型进行文本生成。 我们将使用TensorFlow和Hugging Face的Transformers库来实现这一任务。 1. 语言模型简…...

大数据面试题之Hive(3)

目录 Hive的函数:UDF、UDAF、UDTF的区别? UDF是怎么在Hive里执行的 row_number&#xff0c;rank&#xff0c;dense_rank的区别 Hive count(distinct)有几个reduce&#xff0c;海量数据会有什么问题 HQL&#xff1a;行转列、列转行 一条HQL从代码到执行的过程 了解Hive S…...

测试微信模版消息推送

进入“开发接口管理”--“公众平台测试账号”&#xff0c;无需申请公众账号、可在测试账号中体验并测试微信公众平台所有高级接口。 获取access_token: 自定义模版消息&#xff1a; 关注测试号&#xff1a;扫二维码关注测试号。 发送模版消息&#xff1a; import requests da…...

《Qt C++ 与 OpenCV:解锁视频播放程序设计的奥秘》

引言:探索视频播放程序设计之旅 在当今数字化时代,多媒体应用已渗透到我们生活的方方面面,从日常的视频娱乐到专业的视频监控、视频会议系统,视频播放程序作为多媒体应用的核心组成部分,扮演着至关重要的角色。无论是在个人电脑、移动设备还是智能电视等平台上,用户都期望…...

【入坑系列】TiDB 强制索引在不同库下不生效问题

文章目录 背景SQL 优化情况线上SQL运行情况分析怀疑1:执行计划绑定问题?尝试:SHOW WARNINGS 查看警告探索 TiDB 的 USE_INDEX 写法Hint 不生效问题排查解决参考背景 项目中使用 TiDB 数据库,并对 SQL 进行优化了,添加了强制索引。 UAT 环境已经生效,但 PROD 环境强制索…...

【HarmonyOS 5.0】DevEco Testing:鸿蒙应用质量保障的终极武器

——全方位测试解决方案与代码实战 一、工具定位与核心能力 DevEco Testing是HarmonyOS官方推出的​​一体化测试平台​​&#xff0c;覆盖应用全生命周期测试需求&#xff0c;主要提供五大核心能力&#xff1a; ​​测试类型​​​​检测目标​​​​关键指标​​功能体验基…...

2.Vue编写一个app

1.src中重要的组成 1.1main.ts // 引入createApp用于创建应用 import { createApp } from "vue"; // 引用App根组件 import App from ./App.vue;createApp(App).mount(#app)1.2 App.vue 其中要写三种标签 <template> <!--html--> </template>…...

【算法训练营Day07】字符串part1

文章目录 反转字符串反转字符串II替换数字 反转字符串 题目链接&#xff1a;344. 反转字符串 双指针法&#xff0c;两个指针的元素直接调转即可 class Solution {public void reverseString(char[] s) {int head 0;int end s.length - 1;while(head < end) {char temp …...

相机从app启动流程

一、流程框架图 二、具体流程分析 1、得到cameralist和对应的静态信息 目录如下: 重点代码分析: 启动相机前,先要通过getCameraIdList获取camera的个数以及id,然后可以通过getCameraCharacteristics获取对应id camera的capabilities(静态信息)进行一些openCamera前的…...

佰力博科技与您探讨热释电测量的几种方法

热释电的测量主要涉及热释电系数的测定&#xff0c;这是表征热释电材料性能的重要参数。热释电系数的测量方法主要包括静态法、动态法和积分电荷法。其中&#xff0c;积分电荷法最为常用&#xff0c;其原理是通过测量在电容器上积累的热释电电荷&#xff0c;从而确定热释电系数…...

网站指纹识别

网站指纹识别 网站的最基本组成&#xff1a;服务器&#xff08;操作系统&#xff09;、中间件&#xff08;web容器&#xff09;、脚本语言、数据厍 为什么要了解这些&#xff1f;举个例子&#xff1a;发现了一个文件读取漏洞&#xff0c;我们需要读/etc/passwd&#xff0c;如…...

莫兰迪高级灰总结计划简约商务通用PPT模版

莫兰迪高级灰总结计划简约商务通用PPT模版&#xff0c;莫兰迪调色板清新简约工作汇报PPT模版&#xff0c;莫兰迪时尚风极简设计PPT模版&#xff0c;大学生毕业论文答辩PPT模版&#xff0c;莫兰迪配色总结计划简约商务通用PPT模版&#xff0c;莫兰迪商务汇报PPT模版&#xff0c;…...