PostgreSQL定义返回表函数
本文我们学习如何在PostgreSQL 开发返回表函数。
示例数据表
我们使用的示例数据库表为film，如下图所示：
示例1
第一个函数发挥所有满足条件film表记录，这里使用ilike操作，和like类似，但不区分大小写：
CREATE OR REPLACE FUNCTION get_film (p_pattern VARCHAR)
RETURNS TABLE (
film_title VARCHAR,
film_release_year INT
)
AS $$
BEGIN
RETURN QUERY SELECT
title,
cast( release_year as integer)
FROM
film
WHERE
title ILIKE p_pattern ;
END; $$
LANGUAGE 'plpgsql';
get_film(varchar) 函数接收一个参数，为匹配title字段的模式字符串。
为了从函数中返回表，需要使用return table语法，以及表的字段，每个字段使用逗号分隔。
在函数中，我们返回一个查询（select 语句）作为返回结果。注意select语句中的字段必须和返回表的字段类型一致。因为film表中release_year的数据类型不是integer，所以我们要使用cast函数转换成integer。
下面进行测试该函数：
SELECT
*
FROM
get_film ('Al%');
我们调用该函数，获取所有title以Al开头的记录：
注意，我们也可以使用下面语句进行调用：
SELECT
get_film ('Al%');
PostgreSQL 返回已一列数组形式返回表。
示例2
实际开发中，我们经常需要在返回函数结果集之前处理每一行记录。下面通过示例说明：
CREATE OR REPLACE FUNCTION get_film (p_pattern VARCHAR,p_year INT)
RETURNS TABLE (
film_title VARCHAR,
film_release_year INT
) 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; $$
LANGUAGE 'plpgsql';
该函数与上一个名称一样get_film(varchar,int)，但有两个参数：
- 第一个参数匹配title字段的模式字符串。仍然使用ilike操作执行搜索。
- 第二个参数是file的发行年度。
这两个函数在PostgreSQL中称为重载函数。我们想在返回结果之前处理每一行，使用 FOR LOOP语句进行处理。内部每个迭代中使用UPPER函数是film title 变为大写，仅为了演示而已。
return next语句是增加一行至函数结果集中，不断执行循环，在每次迭代中生成结果集。
下面进行测试：
SELECT
*
FROM
get_film ('%er', 2006);
总结
现在你应该理解了如何开发返回表的函数，主要使用return query 和 return next 语句。
本文参考链接：https://blog.csdn.net/neweastsun/article/details/90316547
