PostgreSQL中使用PL/pgSQL开发函数

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

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

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

如何定义函数

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

函数名；
传入参数；
返回值；
函数体；

在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 $$
beginreturn 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 | student01 | student12 | student23 | student34 | 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 loopraise notice '% Say Hello to 校长', res.name;
end loop;

我们将其写入函数：

create or replace function say_hello_to_principal() returns void as $$
beginfor res in select id,name from employee loopraise 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 $$
declareres record;
beginfor res in select id,name from employee loopraise 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 $$
declareres record;
beginfor res in select id,name from employee loopraise 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我认为是一件理所应当的事情。当然，下达这样的判断需要有准确判断迁移成本和迁移概率的能力，这考验的就是项目的管理者的经验和能力了。