如何從PL/pgSQL 函數中返回多行數據

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

我們從處理簡單表單函數說起。

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 可以返回的數據類型可以是現有表中定義過的 rowtype 或通用的 record 類型。
首先我們看一個簡單的SQL函數返回現有表中的 rowtype

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

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

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

select * from GetEmployees() where id > 2;

很好，但要返回更復雜的數據怎么辦？
例如： 部門列表及其中所有聘員的薪水，
要返回現有記錄類型，你需要創建一個虛構類型來保存輸出的數據，
例如：

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

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

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，

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

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

新函數的使用與之前相同，

select * from PLpgSQLDepartmentSalaries();

PL/pgSQL 函數還可以對結果進行運算，只返回某些結果。
例如：要計算部門運營成本：部門總薪資 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() 的區別。

因為本次只需要返回高成本部門的 department id
函數返回一個整數集合 (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;

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

返回的類型可以調用時設置。例如我們要創建一個函數返回指定表中的所有行：

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';

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

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

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

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

包含一個名為 deptid 的整數和 deptname 的文本。

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

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

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

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';

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

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

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

用 record 類型可以實現通用效果，免去函數外的類型聲明，

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

類似返回多個結果的還有 動態 SQL 查詢語句

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

通過返回 指針 也可以實現返回多行結果。

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