[Original] PostgreSQL returns multiple result sets

MySQL stored procedures can easily return multiple result sets at a time, but POSTGRESQL currently does not provide such a function.

A literal translation method is to use a cursor, but you can also put the result set in an array or a large temporary table or XML format for output.

For example, you can simply write a stored procedure in MySQL.

Delimiter | create procedure sp_get_multiple_set1 () begin select * from j1 limit 20; select * from tmp_2 limit 20; end; | delimiter; two result sets are obtained after execution. T_girl -- call sp_get_multiple_set1 (); + ------ + | id | str1 | + ------ + | 1 | love | 2 | hate | + ------ + 2 rows in set (0.00 sec) + ------ + ------------- + | id | register_date | + ------ + --------------- + | 1 | 2014-01-18 | 2 | 2014-01-03 | 3 | 2014-01-16 | + ------ + --------------- + 3 rows in set (0.00 sec) query OK, 0 rows affected (0.00 sec) t_girl --

In the PostgreSQL environment, the following function code can only be implemented with a cursor, and cannot be returned in the function.

create or replace function sp_get_multiple_set1(IN f_id int,refcursor,refcursor) returns setof refcursoras$ytt$declare r1 alias for $2;               r2 alias for $3;begin   open  r1 for select * from j1 where id =f_id;   return next r1;   open r2 for select * from tmp_2 where id = f_id;   return next r2;end;$ytt$ language plpgsql;

Next, you can obtain the corresponding cursor result set in a transaction.

t_girl=# begin;BEGINt_girl=# select * from sp_get_multiple_set1(1,'a'::refcursor,'b'::refcursor); sp_get_multiple_set1---------------------- a b(2 rows)t_girl=# fetch all from a; id | str1----+------  1 | love(1 row)t_girl=# fetch all from b; id | register_date----+---------------  1 | 2013-01-20(1 row)t_girl=# close a;CLOSE CURSORt_girl=# close b;CLOSE CURSORt_girl=# commit;COMMITt_girl=#

If multiple result sets exist, but each result set contains only one row of data, you can also use the OUT parameter.

create or replace function sp_get_single_set2(f_id int,IN refcursor,IN refcursor,OUT r3 ytt_j1,OUT r4 ytt_tmp_2) returns  recordas$ytt$declare r1 alias for $2;              r2 alias for $3;begin   open  r1 for select * from j1 where id =f_id;   open r2 for select * from tmp_2 where id = f_id;   loop      fetch r1 into r3;      fetch r2 into r4;      exit when not found;      return;   end loop;   close r1;   close r2;end;$ytt$ language plpgsql;t_girl=# select * from sp_get_multiple_set2(1,'a'::refcursor,'b'::refcursor);    r3    |       r4      ----------+---------------- (1,love) | (1,2013-01-20)(1 row)t_girl=#

If the fields and types of multiple returned result sets are the same, the same result set will be followed.