likes
comments
collection
share

PostgreSQL技术问答38 - Function 函数(上)本文是在Postgres中，定义和使用函数的相关内容的

作者站长头像
站长
· 阅读数 88

本文是《PostgreSQL技术问答》系列文章中的一篇。关于这个系列的由来，可以参阅开篇文章：

文章的编号只是一个标识，在系列中没有明确的逻辑顺序和意义。读者进行阅读时，不用太关注这个方面。

本文主要讨论的内容是在Postgres中，另一个比较核心和重要的功能： Function - 函数。

由于这部分内容比较多，所以笔者分为两个部分进行讨论。本文作为上半部分，主要探讨函数的基本概念，定义和基本控制结构，基本的调用方式等。下半部分讨论一些相对比较扩展的主题包括结果集遍历、错误处理、事务支持、日志等方面的内容。

什么是Function函数

Function本身就是函数的意思，但在关系型数据库中，它们应该是特指数据库应用开发者可以使用特定的脚本化语言开放的代码模块，并且存储和部署在数据库系统，可以使用SQL语句调用和执行的程序，这个还是和一般编程语言和平台中，标准的函数定义有一些差别的。在比较古老的数据库系统中，有时也被称为“存储过程(Store Procedure)”，笔者认为都是相同的东西，名字有差异而已。本文中就统一称为Function函数。

函数这一技术的出现，其根本目的，就是丰富和扩展数据库系统的功能。

我们都很熟悉，经典的关系型数据库系统所使用的SQL语言，是一种自然语言形式的指令性语言系统，它通过直接描述操作和任务目的，来给数据库系统下达指令。而这种方式，要表达一种程序化结构处理过程的时候，就显得不是特别合适了。所以，为了弥补原生SQL语言本身在执行流程结构和控制方面的不足，数据库系统模仿一般编程语言中的标准程序结构，提供了函数模块和执行的机制，来提供更灵活和丰富的数据处理和计算功能。

和一般的编程语言中函数的概念和定义类似，在数据库系统中，函数也是一个封闭的代码和执行单元，也可以用于软件功能的封装和模块化。但显然，一般的数据库系统，并不是一个完整的流程化代码执行环境，所以，通过编写如函数这种相对模式化的功能模块，将所有的数据和信息处理，都抽象到输入-处理-输出等三个简单的组成部分，来对系统的功能进行扩展，就是一种比较合适的方式了。

除了函数Function之外，在Postgres中，还有一种代码模块对象，名为Procedue(过程)。从功能性的角度而言，函数和过程没有本质区别，而且在内部使用的语言模块都是一样的。它们的主要差别在于语义和调用方面的差异。函数比较明确的表明其有一个功能性的返回值，通常作为表达式的形式，从select进行调用；而过程就是简单执行一段代码，其输出也体现在执行过程造成的影响当中，可以没有明确的返回值，也可以选择声明从参数返回；过程需要显示的使用call语句进行调用。

由于以上的特性，而且除了一些业务方面的需求之外，我们通常不会编写大量的过程代码，一般都是基于程序抽象扩展的需求，来编写函数，所以本文的讨论以函数为主，并且没有明确的区分函数和过程。基本认为这两者在技术方面是相同的东西，都是为了用来扩展流程化的数据操作而出现的。

在Postgres中，要实现函数和过程模块，在其中使用流程化的处理方式，就需要除了SQL之外，提供类似一般编程语言的相关功能特性，这一般是通过过程语言来实现(Procedural Language，PL)。一般的PL在数据库中的实现需要在提供通用化的编程功能的基础上，还需要很好的结合SQL命令和数据库操作的执行，比如可以将数据库查询结果赋予过程变量，或者在过程中调用和处理SQL查询和其结果等等，并不是简单的在数据库环境中，实现一种标准的编程语言。所以，可能需要对PL的实现和支持有正确的预期，不要以为支持JS或者Java就认为可以无限扩展了，在真实的场景中，其实是受到很多条件限制的。

在PG中，函数和过程的基本结构是怎么样的

在Postgres中，函数和过程最终的形态，都是数据库对象(以其定义代码的形式保存)，从属于于某个数据库架构(命名空间)。为了更直观的理解，先来看看两个简单的定义创建示例的SQL代码：

-- 函数 
CREATE FUNCTION 
x_intRange_end(irange INT)
RETURNS INT AS $$
DECLARE 
   iloop INT;
   iend  INT := 0;
BEGIN
  IF (irange <= 0) THEN 
	RETURN 0;
  END IF;
  
  iloop = irange; 
  WHILE iloop > 0 LOOP
    iend = iend+1;	
	iloop = iloop >> 1;
  END LOOP;
  
  return iend;
END;
$$ LANGUAGE plpgsql;

-- 过程
CREATE OR REPLACE PROCEDURE insert_adata(aa integer, bb integer)
LANGUAGE SQL
AS $$
  INSERT INTO adata(ivalues) VALUES (Array[aa,bb]);
  INSERT INTO adata(ivalues) VALUES (Array[bb,aa]);
$$;

上面的代码，用于在数据库中，创建函数对象。从这段代码中，我们可以看到Postgres中，函数的标准结构和定义方式，要点如下：

  • Create Function 关键字声明函数创建，类似的Create Procedure创建过程
  • 需要指定函数/过程的名称，非关键字，和数据库其他对象不重复
  • 函数和过程的输入参数，可以是命名式的，也可以是占位符
  • 可以支持多个参数(使用逗号分隔)
  • Postgres对函数的标识和调用检查非常严格，执行前会检查参数的数量、次序和类型
  • 相同名称但参数数量或者类型不同的函数，PG认为是不同的函数
  • 在函数中，需要声明RETURNS+参数类型，标识返回值的类型
  • AS $$ 表示函数内容起始
  • DECLARE，声明函数范围内可以使用的变量，包括类型和初始数值
  • BEGIN 关键字表明函数实际内容开始，配套 END 关键字
  • LANGUAGE 关键字声明函数使用的语言
  • Postgres支持多种函数语言，此处用的是默认的plpgsql和SQL

和一般的Postgres数据库对象一样，它使用一个SQL对象定义语句和执行来创建函数对象，这个对象在创建之后，会存储在数据库架构当中。可以在后续调用和执行。同理，函数对象的删除，也可以使用 drop function/procedure 这种语句来进行操作。

这就是一个Postgres函数和过程定义的一般结构，它们都有一定的规范，在使用中需要严格的遵循。关于这些对象的具体内容，就是具体相关代码块的定义和规则，在本文后面的内容进行讨论。

什么是plpgsql

在前面的代码中，我们可以发现，函数的定义可以选择和使用plpgsql或者SQL等语言。SQL语言很好理解，就是数据库系统原生的语言，那么什么是plpgsql呢？

plpgsql其实就是PL/pgSQL（PostgreSQL的PL/pgSQL）的简称。它是PostgreSQL数据库(默认)支持的一种过程语言（Procedural Language）。plpgsql也是是一种面向过程的脚本化编程语言，并允许在PostgreSQL中用于定义函数、存储过程和触发器。

作为PostgreSQL官方默认的过程语言，plpgsql提供了丰富的功能和特性，可以用于编写复杂的业务逻辑。它支持和提高如变量、控制结构(条件、分支、循环等)、异常处理、错误处理等等基本和高级特性。

和普通的编程和脚本语言不同，plpgsql还需要和SQL语言和数据库操作进行结合，例如可以将数据查询结果赋予某个变量，可以在数据结果集中进行遍历，或者执行SQL语句等等，笔者认为这才是一个数据库过程语言和普通编程语言相比最大的差异和价值。

作为一个经常使用Java和JavaScript语言的开发者，可能觉得plpgsql的语法，确实是比较繁琐。它们都好像来自古早的数据库过程性语言系统，和SQL语言的设计逻辑类似，强调使用字符串关键字指令作为控制机制，而没有很好的利用符号系统。这些特点，我们后面会在相关的结构和特性分析中可以感受到，这确实需要一个适应和熟悉的过程。

除了pgsql之外，postgres还支持使用多种过程语言来创建函数和过程，包括perl、python、tcl、R、java、V8(js)、ruby、lua、sh等等，当然大多数语言都是作为扩展来进行支持的。此外，在函数和存储过程中，也可以直接使用sql进行数据和信息的处理，这时候，函数/过程基本上就是参数化SQL的简单组合了，更像封装好的一个SQL语句块。

关于如何编写函数和过程

本章节中，我们将通过一系列主题，以plpgsql语言为基础，来具体了解在函数/过程编写过程中，我们可能遇到的问题和解决的方式。这些问题在本质上，也是基于任何编程语言，来编写一段程序或者功能模块，都会遇到的问题和一般性的知识框架。

如何使用变量

作为指令性的语言，SQL语言的一个明显的缺陷，就是不能在过程中声明和使用变量，这个特性需要在函数系统和语言中提供，才能实现程序化的处理。

在PG函数中，提供了相对完整和丰富的变量管理和使用的生命周期。并且和SQL语句的各种查询和操作都结合的比较好。这个生命周期包括：

  • 对变量进行声明(declare...)
  • 对变量进行赋值( :=， 但好像 = 也可以 )
  • 基于查询结果对变量进行赋值(select... into...)
  • 在查询和操作中使用变量( 直接使用变量名 )

下面的参考示例代码，展示了变量的整个生命周期，包括生命、查询赋值、修改、查询参考等：


DO $$
DECLARE
    employee_id INT;
    employee_name VARCHAR(100);
    department_id INT;
    department_name VARCHAR(100);
BEGIN
    -- 查询 employees 表中的某个员工，并将结果赋予变量
    SELECT id, name, department_id
    INTO employee_id, employee_name, department_id
    FROM employees
    WHERE name = 'John Doe';

    -- 修改变量
    department_id := 'D_'|| deparment_id;
    
    -- 使用变量进行后续查询
    SELECT name
    INTO department_name
    FROM departments
    WHERE id = department_id;

    
    -- 输出结果
    RAISE NOTICE 'Employee ID: %, Name: %, Department: %',
        employee_id, employee_name, department_name;
END;
$$ LANGUAGE plpgsql;

函数中如何实现条件和分支控制

在普通的应用程序中，就是使用IF和SWITCH等语句结构，来进行流程的分支控制。在plpgsql中，也有类型的结构，但写法稍有不同。

plpgsql有以下几种形式的条件控制方式，来满足不同的应用需求：

-- 简单条件执行
IF boolean-expression THEN
    statements
END IF;

-- 简单分支执行
IF boolean-expression THEN
    statements
ELSE
    statements
END IF;

-- 多重分支执行
IF boolean-expression THEN
    statements
[ ELSIF boolean-expression THEN
    statements
[ ELSIF boolean-expression THEN
    statements
    ...
]
]
[ ELSE
    statements ]
END IF;

这里boolean-expression是条件表达式，返回值是一个布尔值；statments是要执行的(多个)语句。注意这里面THEN和END IF不能省略。

对于分支控制，有两种形式，简单形式和搜索形式。

-- 简单形式
CASE search-expression
    WHEN expression [, expression [ ... ]] THEN
      statements
  [ WHEN expression [, expression [ ... ]] THEN
      statements
    ... ]
  [ ELSE
      statements ]
END CASE;

-- 搜索形式
CASE
    WHEN boolean-expression THEN
      statements
  [ WHEN boolean-expression THEN
      statements
    ... ]
  [ ELSE
     statements ]
END CASE;

简单形式使用一个搜索表达式(如一个字段的内容)，然后检查其是否匹配不同的值或者表达式结果(可以定义多个)，如果匹配，则进行某些操作，或者进行默认操作(都不匹配)。

而搜索形式，就是一次检查结构中每个条件表达式的返回结果，如果为真，则进行相应的操作。如果都不为真，则可选进行默认操作(Else部分)。

如何实现流程的循环控制

在plpgsql语言中，有很多种方式可以进行流程的循环控制操作。这里面的控制关键字包括LOOP, EXIT, CONTINUE, WHILE, FOR, FOREACH等等。

-- 简单循环
LOOP
   statments
   
   EXIT WHEN boolean-expression;
   CONTINUE WHEN boolean-expression;
   -- 
END LOOP;

-- 条件控制循环
[ <<label>> ]
WHILE boolean-expression LOOP
    statements
END LOOP [ label ];

-- FOR循环
FOR i IN 1..10 LOOP
    -- i will take on the values 1,2,3,4,5,6,7,8,9,10 within the loop
END LOOP;

FOR i IN REVERSE 10..1 LOOP
    -- i will take on the values 10,9,8,7,6,5,4,3,2,1 within the loop
END LOOP;

FOR i IN REVERSE 10..1 BY 2 LOOP
    -- i will take on the values 10,8,6,4,2 within the loop
END LOOP;

相关的要点总结如下：

  • 所有的循环，都必须有一个条件检查和跳出机制，否则会显然死循环
  • EXIT 命令关键字(结合条件表达式)可以用于强行跳出
  • CONTINUE 命令关键字(结合条件表达式)可以用于强行进入下一个循环
  • 可以选择给循环体进行命名，并在EXIT中指定使用(也许用于多重循环？)
  • WHILE本质上是将WHEN EXIT写在了结构开头，可以简化和强调
  • FOR ... IN ... LOOP 用于整数循环控制，还可以选择方向和步长，注意其结构
  • FOR还支持记录集和数组遍历，我们会在后面的扩展内容中详细探讨

其他关于编程、开发和调试的特性

对于一个常规的过程化程序而言，这些内容就基本上构成了一个比较完整的程序执行所需要的要素和过程。但在普通的编程系统中，扩展的开发支持功能，还包括日志打印、错误处理等功能和特性，这些内容我们将会在扩展的内容(本文的下半部分)中进行探讨。

如何返回处理结果

我们在Postgres中执行函数和过程，通常有两种期望的结果。一是执行某些数据修改的操作，只需要了解这个操作是否正常的执行；而是希望通过执行一个操作，获得这个操作的结果，进行结果的输出或者用于后续的动作。前者通常通过定义和调用函数来实现，而后者则通过定义函数来实现。在这个意义上，通常函数的定义，需要有一个明确的输出结果。

其实，在Postgres中，我们可以简化这个问题。只保留函数这一种类型，存储过程，可以使用一个处理状态的结果作为返回值，这是虽然在语义上，返回值的意义是不同的，但在使用和业务上，可以是没有区别的，不会影响实际的业务和需求的实现。

我们还是以plpgsql和SQL作为主要示例来进行说明，在plpgsql中，使用return可以返回当前函数的处理结果。返回处理结果非常简单，这里需要注意的事情是，返回的数据，必须和创建函数时，声明的返回数据类型是相同的。实际的返回值包括以下几种：

  • 标量或对象

就是返回一个简单的数值，或者单一的对象。下面是一些简单的示例：


-- 返回计算结果
CREATE FUNCTION add_em(integer, integer) RETURNS integer AS $$
    SELECT $1 + $2;
$$ LANGUAGE SQL;

SELECT add_em(1, 2) AS answer;

 answer
--------
      3


-- 返回固定结果
CREATE FUNCTION tf1 (accountno integer, debit numeric) RETURNS numeric AS $$
    UPDATE bank
        SET balance = balance - debit
        WHERE accountno = tf1.accountno;
    SELECT 1;
$$ LANGUAGE SQL;

-- 返回记录类型 定义数据类型和函数
CREATE TABLE emp (
    name        text,
    salary      numeric,
    age         integer,
    cubicle     point
);

CREATE FUNCTION new_emp() RETURNS emp AS $$
    SELECT ROW('None', 1000.0, 25, '(2,2)')::emp;
$$ LANGUAGE SQL;

-- 调用函数
SELECT new_emp();

         new_emp
--------------------------
 (None,1000.0,25,"(2,2)")
  • 游标

这种操作返回的是一个游标，然后在后续的处理中，可以对这个游标进行操作。

-- 定义函数返回游标
CREATE OR REPLACE FUNCTION get_employees_cursor()
RETURNS refcursor AS $$
DECLARE
    employee_cursor refcursor;
BEGIN
    -- 打开游标
    OPEN employee_cursor FOR
        SELECT id, name, department FROM employees;

    -- 返回游标
    RETURN employee_cursor;
END;
$$ LANGUAGE plpgsql;

-- 调用函数，获得游标，并且使用游标
DO $$
DECLARE
    employee_cursor refcursor;
    employee_record RECORD;
BEGIN
    -- 调用自定义函数并获取游标
    employee_cursor := get_employees_cursor();

    -- 循环遍历游标中的记录
    LOOP
        FETCH employee_cursor INTO employee_record;
        EXIT WHEN NOT FOUND;

        -- 处理每条记录
        RAISE NOTICE 'ID: %, Name: %, Department: %',
            employee_record.id,
            employee_record.name,
            employee_record.department;
    END LOOP;

    -- 关闭游标
    CLOSE employee_cursor;
END;
$$ LANGUAGE plpgsql;

示例中，是在一个SQL语句块中，调用函数，获取并使用游标的场景。在应用中，应当也能够通过相同的方法，获取游标对象并且进行相关的操作，基本概念和逻辑是类似的。关于游标，是另外一个课题，笔者有机会会另行撰文讨论。

  • 结果集

这也是非常常见的情况，就是像一般的查询一样，返回一个查询结果集。下面是一个官方的示例：


CREATE FUNCTION get_available_flightid(date) RETURNS SETOF integer AS
$BODY$
BEGIN
    RETURN QUERY SELECT flightid
                   FROM flight
                  WHERE flightdate >= $1
                    AND flightdate < ($1 + 1);

    -- Since execution is not finished, we can check whether rows were returned
    -- and raise exception if not.
    IF NOT FOUND THEN
        RAISE EXCEPTION 'No flight at %.', $1;
    END IF;

    RETURN;
 END;
$BODY$
LANGUAGE plpgsql;

-- Returns available flights or raises exception if there are no
-- available flights.
SELECT * FROM get_available_flightid(CURRENT_DATE);

这是一个相对规范完整的示例，要点如下：

  • 使用Returns SETOF ... 来声明返回的类型，总是一个结果集
  • 使用Return Query .... 表示返回结果集

如何调用或者执行函数

Postgres中，调用和执行函数，大体上有以下几种方式： select(选择)、call(调用)和执行(execute)。

  • select

select方式，就是将函数作为一个标准的值或者表表达式，放置在SQL查询语句当中。非常简单和直观：

defaultdb=> with D(a,b) as (values (11,15), (4,6)) select a,b, x_rangeint(a,b) irange from D;
 a  | b  | irange 
----+----+--------
 11 | 15 |  31744
  4 |  6 |     56
(2 rows)
  • call

在SQL执行环境中，可以通过call子句，来执行一个过程的调用：

select insert_adata(11,16);
ERROR:  insert_adata(integer, integer) is a procedure
LINE 1: select insert_adata(11,16);
               ^
HINT:  To call a procedure, use CALL.

defaultdb=> call insert_adata(11,16);
CALL
defaultdb=> select * from adata;
 id | ivalues 
----+---------
...
 20 | {12,18}
 21 | {17,13}
(9 rows)

当然，select和call都可以在函数或者过程中发起，这也是其作为代码模块提供的特性。

  • execute

其实，execute和标准的函数调用是没有直接关系的，它用于执行一个字符串化的SQL语句。当然这个语句，是可以包含函数调用的，这样，就可以通过间接的方式，在函数中，来通过执行包含其他函数调用的SQL语句，来执行另一个函数。需要注意的是这个执行，只能在函数和过程定义当中开展，不能在标准SQL执行环境中使用。下面是一个简单的例子：

DO $$
BEGIN
    EXECUTE 'CALL process_data(''2024-01-01'', ''2024-01-31'')';
END $$;

以上就是Postgres中，定义和使用函数的基本过程和内容，通常情况下，已经可以满足大多数的业务应用和数据管理的需求了，但如果需要更多的控制和对开发运维工作的支持，如更高级的结果集遍历、异常处理、日志、事务支持等，我们将在后面的扩展章节中进行讨论。

小结

本文是在Postgres中，定义和使用函数的相关内容的上半部分。主要讨论了函数的基本概念，定义和基本控制结构，基本的调用方式等。

在下半部分中，将会继续讨论一些相对比较扩展的主题包括结果集遍历、错误处理、事务支持、日志等方面的内容。

转载自:https://juejin.cn/post/7415914111861997631
评论
请登录