PostgreSQL 函数返回参数定义以及返回多个参数解决方案

在使用PostgreSQL 写函数过程的时候会存在一个问题那就是定义函数的参数及sql查询返回TABLE数据时比较麻烦

1. 先看如下函数定义了入参reqname，reqage和返回的表字段name，age

CREATE OR REPLACE FUNCTION "public"."creat_table_test"("reqname" varchar, "reqage" varchar)
  RETURNS TABLE("name" varchar,  "age" int4) AS $BODY$
DECLARE 
vsql varchar(1000);
BEGIN
		vsql:= ' SELECT cast ('''' as VARCHAR) as name, 3 as int';
	raise notice 'query sql:%',vsql;
  RETURN  QUERY execute vsql;		
	RETURN;		
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000

如果有需求需要加一个表字段返回：地址 address

CREATE OR REPLACE FUNCTION "public"."creat_table_test"("reqname" varchar, "reqage" varchar)
  RETURNS TABLE("name" varchar,  "age" int4,"address" VARCHAR) AS $BODY$
DECLARE 
vsql varchar(1000);
BEGIN
		vsql:= ' SELECT cast ('''' as VARCHAR) as name, 3 as int';
	raise notice 'query sql:%',vsql;
  RETURN  QUERY execute vsql;		
	RETURN;		
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000

此时有个不友好的提示出现了：

> ERROR:  cannot change return type of existing function
DETAIL:  Row type defined by OUT parameters is different.
HINT:  Use DROP FUNCTION creat_table_test(character varying,character varying) first

不能更改函数定义好的返回类型，需要先DROP该函数才能创建。如果需求时刻变化又增加返回比如电话、学历、性别等，那就需要反复创建函数了。

那么有没有一劳永逸的办法，只创建一次函数，后续无论增加多少字段返回都无需先DROP再创建

答案是有的，且看下面解决的一个办法：

CREATE OR REPLACE FUNCTION "public"."creat_table_test"("reqname" varchar, "reqage" varchar)
 RETURNS TABLE("id" varchar,  "tabledata" text) AS $BODY$
DECLARE 
vsql varchar(1000);
BEGIN
		vsql:= ' SELECT cast (''1'' as VARCHAR) as id, 	(select cast(jsondata as text) as jsondata from (select json_agg(row_to_json(tbres)) as jsondata from 			
							(
									-- sql  start
									
									SELECT 3 as age,
									''liming'' as name,
									''北京''  as address
									-- sql  end
									
									) as tbres
				)  as tmp_tbdata ) as tabledata';
	raise notice 'query sql:%',vsql;
  RETURN  QUERY execute vsql;		
	RETURN;		
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000

这样无论需要返回多少字段的信息，只需要在sql  start 和sql  end之间写需要查询的sql业务逻辑即可，这样就方便了很多。

查询结果：