如何從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）

通過傳回 指針 也可以實作傳回多行結果。