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 语句。

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

看实例吧~

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;

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

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定义 必须与函数输出的列数量及每列数据类型一一对应.

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