PostgreSQL 相当于 Oracle“批量收集”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16289225/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL equivalent of Oracle "bulk collect"
提问by Leandro
In PostgreSQL exists some ways to make a statement using bulk collect into like in Oracle?
在 PostgreSQL 中是否存在一些方法来像在 Oracle 中那样使用批量收集来创建语句？
Example in Oracle:
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;
Thank you
谢谢
回答by Craig Ringer
There is no such syntax in PostgreSQL, nor a close functional equivalent.
PostgreSQL 中没有这样的语法，也没有类似的功能。
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.
您可以在 PL/PgSQL 代码中创建一个临时表并将其用于所需目的。PL/PgSQL 中的临时表有点烦人，因为名称在会话中是全局的，但它们在 PostgreSQL 8.4 及更高版本中正常工作。
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 WITHquery). This won't be suitable for all situations.
当您在单个 SQL 语句中完成所有工作时，更好的替代方法是使用公共表表达式（CTE 或WITH查询）。这并不适合所有情况。
The example above would be much better solved by a simple RETURN QUERYin PL/PgSQL, but I presume your real examples are more complex.
上面的示例可以通过RETURN QUERYPL/PgSQL 中的简单方法更好地解决，但我认为您的真实示例更复杂。
Assuming that tb_personis 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:
假设tb_person您不只是想在联合的每个分支中扫描某种昂贵的生成视图，您可以执行以下操作：
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:
这种特殊情况可以进一步简化为一个普通的 SQL 函数：
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;
回答by Topper Harley
In PostgreSQL 10 you can use array_agg:
在 PostgreSQL 10 中，您可以使用array_agg：
declare
v_ids int[];
begin
select array_agg(id) INTO v_ids
from mytable1
where host = p_host;
--use v_ids...
end;
You'll have array and it can be used to make select from it using unnest:
您将拥有数组，它可用于使用unnest从中进行选择：
select * from unnest(v_ids) where ...
回答by Pavel Stehule
You can use a PostgreSQL arrays too - it is similar to Oracle's collections:
您也可以使用 PostgreSQL 数组 - 它类似于 Oracle 的集合：
postgres=# create table _foo(a int, b int);
CREATE TABLE
postgres=# insert into _foo values(10,20);
INSERT 0 1
postgres=# create or replace function multiply()
returns setof _foo as $$
/*
* two tricks are here
* table name can be used as type name
* table name can be used as fictive column that packs all fields
*/
declare a _foo[] = (select array(select _foo from _foo));
begin
return query select * from unnest(a)
union
all select * from unnest(a);
end;
$$ language plpgsql;
CREATE FUNCTION
postgres=# select * from multiply();
a | b
----+----
10 | 20
10 | 20
(2 rows)
But in your case Craig Ringer's proposal is perfect and should be preferable.
但在你的情况下，克雷格林格的提议是完美的，应该是更可取的。
