如何开发返回表的 PL/pgSQL 函数

摘要：在本教程中，您将学习如何开发返回表的 PostgreSQL 函数。

要定义返回表的函数，请使用以下形式的create function语句：

create or replace function function_name (
   parameter_list
) 
returns table ( column_list ) 
language plpgsql
as $$ declare 
-- variable declaration
begin
-- body
end; $$

此语法不是返回单个值，而是允许您返回具有指定列列表的表：

returns table ( column_list )

我们将使用 PostgreSQL Sakila 示例数据库中的 film 表进行演示：

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

create or replace function get_film (
  p_pattern varchar
) 
    returns table (
        film_title varchar,
        film_release_year int
    ) 
    language plpgsql
as $$ begin
    return query 
        select
            title,
            release_year::integer
        from
            film
        where
            title ilike p_pattern;
end;$$

这 get_film(varchar) 接受一个参数 p_pattern ，该参数是您想要与电影标题匹配的模式。

该函数返回一个查询，该查询是 select 语句的结果。请注意，结果集中的列必须与 returns table 子句后定义的表中的列相同。

由于表中列的数据类型不是整数，因此需要使用强制转换运算符将其强制转换为整数。 release_year film ::

下面显示了如何调用该 get_film() 函数：

SELECT * FROM get_film ('Al%');

输出：

如果您使用以下语句调用该函数，PostgreSQL 将返回一个表，该表由一个包含行数组的列组成：

SELECT get_film ('Al%');

输出：

在实践中，您通常会在将每一行附加到函数的结果集中之前对其进行处理：

create or replace function get_film (
    p_pattern varchar,
    p_year int
) 
returns table (
    film_title varchar,
    film_release_year int
) 
language plpgsql
as $$
declare 
    var_r record;
begin
    for var_r in(
            select title, release_year 
            from film 
         where title ilike 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; $$

在这个例子中，我们创建了 get_film(varchar,int) 接受两个参数的：

将 p_pattern 被用于搜索电影。
p_year 是电影的发行年份。

在函数体中，我们使用了一个 for loop 语句来逐行处理查询。

该 return next 语句向函数的返回表中添加一行。

下面说明如何调用该 get_film() 函数：

SELECT * FROM get_film ('%er', 2006);

请注意，此示例仅用于演示目的。

概括

使用 returns table (column_list) 中的 create function 来定义返回表（或结果集）的函数。

概括

本页目录