PostgreSQL 教程: 返回表的 PL/pgSQL 函数

八月 18, 2023

摘要:在本教程中,您将学习如何开发返回表的 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 )

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

img

以下函数使用 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子句后定义的表中的列相同。

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

下面展示了如何调用该get_film()函数:

SELECT * FROM get_film ('Al%');

输出:

img

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

SELECT get_film ('Al%');

输出:

img

在实践中,您通常会先处理每一行,然后再将其追加到函数的结果集中:

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);

img

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

概括

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