本文我們學習如何在PostgreSQL 開發(fā)返回表函數(shù)。
示例數(shù)據(jù)表
我們使用的示例數(shù)據(jù)庫表為film，如下圖所示：
示例1
第一個函數(shù)發(fā)揮所有滿足條件film表記錄，這里使用ilike操作，和like類似，但不區(qū)分大小寫：
CREATE OR REPLACE FUNCTION get_film (p_pattern VARCHAR)
RETURNS TABLE (
film_title VARCHAR,
film_release_year INT
)
AS $$
BEGIN
RETURN QUERY SELECT
title,
cast( release_year as integer)
FROM
film
WHERE
title ILIKE p_pattern ;
END; $$
LANGUAGE 'plpgsql';
get_film(varchar) 函數(shù)接收一個參數(shù)，為匹配title字段的模式字符串。
為了從函數(shù)中返回表，需要使用return table語法，以及表的字段，每個字段使用逗號分隔。
在函數(shù)中，我們返回一個查詢（select 語句）作為返回結果。注意select語句中的字段必須和返回表的字段類型一致。因為film表中release_year的數(shù)據(jù)類型不是integer，所以我們要使用cast函數(shù)轉換成integer。
下面進行測試該函數(shù)：
SELECT
*
FROM
get_film ('Al%');
我們調用該函數(shù)，獲取所有title以Al開頭的記錄：
注意，我們也可以使用下面語句進行調用：
PostgreSQL 返回已一列數(shù)組形式返回表。
示例2
實際開發(fā)中，我們經(jīng)常需要在返回函數(shù)結果集之前處理每一行記錄。下面通過示例說明：
CREATE OR REPLACE FUNCTION get_film (p_pattern VARCHAR,p_year INT)
RETURNS TABLE (
film_title VARCHAR,
film_release_year INT
) AS $$
DECLARE
var_r record;
BEGIN
FOR var_r IN(SELECT title, release_year
FROM film
WHERE title ILIKE p_pattern
AND release_year = p_year)
LOOP
film_title := upper(var_r.title) ;
film_release_year := var_r.release_year;
RETURN NEXT;
END LOOP;
END; $$
LANGUAGE 'plpgsql';
該函數(shù)與上一個名稱一樣get_film(varchar,int)，但有兩個參數(shù)：
第一個參數(shù)匹配title字段的模式字符串。仍然使用ilike操作執(zhí)行搜索。
第二個參數(shù)是file的發(fā)行年度。
這兩個函數(shù)在PostgreSQL中稱為重載函數(shù)。我們想在返回結果之前處理每一行，使用 FOR LOOP語句進行處理。內(nèi)部每個迭代中使用UPPER函數(shù)是film title 變?yōu)榇髮懀瑑H為了演示而已。
return next語句是增加一行至函數(shù)結果集中，不斷執(zhí)行循環(huán)，在每次迭代中生成結果集。
下面進行測試：
SELECT
*
FROM
get_film ('%er', 2006);
總結
現(xiàn)在你應該理解了如何開發(fā)返回表的函數(shù)，主要使用return query 和 return next 語句。
補充：Postgres自定義函數(shù)返回記錄集(虛擬表結構)
看實例吧~
CREATE OR REPLACE FUNCTION fun_get_real_inv_qty(pvOrderId varchar)
RETURNS SETOF record AS
$BODY$begin
--drop table if exists tmp_1 ;
--create temp table tmp_1 as
return query
select fp_prod_id,fq_part_no,fq_name,
sum(case when fo_type='P' then -fp_qty
else 0
end
) as purchase_qty,
sum(case when fo_type='S' then -fp_qty
else 0
end
) as saleqty,
sum(case when fo_type='S' then -fp_qty
when fo_type='P' then fp_qty
else 0
end ) as surplus_qty from tp_send_det,to_send_note,tq_prod_mstr
where fp_order_id=pvOrderId and fo_note_id=fp_note_id and fq_prod_id=fp_prod_id and fq_type='I'
group by fp_prod_id,fq_part_no,fq_name ;
end;$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 10;
ALTER FUNCTION fun_get_real_inv_qty()
OWNER TO postgres;
上面是例子,調用這個函數(shù):
select * from fun_get_real_inv_qty('D302') f(fp_prod_id bigint,fq_part_no varchar ,fq_name varchar ,purchase_qty numeric ,saleqty numeric ,surplus_qty numeric );
f...后面帶的是記錄的column定義 必須與函數(shù)輸出的列數(shù)量及每列數(shù)據(jù)類型一一對應.
以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。如有錯誤或未考慮完全的地方，望不吝賜教。
您可能感興趣的文章:- Postgresql去重函數(shù)distinct的用法說明
- PostgreSQL的generate_series()函數(shù)的用法說明
- postgresql合并string_agg函數(shù)的實例
- PostgreSQL批量修改函數(shù)擁有者的操作
- PostgreSQL數(shù)據(jù)類型格式化函數(shù)操作
- 在postgresql數(shù)據(jù)庫中判斷是否是數(shù)字和日期時間格式函數(shù)操作
- Postgresql自定義函數(shù)詳解
- postgresql 循環(huán)函數(shù)的簡單實現(xiàn)操作
