PostgreSQL equivalent of Oracle "bulk collect"
plpgsql, postgresql
Solution
There is no such syntax in PostgreSQL, nor a close functional equivalent.
You can create a temporary table in your PL/PgSQL code and use that for the desired purpose. Temp tables in PL/PgSQL are a little bit annoying because the names are global within the session, but they work correctly in PostgreSQL 8.4 and up.
A better alternative for when you're doing all the work within a single SQL statement is to use a common table expression (CTE, or `WITH` query). This won't be suitable for all situations.
The example above would be much better solved by a simple `RETURN QUERY` in PL/PgSQL, but I presume your real examples are more complex.
Assuming that `tb_person` is some kind of expensive-to-generate view that you don't just want to scan in each branch of the union, you could do something like:
CREATE OR REPLACE FUNCTION prc_tst_bulk()
RETURNS TABLE (name text, rowcount integer) AS
$$
BEGIN
RETURN QUERY
WITH v_tb_person AS (SELECT * FROM tb_person)
select name, count(*) from v_tb_person where age > 50
union
select name, count(*) from v_tb_person where gender = 1;
END;
$$ LANGUAGE plpgsql;
This particular case can be further simplified into a plain SQL function:
CREATE OR REPLACE FUNCTION prc_tst_bulk()
RETURNS TABLE (name text, rowcount integer) AS
$$
WITH v_tb_person AS (SELECT * FROM tb_person)
select name, count(*) from v_tb_person where age > 50
union
select name, count(*) from v_tb_person where gender = 1;
$$ LANGUAGE sql;
Problem
In PostgreSQL exists some ways to make a statement using bulk collect into like in Oracle? Example in Oracle: ``` create or replace procedure prc_tst_bulk_test is type typ_person is table of tb_person%rowtype; v_tb_person typ_person; begin select * bulk collect into v_tb_person from tb_person; -- make a selection in v_tb_person, for instance select name, count(*) from v_tb_person where age > 50 union select name, count(*) from v_tb_person where gender = 1 end; ```