[postgresql]postgresql自定义函数返回table类型实例

---------------------------------------
--需求：返回table 二维表格式的数据
---------------------------------------
--1.创建测试表
create table test1(c1 int ,c2 varchar2(50));
insert into test1(c1,c2) values(1,'2');
insert into test1(c1,c2) values(2,'2');
insert into test1(c1,c2) values(1,'3');

--2.创建函数
CREATE OR REPLACE function  f_retable(p_id int)
RETURNS table(id int, name VARCHAR2(50))
LANGUAGE PLPGSQL  
AS $$
 DECLARE
    TYPE varray_type IS VARRAY(20) OF INT; 
    v_varray varray_type; 
BEGIN 
    v_varray := varray_type(1, 2, 3, 4, 5);
   RETURN QUERY SELECT c1 AS id, c2 AS name FROM test1 WHERE c1 in  (
  select  regexp_split_to_table(array_to_string(v_varray, ','),',')
 );
END ; $$

--3.执行函数
select * from f_retable(1);