当前位置： 首页 > article >正文

PostgreSQL中使用PL/pgSQL开发函数

article 2024/12/23 0:46:22

SQL被PostgreSQL和大多数其他关系型数据库用作查询语言。这样做有好处，因为大多数数据库都支持SQL，因此我们不需要改动SQL就能够调用不同的数据库的服务。

但是在使用SQL做应用开发时，你会发现这样一个问题：我们可能需要运行多次的SQL语句以完成某个业务操作，比如：在销售处理系统中，你需要在销售完成的时候，多次查询数据库以形成完整的订单信息，然后你还需要将订单信息插入回数据库的订单表中。

这意味着客户端应用必须发送每一个查询到数据库服务器中、等待服务器处理完成、服务器将结果传输回来、然后客户端继续运算、客户端追加更多的查询…。如果客户端和服务器不再同一台机器上，这样的运行方式会产生更多的网络开销。

这时候，我们可能需要将一部分的业务逻辑写入数据库服务器内部，以此来节省网络开销。

请注意：将业务逻辑写入数据库内部可能会导致数据库的可移植性降低，并且写入数据库中的业务逻辑的版本管理也将成为新的问题。如何进行选择还是需要进行需求工程的分析以及项目管理上的权衡。

想要将业务逻辑写入数据库服务器的内部，那么就需要使用到PostgreSQL中的函数了。

PL/pgSQL

PL/pgSQL是PostgreSQL数据库的存储过程语言，它是一种过程化的SQL扩展，允许用户编写复杂的控制结构，如循环和条件语句。

SQL作为一种结构化查询语言，SELECT UPDATE INSERT DELETE你每次只能够执行一个操作，你在一个操作中执行多个SELECT或者是将以上四个操作串连起来。总的来说就是SQL不能够“过程化”地执行。而我们在实际应用中却希望在数据库中过程化地执行SQL，虽然SQL有标准，但是过程化的SQL实际上是没有标准的，因此不同的数据库提供的过程化SQL的扩展实际上大概率是不兼容的。

学习的目标

虽然PL/pgSQL是一门我之前没有见过的语言，它具有数据类型、函数、控制结构、循环等等的东西，我们从头开始了解它的全部似乎是有些困难的（可能也并不困难，但是去了解那些我已经熟悉但就是语法上略有不同的东西，我是完全没有动力）。因此，我这里只针对于：

  1. 如何定义函数；
  2. 如何使用函数；
  3. 在函数中，如何使用SELECT语句并对SELECT查询的结果集进行操作；
  4. 在函数中，如何将变量作为参数去执行SELECT UPDATE INSERT DELETE这四种操作；
  5. 根据需求去学习剩余的知识；

对于我们这些学过几门编程语言的人来说，这个PL/pgSQL实际上就是“新编程语言 + SQL语句”的编程语言，只要学会如何调用SQL语句就基本将这些东西学会了一大半。

如何定义函数

正常来说，在大部分编程语言中，函数都是由四个玩意组成的：

  1. 函数名；
  2. 传入参数；
  3. 返回值；
  4. 函数体；

在PL/pgSQL中则是这样来定义的：

-- or replace 代表如果该函数以存在，那么当下新定义的函数将替代旧的同名函数
create or replace function 函数名(传入参数) returns 返回值 AS $$
declare
	-- 变量需要在这里定义，才能够在函数体中使用
	变量声明
begin
	函数体定义
end
-- 定义函数体所使用的语言，PostgreSQL支持多种语言来编写函数，可以是PL/pgSQL,PL/Perl,PL/Tcl,PL/Python
$$ language plpgsql;

如何使用函数

我们在SQL中可以使用聚合函数min max，还可以使用字符串拼接函数如concat()。正常来说可以在SQL使用函数的地方都能够使用函数，比如：

create or replace function add_int(a int, b int) returns int as $$
begin
	return a + b;
end
$$ language plpgsql;

select add_int(1,2) res;

你就能够得到一张1行1列的表：

testdb=# select add_int(1,2) res;
 res
-----
   3
(1 row)

在函数中，如何使用SELECT语句并对SELECT查询的结果集进行操作；

我创建了这样一张表，其内容如下：

testdb=# select * from employee;
 id |       name
----+------------------
  0 | student0
  1 | student1
  2 | student2
  3 | student3
  4 | student4
(5 rows)

如果我想要所有employee中的所有学生对校长说好，那么该如何编写这个函数呢？（先不要考虑为什么雇员表中会存在学生了，我写错了）。

已知：

RAISE NOTICE '% Say Hello to 校长', '张三';
-- 打印"张三 Say Hello to 校长"

我们可以通过select id,name from employee;拿到一个数组，该数组的单个元素中包含着学生的id和name这是毫无疑问的，那么如何接收这个数据并进行遍历呢？

一个简单的解决方法是使用for循环：

for res in select id,name from employee loop
	raise notice '% Say Hello to 校长', res.name;
end loop;

我们将其写入函数：

create or replace function say_hello_to_principal() returns void as $$
begin
	for res in select id,name from employee loop
  	raise notice '% Say Hello to 校长', res.name;
  end loop;
end
$$ language plpgsql;

但实际的结果就是函数无法被创建，错误的原因是：res要么是一个record变量，要么是一个标量的列表。但在上面我们是凭空出现一个res变量。

此时你会想到什么？没错，我们需要声明res的类型，把它声明为一个record不就可以了吗？问题是record到底是个啥，这个东西似乎是PostgreSQL提供给我们的概念，文档太长我懒得看到底在哪定义的了，直接问AI：

在 PostgreSQL 的 PL/pgSQL 中，record 类型是一种特殊的数据类型，它可以用来引用行，但不绑定到特定的表结构。record 类型可以存储任何行结构，使其成为一种非常灵活的数据类型。以下是如何声明和使用 record 类型的变量：

你可以使用 record 关键字来声明一个 record 类型的变量，如下所示：

DECLARE  
    v_row record;

加上变量的声明：

create or replace function say_hello_to_principal() returns void as $$
declare
	res record;
begin
	for res in select id,name from employee loop
  	raise notice '% Say Hello to 校长', res.name;
  end loop;
end
$$ language plpgsql;

创建成功了，接下来就让我们来看看执行该函数会得到什么结果吧：

testdb=# select say_hello_to_principal();
NOTICE:  student0         Say Hello to 校长
NOTICE:  student1         Say Hello to 校长
NOTICE:  student2         Say Hello to 校长
NOTICE:  student3         Say Hello to 校长
NOTICE:  student4         Say Hello to 校长
 say_hello_to_principal
------------------------

(1 row)

我们可以看到，调用了该函数之后，每个学生都向校长问好了，这样我们遍历表结构的目的也就达成了。接下来无非就是更复杂的表结构的遍历，但这些都是增加一些字段以及数据量可能会更加大的问题，遍历的本质方法是没有变的，因此就不再继续深究。

在函数中，如何将变量作为参数去执行SELECT UPDATE INSERT DELETE这四种操作

这里我们以INSERT为例来举例吧，实际上在我们会遍历表之后其他的都不是什么大问题。无非就是利用变量来传递参数，然后在函数的不同位置调用四个SQL操作。

我们假设校长想要知道哪个学生没有向校长问好，那么我们就需要将学生的问好记录下来。创建一个greeting表：

create table greeting
(
	id int primary key,
	greet text not null,
	constraint fk_greeting_ref_employee foreign key(id) references employee(id)
);

然后就可以在学生问候的同时将学生的问候记录下来：

create or replace function say_hello_to_principal() returns void as $$
declare
	res record;
begin
	for res in select id,name from employee loop
  	raise notice '% Say Hello to 校长', res.name;
		insert into greeting(id,greet) values(res.id, concat(res.name, ' Say Hello to 校长'));
  end loop;
end
$$ language plpgsql;

执行：

select say_hello_to_principal();

结果为：

testdb=# select * from greeting;
 id |               greet
----+------------------------------------
  0 | student0         Say Hello to 校长
  1 | student1         Say Hello to 校长
  2 | student2         Say Hello to 校长
  3 | student3         Say Hello to 校长
  4 | student4         Say Hello to 校长
(5 rows)

OK，到了这里，我们基本可以宣告自己已经学会了PL/pgSQL的基本用法了，接下来就是按照自己要干的事情，编写适合自己业务的函数然后调用即可。具体该如何做就是你发挥自己创造力的时候了。也就是所谓的师傅领入门，修行靠个人阶段了。

在数据库中使用函数可能存在的问题

那么在实际上的开发过程中，如果我们使用了数据库中过程化的SQL，那么你将来如果更换数据库就会有点困难，因为这些过程化的SQL在不同数据库通常是不兼容的。

为了较少更换数据库导致的迁移成本，我们最好尽量少用过程化的SQL。但是如果业务恰好性能要求高，恰好需要提高数据库的访问效率，那么又要求我们使用过程化的SQL。

如果出现了上述问题，不管使用或者不使用都涉及到成本：

  • 数据库的迁移成本期望 = 数据库迁移成本 * 迁移概率。使用过程化的SQL将提高数据库的迁移成本。
  • 不使用过程化的SQL解决性能问题的成本。

对比成本的大小来决定是否使用，而非因为未来可能会出现的问题而焦虑当下这个解决方案的不完美。从概率论角度来考虑，如果迁移成本期望 << 不使用过程化SQL解决性能问题的成本，那么选择使用过程化的SQL我认为是一件理所应当的事情。当然，下达这样的判断需要有准确判断迁移成本和迁移概率的能力，这考验的就是项目的管理者的经验和能力了。


http://www.kler.cn/a/447207.html

相关文章：

  • XILINX平台LINUX下高速ADC08060驱动
  • UDP系统控制器_音量控制、电脑关机、文件打开、PPT演示、任务栏自动隐藏
  • uniApp使用腾讯地图提示未添加maps模块
  • 方正畅享全媒体采编系统reportCenter.do接口SQL注入漏洞复现 [附POC]
  • 模具生产过程中的标签使用流程图
  • java集合基础
  • Android Studio AI助手---Gemini
  • 前端处理流式数据(SSE服务)
  • 单北斗+鸿蒙系统+国产芯片，遨游防爆手机自主可控“三保险”
  • 基于预测反馈的情感分析情境学习
  • 国内网络在Ubuntu 22.04中在线安装Ollama并配置Open-WebuiDify
  • 【论文阅读】Computing the Testing Error without a Testing Set
  • MySQL 中的 MVCC：实现高效并发控制
  • 重拾设计模式--观察者模式
  • R语言函数简介
  • [SZ901]JTAG高速下载设置（53Mhz）
  • C# 6.0 连接elasticsearch数据库
  • 【进阶编程】MVVM框架的每层对应那些业务
  • 前端（Vue）tagsView（子标签页视图切换） 原理及通用解决方案
  • ubuntu 卸载 MySQL
  • EJB（Enterprise JavaBean）和JavaBean
  • Postbot使用教程
  • Springboot提供外部接口和访问外部接口的简单例子
  • qwt 之 QwtPlotPicker
  • Docker_常用命令详解
  • 嵌入式驱动开发详解19（regmap驱动架构）