Skip to main content
 首页 » 数据库

PostgreSQL定义返回表函数

2022年07月19日137zengkefu

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),但有两个参数:

  1. 第一个参数匹配title字段的模式字符串。仍然使用ilike操作执行搜索。
  2. 第二个参数是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
阅读延展