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