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

成都創(chuàng)新互聯(lián)專注于平順網(wǎng)站建設(shè)服務(wù)及定制，我們擁有豐富的企業(yè)做網(wǎng)站經(jīng)驗。 熱誠為您提供平順營銷型網(wǎng)站建設(shè)，平順網(wǎng)站制作、平順網(wǎng)頁設(shè)計、平順網(wǎng)站官網(wǎng)定制、成都微信小程序服務(wù)，打造平順網(wǎng)絡(luò)公司原創(chuàng)品牌,更為您提供平順網(wǎng)站排名全網(wǎng)營銷落地服務(wù)。

我們從處理簡單表單函數(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;

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

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

這里創(chuàng)建了新的復(fù)合類型 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 版本稍復(fù)雜，首先變量 r 被聲明為 rowtypeholder 。用此變量保存行函數(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) 而非之前的復(fù)合類型。

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 setofholder 這里使用 return nextr;

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

返回的類型可以調(diào)用時設(shè)置。例如我們要創(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ù)時比之前的要稍復(fù)雜，查詢中需要指定函數(shù)返回的數(shù)據(jù)。
PostgreSQL 對 SRF函數(shù) 的處理與子查詢相似，語法上與為子查詢中別名的設(shè)定相似。

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

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

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

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

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

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

我們先寫一個以預(yù)定義類型的為內(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ù)外的類型聲明，

不過做起來會更復(fù)雜而且需要多一次 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

新聞名稱：如何從PL/pgSQL函數(shù)中返回多行數(shù)據(jù)
本文地址：http://jinyejixie.com/article2/gcioic.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供全網(wǎng)營銷推廣、網(wǎng)站內(nèi)鏈、App設(shè)計、云服務(wù)器、網(wǎng)站維護、關(guān)鍵詞優(yōu)化

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)