如何從PL/pgSQL 函數(shù)中返回多行數(shù)據(jù)

PgSQL 自7.3起支持SRF( Set Returning Func. 集合返回函數(shù)) 配合有一些新的函數(shù)權(quán)限選項，
使 schema 的設置更靈活性。SRF 除了手冊里提到的內(nèi)置函數(shù) generate_series generate_subscript 外，自定義函數(shù)也可返回集合。 下面示例摘譯自 PgSQL wiki ：PL/PgSQL如何返回多行結(jié)果

我們從處理簡單表單函數(shù)說起。

create table department(id int primary key, name text);
create table employee(id int primary key, name text, salary int, departmentid int references department);
insert into department values (1, 'Management');
insert into department values (2, 'IT');
insert into employee values (1, 'John Smith', 30000, 1);
insert into employee values (2, 'Jane Doe', 50000, 1);
insert into employee values (3, 'Jack Jackson', 60000, 2);

SRF 可以返回的數(shù)據(jù)類型可以是現(xiàn)有表中定義過的 rowtype 或通用的 record 類型。
首先我們看一個簡單的SQL函數(shù)返回現(xiàn)有表中的 rowtype

create function GetEmployees() 
    returns setof employee as 'select * from employee;' 
language 'sql';

這個非常簡單的函數(shù)直接返回 employee 中的所有行：
其返回類型為 setof employee 即返回由 employee 各行組成的行集合，
其主體采用簡單SQL語句，生成輸出的行。

SRF 可以在查詢中代替 FROM 中的 表 或 子查詢 。
例如用函數(shù)返回所有id>2的聘員只要：

select * from GetEmployees() where id > 2;

很好，但要返回更復雜的數(shù)據(jù)怎么辦？
例如： 部門列表及其中所有聘員的薪水，
要返回現(xiàn)有記錄類型，你需要創(chuàng)建一個虛構(gòu)類型來保存輸出的數(shù)據(jù)，
例如：

create type holder as (departmentid int, totalsalary int8);

這里創(chuàng)建了新的復合類型 holder 由名為 departmentid 的 int
和名為 totalsalary 的 bigint 組成，我們可以讓函數(shù)返回此類型的集合：
這次我們用 SQL 和 PL/pgSQL 來分別實現(xiàn)這個函數(shù):

create function SqlDepartmentSalaries() 
returns setof holder as
'
select 
    departmentid, sum(salary) as totalsalary 
from GetEmployees() 
group by departmentid
'
language 'sql';

create or replace function PLpgSQLDepartmentSalaries() 
returns setof holder as
'
declare
    r holder%rowtype;
begin
    for r in select departmentid, sum(salary) as totalsalary 
    from GetEmployees() group by departmentid loop
        return next r;
    end loop;
    return;
end
'
language 'plpgsql';

SQL的版本與之前的很相像，返回由 holder (int, int8) 類型定義的 rowtype，

返回的行由函數(shù)體中的 group by 查詢決定。

PL/pgSQL 版本稍復雜，首先變量 r 被聲明為 rowtype holder 。用此變量保存行函數(shù)體中的查詢結(jié)果，函數(shù)主體對 group by 查詢的結(jié)果循環(huán)執(zhí)行，r 依次被賦值為結(jié)果中的各行，循環(huán)體中采用了新的return形式 'return next' 即將結(jié)果追加到返回的集合中，但不會造成函數(shù)返回。目前 PL/pgSQL 的 SRF 函數(shù)在全部結(jié)果生成完畢前不會返回。如果集合很大會寫入硬盤。此限制未來的版本中也許會改變。

新函數(shù)的使用與之前相同，

select * from PLpgSQLDepartmentSalaries();

PL/pgSQL 函數(shù)還可以對結(jié)果進行運算，只返回某些結(jié)果。
例如：要計算部門運營成本：部門總薪資 70,000 以上的開銷是 75%, 其他為 50%，

返回薪資+開銷>100,000的部門的部門id。

create or replace function ExpensiveDepartments() 
returns setof int as
'
declare
    r holder%rowtype;
begin
    for r in select departmentid, sum(salary) as totalsalary
    from GetEmployees() group by departmentid loop
        if (r.totalsalary > 70000) then
            r.totalsalary := CAST(r.totalsalary * 1.75 as int8);
        else
            r.totalsalary := CAST(r.totalsalary * 1.5 as int8);
        end if;
        if (r.totalsalary > 100000) then
            return next r.departmentid;
        end if;
    end loop;
return;
end
'
language 'plpgsql';

比較一下本次與之前 PLpgSQLDepartmentSales() 的區(qū)別。

因為本次只需要返回高成本部門的 department id
函數(shù)返回一個整數(shù)集合 (department id) 而非之前的復合類型。

if (r.totalsalary > 70000) then
    r.totalsalary := CAST(r.totalsalary * 1.75 as int8);
else
    r.totalsalary := CAST(r.totalsalary * 1.5 as int8);
end if;

然后判斷 totalsalary 是否大于 100,000 如果為真，則返回識別符

if (r.totalsalary > 100000) then
return next r.departmentid;
end if;

注意本次 return next 沒有返回記錄 r 而只有 departmentid,
如果需要同時返回 薪資總額與開銷之和，

前面聲明中可以定義為 return setof holder 這里使用 return next r;

以上函數(shù)返回的復合類型使用的前提是返回的類型與函數(shù)的 return 聲明中的相同。
如果不同，SQL版本在創(chuàng)建時會報錯，PL/pgSQL 版本在運行中會出現(xiàn)錯。
但如果結(jié)果中的類型只能在運行中確定該怎么辦？
此時你可以聲明 return setof record 以返回復合類型的集合，

返回的類型可以調(diào)用時設置。例如我們要創(chuàng)建一個函數(shù)返回指定表中的所有行：

create or replace function GetRows(text) 
returns setof record as
'
declare
    r record;
begin
    for r in EXECUTE ''select * from '' || $1 loop
        return next r;
    end loop;
return;
end
'
language 'plpgsql';

調(diào)用此函數(shù)時比之前的要稍復雜，查詢中需要指定函數(shù)返回的數(shù)據(jù)。
PostgreSQL 對 SRF函數(shù) 的處理與子查詢相似，語法上與為子查詢中別名的設定相似。

select * from GetRows('Department') as dept(deptid int, deptname text);

我們將 Department 作為參數(shù)傳入，結(jié)果應該與 Department 表的一般記錄相同，

由一個 INT 和一個 TEXT 組成。于是我們告訴PgSQL，結(jié)果dept 為別名，

包含一個名為 deptid 的整數(shù)和 deptname 的文本。

最后我們試試完全用 PL/pgSQL函數(shù)生成數(shù)據(jù)。讓我們從最簡單的做起：

寫一個函數(shù)，接收返回1到任意數(shù)間的所有數(shù)，以及這個他們的二倍。

我們先寫一個以預定義類型的為內(nèi)部和返回類型的版本。

create type numtype as (num int, doublenum int);
create or replace function GetNum(int) 
returns setof numtype as
'
declare
    r numtype%rowtype;
    i int;
begin
    for i in 1 .. $1 loop
        r.num := i;
        r.doublenum := i*2;
        return next r;
    end loop;
    return;
end
'
language 'plpgsql';

函數(shù)非常簡單，聲明中 r 為名為 numtype 的自定義 rowtype 。

將1到參數(shù)間的每個值，賦給 num 和 doublenum

然后 return next r 將結(jié)果加入輸出集合的隊列中；

用 record 類型可以實現(xiàn)通用效果，免去函數(shù)外的類型聲明，

不過做起來會更復雜而且需要多一次 select 調(diào)用。

類似返回多個結(jié)果的還有 動態(tài) SQL 查詢語句

（PREPARE STATEMENT... + EXECUTE...INTO...USING + DEALLOCATE PREPARE）

通過返回 指針 也可以實現(xiàn)返回多行結(jié)果。

https://www.postgresql.org/docs/current/static/ecpg-dynamic.html