服务器之家

服务器之家 > 正文

PostgreSQL 定义返回表函数的操作

时间:2021-03-14 21:07     来源/作者:neweastsun

本文我们学习如何在PostgreSQL 开发返回表函数

示例数据表

 

我们使用的示例数据库表为film,如下图所示:

PostgreSQL 定义返回表函数的操作

示例1

 

第一个函数发挥所有满足条件film表记录,这里使用ilike操作,和like类似,但不区分大小写:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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。

下面进行测试该函数:

?
1
2
3
4
SELECT
 *
FROM
 get_film ('Al%');

我们调用该函数,获取所有title以Al开头的记录:

PostgreSQL 定义返回表函数的操作

注意,我们也可以使用下面语句进行调用:

?
1
2
SELECT
 get_film ('Al%');

PostgreSQL 返回已一列数组形式返回表。

PostgreSQL 定义返回表函数的操作

示例2

 

实际开发中,我们经常需要在返回函数结果集之前处理每一行记录。下面通过示例说明:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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语句是增加一行至函数结果集中,不断执行循环,在每次迭代中生成结果集。

下面进行测试:

?
1
2
3
4
SELECT
 *
FROM
 get_film ('%er', 2006);

PostgreSQL 定义返回表函数的操作

总结

 

现在你应该理解了如何开发返回表的函数,主要使用return query 和 return next 语句。

补充:Postgres自定义函数返回记录集(虚拟表结构)

看实例吧~

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
CREATE OR REPLACE FUNCTION fun_get_real_inv_qty(pvOrderId varchar)
 RETURNS SETOF record AS
$BODY$begin
--drop table if exists tmp_1 ;
--create temp table tmp_1 as
return query
    select fp_prod_id,fq_part_no,fq_name,
    sum(case when fo_type='P' then -fp_qty  
         else 0
      end
    ) as purchase_qty,
    sum(case when fo_type='S' then -fp_qty  
         else 0
      end
    ) as saleqty,
    sum(case when fo_type='S' then -fp_qty
         when fo_type='P' then fp_qty
         else 0
      end ) as surplus_qty from tp_send_det,to_send_note,tq_prod_mstr
    where fp_order_id=pvOrderId and fo_note_id=fp_note_id and fq_prod_id=fp_prod_id and fq_type='I'
    group by fp_prod_id,fq_part_no,fq_name ;
end;$BODY$
 LANGUAGE plpgsql VOLATILE
 COST 100
 ROWS 10;
ALTER FUNCTION fun_get_real_inv_qty()
 OWNER TO postgres;

上面是例子,调用这个函数:

?
1
select * from fun_get_real_inv_qty('D302') f(fp_prod_id bigint,fq_part_no varchar ,fq_name varchar ,purchase_qty numeric ,saleqty numeric ,surplus_qty numeric );

f...后面带的是记录的column定义 必须与函数输出的列数量及每列数据类型一一对应.

以上为个人经验,希望能给大家一个参考,也希望大家多多支持服务器之家。如有错误或未考虑完全的地方,望不吝赐教。

原文链接:https://blog.csdn.net/neweastsun/article/details/90316547

相关文章

热门资讯

2020微信伤感网名听哭了 让对方看到心疼的伤感网名大全
2020微信伤感网名听哭了 让对方看到心疼的伤感网名大全 2019-12-26
yue是什么意思 网络流行语yue了是什么梗
yue是什么意思 网络流行语yue了是什么梗 2020-10-11
背刺什么意思 网络词语背刺是什么梗
背刺什么意思 网络词语背刺是什么梗 2020-05-22
Intellij idea2020永久破解,亲测可用!!!
Intellij idea2020永久破解,亲测可用!!! 2020-07-29
苹果12mini价格表官网报价 iPhone12mini全版本价格汇总
苹果12mini价格表官网报价 iPhone12mini全版本价格汇总 2020-11-13
返回顶部