postgresql存储过程_多行多列_单表或结构相同表

前面的单行单列和多行单列，返回值都只有一个类型，那多行多列如何定义返回值呢？

使用RECORD

record实际上相当于一个占位符或者说指针。
也就是说，系统看到record不会反应过来要去查系统表。

CREATE OR REPLACE FUNCTION am2m1 ( in_id integer)
RETURNS SETOF RECORD as $$
DECLARE
    v_rec RECORD; 
BEGIN
   for v_rec in  ( (select id,d_code,d_name,d_parentid  from department  where id = in_id) union (select id,d_code,d_name,d_parentid  from department1  where id = in_id) )loop
    RETURN NEXT v_rec;
   end loop;
   return;
END;
$$
LANGUAGE PLPGSQL;

select * from am2m1(1) as (id integer, d_code varchar, d_name varchar, d_parentid integer);

数据库中查了几列属性，查询语句后 as 里就要定义几个属性。
不然会报错
ERROR:  wrong record type supplied in RETURN NEXT
DETAIL:  Number of returned columns (4) does not match expected column count (3).
CONTEXT:  PL/pgSQL function am2m1(integer) line 6 at RETURN NEXT

直接将类型定义在RETURNS后

• 先尝试了下用for循环

CREATE OR REPLACE FUNCTION am2m2 ( in_id integer)
RETURNS TABLE("id" integer, "d_code" varchar, "d_name" varchar) as $$
DECLARE
    v_rec RECORD; 
BEGIN
   for v_rec in  ( (select id,d_code,d_name,d_parentid  from department  where id = in_id) union (select id,d_code,d_name,d_parentid  from department1  where id = in_id) )loop
    RETURN NEXT v_rec;
   end loop;
   return;
END;
$$
LANGUAGE PLPGSQL;
select * from am2m2(1);

创建报错：

ERROR:  RETURN NEXT cannot have a parameter in function with OUT parameters
LINE 7:     RETURN NEXT v_rec;

• 尝试用query

CREATE OR REPLACE FUNCTION am2m3 ( in_id integer)
RETURNS TABLE("id" integer, "d_code" varchar, "d_name" varchar, "d_parentid" integer) as $$
DECLARE
    v_rec RECORD; 
BEGIN
   RETURN QUERY ( (select d.id,d.d_code,d.d_name,d.d_parentid  from department d  where d.id = in_id) union (select t.id,t.d_code,t.d_name,t.d_parentid  from department1 t  where t.id = in_id) );
   RETURN;
END;
$$
LANGUAGE PLPGSQL;
select * from am2m3(1);

成功，用query时要求在sql语句中的表给上别名。

定义复合类型

-- 定义符合类型作为返回值
create type myout as (
	id int,
	d_code varchar
);

CREATE OR REPLACE FUNCTION am2m4 ( in_id integer)
RETURNS SETOF myout as $$
BEGIN
   return query  ( (select id,d_code  from department  where id = in_id) union (select id,d_code  from department1 where id = in_id) );
   return;
END;
$$
LANGUAGE PLPGSQL;

select * from am2m4(1);

总结

我们可以看到上面无论是单列多行还是多列多行，都用到了 return next 和 return query 方法

在 plpgsql 中，如果存储过程返回 setof sometype，则返回值必须在 return next 或者 return query 中声明，然后有一个不带参数的 retrun 命令，告诉函数执行完毕；　　　　【setof 就意味着 多行】

RETURN NEXT expression;
RETURN QUERY query;
RETURN QUERY EXECUTE command-string [ USING expression [, ... ] ];

return next 可以用于标量和复合类型数据；
return query 命令将查询到的一条结果追加到函数的结果集中；
return query execute 是 return query 的变形，它指定 sql 将被动态执行；

return query select road_number, sum(frequency) from heat_map group by road_number; --这样可以

sql := 'select road_number, sum(frequency) from heat_map group by road_number';return query sql;    --这样不行