PostgreSQL 教程: 创建 PL/pgSQL 函数

八月 18, 2023

摘要：在本教程中，您将学习如何使用 PostgreSQL 的CREATE FUNCTION语句来开发用户定义函数。

CREATE FUNCTION 语句简介

CREATE FUNCTION语句允许您定义新的用户定义函数。

下面说明了CREATE FUNCTION语句的语法：

create [or replace] function function_name(param_list)
   returns return_type 
   language plpgsql
  as
$$
declare 
-- variable declaration
begin
 -- logic
end;
$$

在这个语法中：

  • 首先，在create function关键字后指定函数的名称。如果你想修改现有的函数，可以使用关键字or replace。
  • 然后，在函数名称后面用括号括起来指定函数参数列表。函数可以有零个或多个参数。
  • 接下来，在returns关键字后面指定返回值的数据类型。
  • 之后，使用language plpgsql指定函数的过程语言。请注意，PostgreSQL 支持许多过程语言，而不仅仅是plpgsql。
  • 最后，在美元符号引用的字符串常量中放置一个代码块。

PostgreSQL CREATE FUNCTION 语句示例

我们将使用 dvdrental 示例数据库中的 film 表。

img

以下语句创建一个函数，用于计算长度在len_from和len_to参数之间的影片数量：

create function get_film_count(len_from int, len_to int)
returns int
language plpgsql
as
$$
declare
   film_count integer;
begin
   select count(*) 
   into film_count
   from film
   where length between len_from and len_to;
   
   return film_count;
end;
$$;

函数get_film_count有两个主要部分：函数头和函数体。

在函数头部分：

  • 首先，函数的名称get_film_count紧跟在create function关键字之后。
  • 其次，该get_film_count()函数接受两个整数类型的参数 len_from 和 len_to。
  • 第三，该get_film_count函数返回由returns int子句指定的整数。
  • 最后，函数的语言是由language plpgsql指定的plpgsql。

在函数体中：

  • 使用以$$开头和$$结尾的美元符号引用的字符串常量语法。在两个$$之间，您可以放置一个包含函数的声明和逻辑的代码块。
  • 在声明部分，声明一个名为film_count的变量，用于存储从film表中查询的电影数量。
  • 在代码块主体中，使用select into语句查询长度在len_from和len_to之间的影片数量，并将结果赋给变量film_count。在块的末尾，使用return语句返回film_count。

要执行 CREATE FUNCTION 语句，您可以使用任何 PostgreSQL 客户端工具，包括 psql 和 pgAdmin。

1) 使用 pgAdmin 创建函数

首先，启动 pgAdmin 工具并连接到 dvdrental 示例数据库。

其次，选择 工具 > 查询工具 打开查询工具。

第三，在查询工具中输入上述代码，然后单击 执行 按钮来创建get_film_count函数。

img

如果一切正常，您将看到以下消息：

CREATE FUNCTION
Query returned successfully in 44 msec.

它说明函数get_film_count已创建成功。

最后，您可以在 函数 列表中找到函数get_film_count：

img

如果找不到函数名称，可以右键单击“函数”节点并选择 刷新… 菜单项来刷新函数列表。

2) 使用 psql 创建函数

首先，启动 psql 交互工具并连接到dvdrental数据库。

其次，在 psql 中输入上述代码来创建函数，如下所示：

dvdrental=# create function get_film_count(len_from int, len_to int)
dvdrental-# returns int
dvdrental-# language plpgsql
dvdrental-# as
dvdrental-# $$
dvdrental$# declare
dvdrental$#    film_count integer;
dvdrental$# begin
dvdrental$#    select count(*)
dvdrental$#    into film_count
dvdrental$#    from film
dvdrental$#    where length between len_from and len_to;
dvdrental$#
dvdrental$#    return film_count;
dvdrental$# end;
dvdrental$# $$;

如果函数创建成功，您将看到以下消息：

CREATE FUNCTION

第三步，使用\df命令列出当前数据库中所有用户定义函数：

dvdrental=# \df

调用用户定义的函数

PostgreSQL 为您提供了三种调用用户定义函数的方法：

  • 使用位置表示法
  • 使用命名表示法
  • 使用混合表示法

1) 使用位置表示法

要使用位置表示法调用函数，您需要按照与参数相同的顺序指定参数。例如：

select get_film_count(40,90);

输出：

 get_film_count
----------------
            325
(1 row)

在此示例中，get_film_count() 的参数是40和90，对应于from_len和to_len参数。

当函数的参数很少时，可以使用位置表示法来调用该函数。

如果函数有很多参数，则应该使用命名表示法来调用它，因为这将使函数调用更加明显。

2) 使用命名表示法

下面显示了如何使用命名表示法调用get_film_count函数：

select get_film_count(
    len_from => 40, 
    len_to => 90
);

输出：

 get_film_count
----------------
            325
(1 row)

在命名表示法中，您可以使用=>来分隔参数的名称及其值。

为了向后兼容，PostgreSQL 支持基于:=的旧语法，如下所示：

select get_film_count(
    len_from := 40, 
    len_to := 90
);

3) 使用混合表示法

混合表示法是位置表示法和命名表示法的组合。例如：

select get_film_count(40, len_to => 90);

请注意，您不能在位置参数之前使用命名参数，如下所示：

select get_film_count(len_from => 40, 90);

错误：

ERROR:  positional argument cannot follow named argument
LINE 1: select get_film_count(len_from => 40, 90);

在本教程中，您学习了如何使用CREATE FUNCTION语句创建用户定义函数。

搜索

分类

标签