PostgreSQL等效的Oracle“批量收集” [英] PostgreSQL equivalent of Oracle "bulk collect"

问题描述

在Oracle中使用示例：

 创建或替换过程prc_tst_bulk_test是
 
类型typ_person是表的tb_person％rowtype; 
 v_tb_person typ_person; 
 
 begin 
 
 select * 
批量收集到v_tb_person 
 from tb_person; 
 
  - 在v_tb_person中进行选择，例如
 select name，count（*）from v_tb_person where age> 50 
 union 
从v_tb_person中选择名称，计数（*），其中gender = 1 
 
 end; 
  

谢谢

解决方案

在PostgreSQL中没有这样的语法，也没有一个close函数等价。

你可以在PL / PgSQL代码中创建一个临时表，用于所需目的。 PL / PgSQL中的临时表有点烦人，因为名称在会话中是全局的，但它们在PostgreSQL 8.4及更高版本中正常工作。

你在单个SQL语句中所做的所有工作是使用公共表表达式（CTE，或 WITH 查询）。这不适合所有情况。

上面的例子可以通过一个简单的 RETURN QUERY 在PL / PgSQL中，但我认为你的实例更复杂。

假设 tb_person 是某种昂贵的生成视图，在联合的每个分支中，您可以执行以下操作：

  CREATE或REPLACE FUNCTION prc_tst_bulk（）
 RETURNS TABLE （name text，rowcount integer）AS 
 $$ 
 BEGIN 
返回查询
 WITH v_tb_person AS（SELECT * FROM tb_person）
 select name，count（*）from v_tb_person where age> 50 
 union 
从v_tb_person中选择名称，计数（*），其中gender = 1; 
 END; 
 $$ LANGUAGE plpgsql; 
  

这种特殊情况可以进一步简化为一个简单的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
从v_tb_person中选择名称，计数（*），其中gender = 1;
$$ LANGUAGE sql;


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;

Thank you

解决方案

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;

这篇关于PostgreSQL等效的Oracle“批量收集”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！