postgresql之自定义函数

要在 PostgreSQL 定义一个新的用户自定义函数，需要使用 CREATE FUNCTION 语句，如下所示：

1
2
3
4
5
6

CREATE FUNCTION function_name(p1 type, p2 type)
 RETURNS type AS
BEGIN
 
END;
LANGUAGE language_name;

来看下 CREATE FUNCTION 语句的细节：

• 首先，在 CREATE FUNCTION 语句后面给定函数的名称
• 然后，在函数名称后面的圆括号内放置逗号分隔的参数列表
• 接下来，在 RETURNS 关键字之后指定函数的返回类型
• 之后，将代码放在 BEGIN 和 END 块内。该函数始终以分号（;）结尾，跟END关键字之后
• 最后，说明函数的过程语言。比如，使用 plpgsql 表示 PL/pgSQL

众所周知，函数是有参数的。那么，PostgreSQL 的函数参数又是怎样的呢？

以一个名为 get_sum() 的简单函数开始：

1
2
3
4
5
6
7
8
9

CREATE OR REPLACE FUNCTION get_sum(
 a NUMERIC, 
 b NUMERIC) 
RETURNS NUMERIC AS $$
BEGIN
 RETURN a + b;
END; $$

LANGUAGE plpgsql;

get_sum() 接受 a 和 b 两个参数，并返回一个 NUMERIC 类型的值。这两个参数的数据类型也都是 NUMERIC。默认情况下，PostgreSQL 任何参数都是 IN 参数。你可以将 IN 参数传递给函数，但无法从返回结果里再获取到。

OUT 参数是函数参数列表的一部分，您可以将其作为结果的一部分返回。使用 OUT 关键字定义 OUT 参数：

1
2
3
4
5
6
7
8
9
10
11
12
13

CREATE OR REPLACE FUNCTION hi_lo(
 a NUMERIC, 
 b NUMERIC,
 c NUMERIC, 
        OUT hi NUMERIC,
 OUT lo NUMERIC)
AS $$
BEGIN
 hi := GREATEST(a,b,c);
 lo := LEAST(a,b,c);
END; $$

LANGUAGE plpgsql;

hi_lo() 接受 5 个参数：

• IN 参数：a, b, c
• OUT 参数：hi（最大数） 和 lo（最小数）

函数内部，我们使用 GREATEST 和 LEAST 两个内置函数分别计算三个 IN 参数中的最大数和最小数。由于我们使用了 OUT 参数，所以不需要 RETURN 语句。OUT 参数经常用于一个函数需要返回多个值，而又不需要自定义数据类型。

INOUT 参数是 IN 和 OUT 参数的组合。这意味着调用者可以将值传递给函数，函数然后改变参数并且将该值作为结果的一部分传递回去。

下面的 square 函数接受一个数字并返回该数字的平方：

1
2
3
4
5
6
7

CREATE OR REPLACE FUNCTION square(
 INOUT a NUMERIC)
AS $$
BEGIN
 a := a * a;
END; $$
LANGUAGE plpgsql;

PostgreSQL 函数可以接受可变数量的参数，其中一个条件是所有参数具有相同的数据类型。参数作为数组传递给函数。 看下面的例子：

1
2
3
4
5
6
7
8
9
10
11
12
13
14

CREATE OR REPLACE FUNCTION sum_avg(
 VARIADIC list NUMERIC[],
 OUT total NUMERIC, 
        OUT average NUMERIC)
AS $$
BEGIN
   SELECT INTO total SUM(list[i])
   FROM generate_subscripts(list, 1) g(i);

   SELECT INTO average AVG(list[i])
   FROM generate_subscripts(list, 1) g(i);

END; $$
LANGUAGE plpgsql;

与 Java 等编程语言相同，PostgreSQL 允许多个函数具有相同的名称，只要参数不同即可。如果多个函数具有相同的名称，那么我们说这些函数是重载的。当一个函数被调用时，PostgreSQL 根据输入参数调用确切的函数。

来看一下 get_rental_duration() 函数：

1
2
3
4
5
6
7
8
9
10
11
12
13
14

CREATE OR REPLACE FUNCTION get_rental_duration(p_customer_id INTEGER)
 RETURNS INTEGER AS $$

DECLARE 
 rental_duration INTEGER; 
BEGIN
 -- get the rate based on film_id
 SELECT INTO rental_duration SUM( EXTRACT( DAY FROM return_date - rental_date)) 
    FROM rental 
 WHERE customer_id=p_customer_id;

 RETURN rental_duration;
END; $$
LANGUAGE plpgsql;

get_rental_duration() 函数接受 p_customer_id 参数。它返回特定客户租用 DVD 的持续时间（以天计）。

假设，我们想知道从某个特定的日期到现在的客户的租赁时间。我们可以给 get_retal_duration() 函数添加一个 p_rom_date 参数，或者重新定义一个有两个参数的同名函数，如下所示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

CREATE OR REPLACE FUNCTION get_rental_duration(p_customer_id INTEGER, p_from_date DATE)
 RETURNS INTEGER AS $$
DECLARE 
 rental_duration integer;
BEGIN
 -- get the rental duration based on customer_id and rental date
 SELECT INTO rental_duration
             SUM( EXTRACT( DAY FROM return_date - rental_date)) 
 FROM rental 
 WHERE customer_id= p_customer_id AND 
   rental_date >= p_from_date;

 RETURN rental_duration;
END; $$
LANGUAGE plpgsql;

这个函数和之前的函数同名，但它有两个参数。我们称之为 get_rental_duration(integer) 函数被 get_rental_duration(integer,date) 函数重载了。

在 get_rental_duration(integer,date) 函数中，如果我们想给第二个参数设置默认值，如下例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

CREATE OR REPLACE FUNCTION get_rental_duration(
 p_customer_id INTEGER, 
 p_from_date DATE DEFAULT '2005-01-01'
 )
 RETURNS INTEGER AS $$
DECLARE 
 rental_duration integer;
BEGIN
 -- get the rental duration based on customer_id and rental date
 SELECT INTO rental_duration
             SUM( EXTRACT( DAY FROM return_date - rental_date)) 
 FROM rental 
 WHERE customer_id= p_customer_id AND 
   rental_date >= p_from_date;

 RETURN rental_duration;
END; $$
LANGUAGE plpgsql;

这意味着，如果我们省略了 p_from_date 参数，PostgreSQL 将使用 2005-01-01 作为默认值。

以下函数使用 LIKE 运算符返回标题匹配特定模式的所有电影：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

CREATE OR REPLACE FUNCTION get_film (p_pattern VARCHAR) 
 RETURNS TABLE (
 film_title VARCHAR,
 film_release_year INT
) 
AS $$
BEGIN
 RETURN QUERY SELECT
 title,
 cast( release_year as integer)
 FROM
 film
 WHERE
 title LIKE p_pattern ;
END; $$ 

LANGUAGE 'plpgsql';

get_film(varchar) 函数接受一个想匹配的影片标题的模式 p_pattern 参数。

要从函数里返回一张表，需要使用 RETURNS TABLE 语法并指定该表的字段。字段之间使用逗号分隔。

在函数中，我们返回一个 SELECT 语句结果的查询。注意，SELECT 语句里的字段必须和想要返回的表的字段相同。由于 film 表里的 release_year 字段的数据类型并不是 INT，所以我们使用类型转换将其转成整数。

在实践中，我们可能经常需要在将每行添加到函数的结果集之前处理它们。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

CREATE OR REPLACE FUNCTION get_film (p_pattern VARCHAR,p_year INT) 
 RETURNS TABLE (
 film_title VARCHAR,
 film_release_year INT
) 
AS $$
DECLARE 
    var_r record;
BEGIN
   FOR var_r IN(SELECT title, release_year 
                       FROM film WHERE title LIKE p_pattern AND 
                        release_year = p_year)  
     LOOP
              film_title := upper(var_r.title) ; 
       film_release_year := var_r.release_year;
              RETURN NEXT;
            END LOOP;
END; $$ 

LANGUAGE 'plpgsql';

我们创建了同名的接受两个参数的 get_film(varchar,int) 函数。

• 第一个参数是我们用来搜索电影的 p_pattern，如果标题与这个模式匹配的话。我们使用 LIKE 来进行搜索
• 第二个参数是电影的发行年份

顺便说一下，这是函数重载。

一个 PostgreSQL 函数由块（block）进行组织。下面举例说明 PostgreSQL 中一个完整块的语法：

1
2
3
4
5
6
7

[ <<标签>> ]
[ DECLARE
    声明 ]
BEGIN
    主体;
 ...
END [ 标签 ];

让我们来更详细地讨论块结构：

• 每个块有两个部分，称为声明和主体。声明部分是可选的，而主体部分是必需的。该块在END关键字后以分号（;）结尾。
• 一个块在开始和结束时可以有可选的标签。开始和结束时的标签必须相同。如果要在 EXIT 语句中使用块，或者想要限定块中声明的变量的名称，则使用块标签。
• 声明部分是声明主体部分中使用的所有变量的地方。声明部分中的每个语句都以分号（;）结尾。
• 主体部分是放置块的逻辑的地方。它包含任何有效的语句。 主体部分中的每个语句也以分号（;）结尾。

1
2
3
4
5
6
7
8
9

DO $$ 
<<first_block>>
DECLARE
  counter integer := 0;
BEGIN 
   counter := counter + 1;
   RAISE NOTICE 'The current value of counter is %', counter;
END first_block $$;
NOTICE:  The current value of counter is 1

提示，DO 语句不属于这个块。它用于执行一个匿名块。PostgreSQL 9.0 开始引入 DO 语句。

在声明部分，我们声明了一个名为 counter 的变量，并将其值设置为0。在主体部分，我们将计数器增加1，并使用 RAISE NOTICE 语句输出其值。first_block 标签仅用于演示目的。 它在这个例子中什么也没做。

你可以将一个块放在另一个块的主体部分。嵌套在另一个块内部的块被称为子块（subblock）或内部块（inner block）。包含子块的块称为外部块（outer block）。

经常使用子块来对语句进行分组，以便可以将较大的块分割成更小或更多的逻辑子块。子块中的变量可以具有和外部块中的相同名称的变量，即使这不是一个好的做法。

当您在子块中定义一个与外部块名称相同的变量时，外部块中的变量将隐藏在子块中。如果要访问外部块中的变量，可以使用块标签来限定其名称; 看下面的例子：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

DO $$ 
<<outer_block>>
DECLARE
  counter integer := 0;
BEGIN 
   counter := counter + 1;
   RAISE NOTICE 'The current value of counter is %', counter;

   DECLARE 
       counter integer := 0;
   BEGIN 
       counter := counter + 10;
       RAISE NOTICE 'The current value of counter in the subblock is %', counter;
       RAISE NOTICE 'The current value of counter in the outer block is %', outer_block.counter;
   END;

   RAISE NOTICE 'The current value of counter in the outer block is %', counter;

END outer_block $$;

要报告一条消息，可以使用下面的 RAISE 语句：

1

RAISE level format;

让我们看看 RAISE 语句各部分的细节。

跟在 RAISE 后面的是指定错误严重性的级别选项。PostgreSQL 有以下错误级别：

• DEBUG
• LOG
• NOTICE
• INFO
• WARNING
• EXCEPTION

如果没有指定 level，默认，RAISE 语句将使用 EXCEPTION 级别来抛出错误并停止当前事务。

format 用来对消息进行格式化。它使用百分号（%）来给下一个参数占位。占位符的数量必须与参数的数量相匹配，否则 PostgreSQL 将报告以下错误消息：

1

[Err] ERROR:  too many parameters specified for RAISE

以下示例说明了当前时间下，报告不同消息的 RAISE 语句。

1
2
3
4
5
6
7
8

DO $$ 
BEGIN 
  RAISE INFO 'information message %', now() ;
  RAISE LOG 'log message %', now();
  RAISE DEBUG 'debug message %', now();
  RAISE WARNING 'warning message %', now();
  RAISE NOTICE 'notice message %', now();
END $$;

要抛出错误，需要在 RAISE 语句后面使用 EXCEPTION 级别。PostgreSQL 默认就是使用该级别。

除了抛出错误之外，还可以使用以下子句和 RAISE 语句添加更多详细信息：

1

USING option = expression

option 可以是：

• MESSAGE：设置错误消息的内容
• HINT：提供提示信息，以便更容易发现错误的根本原因
• DETAIL：提供有关错误的详细信息
• ERRCODE：来识别错误代码，可以通过条件名或直接使用五个字符的 SQLSTATE 代码来识别

expression 是一个字符串的值表达式。

下面的示例抛出 Email 重复的错误消息：

1
2
3
4
5
6
7
8
9
10

DO $$ 
DECLARE
 email varchar(255) := '[email protected]';
BEGIN 
  -- check email for duplicate
  -- ...
  -- report duplicate email
  RAISE EXCEPTION '重复的 Email: %', email 
 USING HINT = '请再次检查该 Email。';
END $$;

有时候，PostgreSQL 函数太大，使得检测错误更加困难。因此，为了方便起见，PostgreSQL 提供了 ASSERT 语句，用于将调试检查添加到 PostgreSQL 函数中。

以下说明了 ASSERT 语句的语法：

1

ASSERT condition [, message];

condition 是个布尔表达式，如果它为 TRUE，ASSERT 语句什么都不做。如果它为 FALSE 或 NULL，将抛出 ASSERT_FAILURE。

如果没有提供 message，PL/pgSQL 将使用 "assertion failed" 作为默认消息。

1
2
3
4
5
6
7

DO $$ 
DECLARE 
 counter integer := -1;
BEGIN 
   ASSERT counter < 0 
   MESSAGE 'Expect counter starts with 0';
END $$;

非常重要的提示：ASSERT 语句仅用于调试。

变量是一个有意义的内存位置的名称。一个变量保存一个可以通过块或函数改变的值。变量总是与特定的数据类型相关联。

在使用变量之前，必须在 PostgreSQL 的声明部分声明它。以下说明声明变量的语法。

1

variable_name data_type [:= expression]

首先，指定了变量的名字。为变量分配一个有意义的名字是一个好习惯。比如，使用 index 或 counter 来代替 i 这种命名。

其次，将一个特定的数据类型与变量相关联。它可以是任何有效的 PostgreSQL 数据类型，如 INT，NUMERIC，VARCHAR，CHAR 等。

第三，你可以给变量分配一个默认值。这是可选的。如果不为变量设置默认值，则变量的值将初始化为 NULL值。

PostgreSQL 能够定义一个引用表中字段的数据类型，甚至是另一个变量数据类型的变量。如下所示：

1
2

variable_name table_name.column_name%TYPE;
variable_name variable%TYPE;

比如，可以定义一个引用 city 表的 name 字段的数据类型的变量 city_name：

1

city_name city.name%TYPE := 'San Francisco';

通过拷贝数据类型功能，可以得到以下益处：

• 首先，不需要关心字段的数据类型。
• 其次，当字段的数据类型发生变化时，不需要更改函数中的变量声明以适应新的更改
• 最后，您可以将变量的类型引用到函数参数的数据类型中，以创建多态函数，因为内部变量的类型可以从一个调用更改为下一个

PostgreSQL 允许你为任何变量定义一个别名，如下所示：

1

new_name ALIAS FOR old_name;

别名主要用于触发器过程，为具有预定名称的变量分配更多有意义的名称。比如：NEW 或 OLD。

要在 PostgreSQL 中声明一个常量，可以使用以下语法：

1

constant_name CONSTANT data_type := expression;

来看一下语法每部分的细节：

• 首先，指定了常量的名字。按惯例，常量的名应该是全部大写，比如 VAT, DISCOUNT等
• 接下来，在 CONSTANT 关键字后指定数据类型
• 最后，必须给常量指定初始值

IF 语句用于有条件地执行命令。PostgreSQL 提供了三种形式的 IF 语句。

• IF THEN
• IF THEN ELSE
• IF THEN ELSIF THEN ELSE

与 CASE 表达式评估为一个值不同，CASE 语句根据条件执行语句。

1
2
3
4
5
6
7

CASE search-expression
   WHEN expression_1 [, expression_2, ...] THEN
      when-statements
  [ ... ]
  [ELSE
      else-statements ]
END CASE;

search-expression 是一个表达式，将在每个 WHEN 分支里使用相等（=）比较时进行计算。如果找到了匹配的项，则对应的 when-statements 会执行，而其它的判断子语句不会再执行。

ELSE 分支里的 else-statements 将在所有的 WHEN 分支都不匹配时执行。这个 ELSE 分支是可选的。如果没有指定 ELSE 分支，并且所有的 WHEN 都没有匹配成功，则会抛出 CASE_NOT_FOUND 异常。

1
2
3
4
5
6
7
8
9

CASE
    WHEN boolean-expression-1 THEN
      statements
  [ WHEN boolean-expression-2 THEN
      statements
    ... ]
  [ ELSE
      statements ]
END CASE;

搜索的 CASE 语句根据每个 WHEN 子句中布尔表达式的结果执行语句。PostgreSQL 按从上到下的顺序，依次判断 WHEN 表达式的结果，直到找到结果为 TRUE 的 WHEN。如果没有找到结果为 TRUE 子句，则执行 ELSE后的语句。这个 ELSE 分支是可选的。如果没有指定 ELSE 分支，并且所有的 WHEN 都没有匹配成功，则会抛出 CASE_NOT_FOUND 异常。

PostgreSQL 提供了3种循环语句：LOOP，WHILE 和 FOR 循环。

有时，您需要重复执行一个语句块，直到条件成立。要做到这一点，可以使用 PL/pgSQL 的 LOOP 语句。其语法如下：

1
2
3
4
5

<<label>>
LOOP
   Statements;
   EXIT [<<label>>] WHEN condition;
END LOOP;

LOOP 语句也被称为无条件循环语句，因为它会执行语句，直到 EXIT 语句中的条件计算结果为 true。

LOOP 语句可以嵌套。这种情况下，需要使用标签来指定 EXIT 跳出哪个 LOOP。

WHILE 循环语句执行一个语句块，直到条件计算结果为 false。在WHILE循环语句中，PostgreSQL 在执行语句块之前评估条件。如果条件为真，语句块将被执行，直到它被评估为 false。

WHILE 循环语句的语法如下：

1
2
3
4

[ <<label>> ]
WHILE condition LOOP
   statements;
END LOOP;

FOR 循环语句是 PostgreSQL 中最复杂的循环语句。我们将详细讨论 FOR 循环语句的每种形式。

1
2
3
4

[ <<label>> ]
FOR loop_counter IN [ REVERSE ] from.. to [ BY expression ] LOOP
    statements
END LOOP [ label ];

首先，PostgreSQL 创建一个只存在于 FOR 循环内部的 loop_counter 变量。默认情况下，循环计数器在每次迭代之后被添加，如果使用 REVERSE 关键字，PostgreSQL 将会减去循环计数器。

下一步，from 和 to 指定了范围的起始和结束位置。PostgreSQL 会在进入循环之前执行这个表达式。

第三，BY 子句指定了每次迭代的步长。如果省略了此子句，将使用默认步行 1。PostgreSQL 在循环时也执行这个表达式。

有时候，需要构造一个动态查询，并使用 FOR 循环来迭代查询结果。要实现这个功能，可以使用以下语法：

1
2
3
4
5

[ <<label>> ]
FOR row IN EXECUTE string_expression [ USING query_param [, ... ] ] 
LOOP
    statements
END LOOP [ label ];

游标（CURSOR）允许我们封装一个查询并每次处理一行。当我们想要将一个大的结果集分成多个部分并分别处理每个部分时，我们使用游标。如果我们一次处理它，我们可能会有内存溢出错误。

另外，我们可以开发一个返回游标引用的函数。这是从函数中返回一个大的结果集的有效方法。函数的调用者可以根据游标引用来处理结果集。

1. 首先，定义一个游标
2. 下一步，打开这个游标
3. 然后，从结果集中获取行到目标中。
4. 之后，检查是否还有更多的行要取。 如果是，则转到步骤3，否则转到步骤5。
5. 最后，关闭游标。

要访问一个游标，需要先在块的声明部分来声明一个游标变量。PostgreSQL 提供了一个特殊的 REFCURSOR类型来定义游标变量。

1
2

DECLARE 
   my_cursor REFCURSOR;

另一种方法是，声明绑定到查询的游标，使用以下语法：

1

cursor_name [ [NO] SCROLL ] CURSOR [( name datatype, name data type, ...)] FOR query;

首先指定了游标的名字。

下一步，可以使用 SCROLL 指定是否可以向后滚动游标。如果使用 NO SCROLL，则游标不能向后滚动。

然后，是 CURSOR 关键字。并在其后使用逗号分隔的参数列表来定义查询的参数。游标打开时，这些参数将被替换为具体的值。

之后，在 FOR 关键字后指定查询。可以在这里使用任何合法的 SELECT 语句。

下面是一个例子：

1
2
3

DECLARE
    cur_films  CURSOR FOR SELECT * FROM film;
    cur_films2 CURSOR (year integer) FOR SELECT * FROM film WHERE release_year = year;

在使用游标进行查询之前，需要将其打开。PostgreSQL 提供了打开未绑定和绑定游标的语法。

使用下面的语法打开未绑定游标：

1

OPEN unbound_cursor_variable [ [ NO ] SCROLL ] FOR query;

由于在定义未绑定游标时，我们没有将该游标和任何查询进行绑定，所以在打开该游标时，需要指定查询。

1

OPEN my_cursor FOR SELECT * FROM city WHERE counter = p_country;

PostgreSQL 允许我们打开一个游标并将其绑定到一个动态查询上：

1
2

OPEN unbound_cursor_variable[ [ NO ] SCROLL ] 
FOR EXECUTE query_string [USING expression [, ... ] ];

举例：

1
2
3

query := 'SELECT * FROM city ORDER BY $1';

OPEN cur_city FOR EXECUTE query USING sort_field;

由于我们在定义游标时，已将其绑定到指定的查询上，所以在打开游标时，只需要传递查询所需要的参数：

1

OPEN cursor_variable[ (name:=value,name:=value,...)];

打开游标后，我们可以使用 FETCH，MOVE，UPDATE 或 DELETE 语句来操作它。

FETCH 语句用于从游标中获取下一行，并将结果赋给 target_variable，成为一条记录、一个行变量或以逗号分隔的变量列表。如果没有更多的行存在了，target_variable 将被设置为 NULL。

如果没有指定方向，FETCH 将获取下一行记录。以下是可以使用的方向：

• NEXT：下一行
• LAST：最后一行
• PRIOR
• FIRST：第一行
• ABSOLUTE count
• RELATIVE count
• FORWARD：前进
• BACKWARD：后退

注意，FORWARD 和 BACKWARD 只能用于使用 SCROLL 选项定义的游标。

1
2

FETCH cur_films INTO row_film;
FETCH LAST FROM row_film INTO title, release_year;

一旦光标被定位，可以使用 DELETE WHERE CURRENT OF 或 UPDATE WHERE CURRENT OF 语句来删除或修改行。

1
2
3
4
5

UPDATE table_name 
SET column = value, ... 
WHERE CURRENT OF cursor_variable;
DELETE FROM table_name 
WHERE CURRENT OF cursor_variable;

使用 CLOSE 语句来关闭游标，如下所示：

1

CLOSE cursor_variable;