test: create type h3 as (id int,name char(10)); CREATE or replace FUNCTION proc17() RETURNS SETOF h3 AS $$ DECLARE v_rec h3; BEGIN create temp table abc(id int,name varchar) on commit drop; insert into abc select 1,'lw'; insert into abc select 2,'lw2'; for v_rec in select * from abc loop return next v_rec; end loop; END; $$ LANGUAGE plpgsql;
CREATE or replace FUNCTION proc16() RETURNS SETOF h3 AS $$ DECLARE id_array int[]; name_arr varchar[]; v_rec h3; BEGIN id_array =array[1,2]; name_arr=array['lw','lw2']; for v_rec in select unnest(id_array) ,unnest(name_arr) loop return next v_rec; end loop; END; $$ LANGUAGE plpgsql; postgres=# select * from proc17(); id | name ----+------------ 1 | lw 2 | lw2 (2 rows) Time: 68.372 ms postgres=# select * from proc16(); id | name ----+------------ 1 | lw 2 | lw2 (2 rows) Time: 1.357 ms temp talbe result: [postgres@pg95 test_sql]$ pgbench -M prepared -n -r -c 2 -j 2 -T 10 -f temporary_test_1.sql transaction type: Custom query scaling factor: 1 query mode: prepared number of clients: 2 number of threads: 2 duration: 10 s number of transactions actually processed: 5173 latency average: 3.866 ms tps = 517.229191 (including connections establishing) tps = 517.367956 (excluding connections establishing) statement latencies in milliseconds: 3.863798 select * from proc17(); array result: [postgres@pg95 test_sql]$ pgbench -M prepared -n -r -c 2 -j 2 -T 10 -f arrary_test_1.sql transaction type: Custom query scaling factor: 1 query mode: prepared number of clients: 2 number of threads: 2 duration: 10 s number of transactions actually processed: 149381 latency average: 0.134 ms tps = 14936.875176 (including connections establishing) tps = 14940.234960 (excluding connections establishing) statement latencies in milliseconds: 0.132983 select * from proc16(); Array is not convenient to use in function, whether there are other methods can be replaced temp table in function dby...@163.com