postgresql存储过程_多行单列

上一篇讲的返回的是单行单列，比较简单，那如何返回多行呢

准备工作

1. return next，用在 for 循环中

CREATE OR REPLACE FUNCTION am2o ( in_id integer)
RETURNS SETOF varchar as $$
DECLARE
    v_name varchar;
BEGIN
   for v_name in  ( (select  d_name  from department where d_parentid = in_id) union all (select d_name from department1  where d_parentid = in_id) ) loop
     RETURN NEXT v_name;
   end loop;
   return;
END;
$$
LANGUAGE PLPGSQL;

select am2o(1);

1. 循环外还有个 return
2. 需要实现声明 v_name

2. return query，无需 for 循环

CREATE OR REPLACE FUNCTION am2o1 ( in_id integer)
RETURNS SETOF varchar as $$
BEGIN
   return query  ( (select d_name from department  where d_parentid = in_id) union all (select d_name from department1  where d_parentid = in_id) );
   return;
END;
$$
LANGUAGE PLPGSQL;

select am2o1(1);