PostgreSQL 存储过程详细案例

返回多行单列

又分为几种方式

1. return next，用在 for 循环中

CREATE OR REPLACE FUNCTION funcname ( in_id integer)
RETURNS SETOF varchar as $$
DECLARE
    v_name varchar;
BEGIN
   for v_name in  ( (select  name  from test_result1  where id = in_id) union (select  name  from test_result2  where id = in_id) ) loop
     RETURN NEXT v_name;
   end loop;
   return;
END;
$$
LANGUAGE PLPGSQL;

注意

1. 循环外还有个 return

2. 需要实现声明 v_name

2. return query，无需 for 循环

CREATE OR REPLACE FUNCTION funcname ( in_id integer)
RETURNS SETOF varchar as $$
DECLARE
    v_rec RECORD;
BEGIN
   return query  ( (select  name  from test_result1  where id = in_id) union (select  name  from test_result2  where id = in_id) );
   return;
END;
$$
LANGUAGE PLPGSQL;

注意：如果 返回类型为 setof，最好用如下方法

RETURN QUERY EXECUTE SQL

不要这么用

execute sql into  out;
return out;

返回多行多列

也有多种方式

1. 使用 return next 和  setof record ，需要 for 循环

CREATE OR REPLACE FUNCTION funcname ( in_id integer)
RETURNS SETOF RECORD as $$
DECLARE
    v_rec RECORD; 
BEGIN
   for v_rec in  ( (select id , name  from test_result1  where id = in_id) union (select id , name  from test_result2  where id = in_id) )loop
    RETURN NEXT v_rec;
   end loop;
   return;
END;
$$
LANGUAGE PLPGSQL;

注意

1. 读取表的整行数据时才能用 record

2. 如果读取的数据不是整行，需要自定义 复合数据类型，否则会报如下错误

ERROR:  a column definition list is required for functions returning "record"

定义复合类型，示例如下

create type myout2 as (
road_num int,
freq bigint
);

create or replace function test(car text, time1 text, time2 text)
returns setof myout2 as $$

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 号拼接
    select regexp_split_to_array(car, ',') into array2;
    select array_length(array2, 1) into len2;
    car_str := '';
    y := 1;
    while y <= 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;
    for i in execute sql loop
    return next i;
    end loop;
    return;

end

$$ language plpgsql;

在执行时可能会报如下错误

ERROR:  set-valued function called in context that cannot accept a set

解决方法

select funcname(arg);
--改为
select * from funcname(arg);

2.  return query，无需 for 循环

CREATE OR REPLACE FUNCTION funcname ( in_id integer)
RETURNS SETOF RECORD as $$
DECLARE
    v_rec RECORD;
BEGIN
   return query  ( (select id , name  from test_result1  where id = in_id) union (select id , name  from test_result2  where id = in_id) );
   return;
END;
$$
LANGUAGE PLPGSQL;

3. 使用 out 输出参数

 CREATE OR REPLACE FUNCTION funcname ( in_id integer,out o_id integer,out o_name varchar)
 RETURNS SETOF RECORD as $$
DECLARE
    v_rec RECORD;
BEGIN
   for v_rec in  ( (select id , name  from test_result1  where id = in_id) union (select id , name  from test_result2  where id = in_id) )loop
    o_id   := v_rec.id;
    o_name := v_rec.name;
    RETURN NEXT ;
   end loop;
   return;
END;
$$
LANGUAGE PLPGSQL;

总结 - return next && return query 

我们可以看到上面无论是单列多行还是多列多行，都用到了 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;