https://www.cnblogs.com/Thenext/p/13531947.html
如果返回一个 数字或者字符 比较简单，那么多行多列怎么办呢，分为以下几种情况 【东西很多，这里只做简单列举】
返回多行单列
又分为几种方式
1. return next，用在 for 循环中
CREATEORREPLACEFUNCTIONfuncname ( in_idinteger)RETURNSSETOFvarcharas $$DECLARE v_name varchar;BEGINforv_namein( (selectnamefromtest_result1whereid=in_id)union(selectnamefromtest_result2whereid= in_id) ) loop
RETURNNEXT v_name;
end loop;
return;END;
$$
LANGUAGE PLPGSQL;
注意
1. 循环外还有个 return
2. 需要实现声明 v_name
2. return query，无需 for 循环
CREATEORREPLACEFUNCTIONfuncname ( in_idinteger)RETURNSSETOFvarcharas $$DECLARE v_rec RECORD;BEGINreturnquery ( (selectnamefromtest_result1whereid=in_id)union(selectnamefromtest_result2whereid= in_id) );
return;END;$$LANGUAGE PLPGSQL;
注意：如果 返回类型为 setof，最好用如下方法
RETURNQUERYEXECUTESQL
不要这么用
executesqlinto out;returnout;
返回多行多列
也有多种方式
1. 使用 return next 和 setof record ，需要 for 循环
CREATEORREPLACEFUNCTIONfuncname ( in_idinteger)RETURNSSETOF RECORDas $$DECLARE v_rec RECORD; BEGINforv_recin( (selectid , namefromtest_result1whereid=in_id)union(selectid , namefromtest_result2whereid= in_id) )loop
RETURNNEXT v_rec;
end loop;
return;END;
$$
LANGUAGE PLPGSQL;
注意
1. 读取表的整行数据时才能用 record
2. 如果读取的数据不是整行，需要自定义 复合数据类型，否则会报如下错误
ERROR: acolumndefinition listisrequiredforfunctions returning "record"
定义复合类型，示例如下
createtype myout2as (
road_num int,
freq bigint);createorreplacefunctiontest(cartext, time1text, time2text)returnssetof myout2as $$declare
array1 text[];
array2 text[];
len1 integer;
len2 integer;
x integer;
y integer;
road_str text;
car_str text;
sql text;
i myout2;
begin-- vin 号拼接selectregexp_split_to_array(car,',')into array2;
selectarray_length(array2,1)into len2;
car_str :='';
y :=1;
whiley<= len2 loop
car_str :=car_str||quote_literal(array2[y])||',';
y :=y+1;
end loop;
-- sql 拼接sql :='select road_number, sum(frequency) from heat_map where date_key >= '''|| time1
||'-01'' and date_key <='''|| time2
||'-20'' and vin in ('||rtrim(car_str,',')
||')group by road_number;';
--execute sql into out;foriinexecute sql loop
returnnext i;
end loop;
return;end$$ language plpgsql;
在执行时可能会报如下错误
ERROR:set-valuedfunctioncalledincontext that cannot accept aset
解决方法
select funcname(arg);--改为select*fromfuncname(arg);
2. return query，无需 for 循环
CREATEORREPLACEFUNCTIONfuncname ( in_idinteger)RETURNSSETOF RECORDas $$DECLARE v_rec RECORD;BEGINreturnquery ( (selectid , namefromtest_result1whereid=in_id)union(selectid , namefromtest_result2whereid= in_id) );
return;END;
$$
LANGUAGE PLPGSQL;
3. 使用 out 输出参数
CREATEORREPLACEFUNCTIONfuncname ( in_idinteger,out o_idinteger,out o_namevarchar)
RETURNSSETOF RECORDas $$DECLARE v_rec RECORD;BEGINforv_recin( (selectid , namefromtest_result1whereid=in_id)union(selectid , namefromtest_result2whereid= in_id) )loop
o_id := v_rec.id;
o_name := v_rec.name;
RETURNNEXT ;
end loop;
return;END;
$$
LANGUAGE PLPGSQL;
总结 - return next && return query
我们可以看到上面无论是单列多行还是多列多行，都用到了 return next 和 return query 方法
在 plpgsql 中，如果存储过程返回 setof sometype，则返回值必须在 return next 或者 return query 中声明，然后有一个不带参数的 retrun 命令，告诉函数执行完毕； 【setof 就意味着 多行】
用法如下
RETURNNEXT expression;RETURN QUERY query;RETURNQUERYEXECUTEcommand-string[ USING expression [, ... ]];
return next 可以用于标量和复合类型数据；
return query 命令将查询到的一条结果追加到函数的结果集中；
二者在单一集合返回函数中自由混合，在这种情况下，结果将被级联。【有待研究】
return query execute 是 return query 的变形，它指定 sql 将被动态执行；
returnqueryselectroad_number,sum(frequency)fromheat_mapgroupbyroad_number;--这样可以sql :='select road_number, sum(frequency) from heat_map group by road_number';returnquery sql;--这样不行
参考资料：
https://blog.csdn.net/victor_ww/article/details/44415895 postgresql自定义类型并返回数组
https://blog.csdn.net/weixin_42767321/article/details/92992935 PG return next & return query
https://blog.csdn.net/luojinbai/article/details/45487373 PostgreSQL function返回多列多行
https://www.cnblogs.com/xiongsd/archive/2013/06/05/3118704.html返回结果集多列和单列的例子
https://www.cnblogs.com/lottu/p/7404722.htmlPostgreSQL存储过程(1)-基于SQL的存储过程
https://blog.csdn.net/pg_hgdb/article/details/79692749 Postgresql动态SQL
https://stackoverflow.com/questions/40864464/postgresql-pgadmin-error-return-cannot-have-a-parameter-in-function-returning-s/40864898postgresql, pgadmin error RETURN cannot have a parameter in function returning set
https://blog.csdn.net/qq_42535651/article/details/92089510 postgresql存储过程输出参数
https://www.cnblogs.com/winkey4986/p/6437811.html
https://www.cnblogs.com/lottu/p/7405829.htmlPostgreSQL存储过程(3)-流程控制语句
网友评论