五月 25, 2024
摘要：在本教程中，您将学习如何使用returns setof选项，来定义返回一行或多行的函数。
目录
使用 returns setof 选项定义函数
PostgreSQL 函数允许您在数据库中封装可重用的逻辑。要从函数返回一行或多行，可以使用returns setof选项。
returns setof选项允许您从函数返回一个或多个具有预定义结构的行。
下面是创建一个返回一组行的函数的语法：
create or replace function function_name(parameters)
returns setof row_structure
as
$$
-- logic
-- ...
-- return one or more rows
return query select_query;
$$ language plpgsql;
在此语法中：
- 首先，在
create or replace function关键字后面指定函数名。 - 第二，带有一个预定义行结构，使用
returns setof。行结构可以是数据库中定义的复合类型。 - 第三，使用
return query语句和一个 SELECT 语句，从函数体中返回行。
调用函数
要调用一个带有returns setof的函数，请使用下面语句：
SELECT function_name(argument);
它将返回单列，其中包含了一个返回行的所有列的数组。
若要从返回行的特定列中检索数据，请在函数调用后指定点号（.）和列名：
SELECT (function_name(argument)).column_name;
如果你想检索返回行的所有列中的数据，你可以像这样使用.*：
SELECT (function_name(argument)).*;
或者，您可以使用SELECT ... FROM语句调用该函数：
SELECT * FROM function_name(argument);
PL/pgSQL 返回 SetOf 示例
我们将使用示例数据库中的film表进行演示。
首先，定义一个函数，通过 id 从film表中检索一个电影：
create or replace function find_film_by_id(
p_id int
)
returns setof film
as
$$
begin
return query select * from film
where film_id = p_id;
end;
$$
language plpgsql;
第二步，调用find_film_by_id()函数：
SELECT find_film_by_id(100);
输出：
find_film_by_id
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
(100,"Brooklyn Desert","A Beautiful Drama of a Dentist And a Composer who must Battle a Sumo Wrestler in The First Manned Space Station",2006,1,7,4.99,161,21.99,R,"2013-05-26 14:50:58.951",{Commentaries},"'battl':14 'beauti':4 'brooklyn':1 'compos':11 'dentist':8 'desert':2 'drama':5 'first':20 'man':21 'must':13 'space':22 'station':23 'sumo':16 'wrestler':17")
(1 row)
输出是一个包含了列数据的数组。
第三步，检索 id 为 100 的电影名称：
select (find_film_by_id(100)).title;
输出：
title
-----------------
Brooklyn Desert
(1 row)
第四步，检索返回行的所有列的数据：
SELECT * FROM find_film_by_id(100);
输出：
-[ RECORD 1 ]----+--------------------------------------------------------------------------------------------------------------------------------------------------------------
film_id | 100
title | Brooklyn Desert
description | A Beautiful Drama of a Dentist And a Composer who must Battle a Sumo Wrestler in The First Manned Space Station
release_year | 2006
language_id | 1
rental_duration | 7
rental_rate | 4.99
length | 161
replacement_cost | 21.99
rating | R
last_update | 2013-05-26 14:50:58.951
special_features | {Commentaries}
fulltext | 'battl':14 'beauti':4 'brooklyn':1 'compos':11 'dentist':8 'desert':2 'drama':5 'first':20 'man':21 'must':13 'space':22 'station':23 'sumo':16 'wrestler':17
注意，我们使用了 psql 中\x命令，来垂直显示结果集。
总结
使用returns setof从一个函数返回一行或多行。