PostgreSQL教程(十九)：SQL語言函數(shù)
一、基本概念：
SQL函數(shù)可以包含任意數(shù)量的查詢，但是函數(shù)只返回最后一個查詢(必須是SELECT)的結(jié)果。在簡單情況下，返回最后一條查詢結(jié)果的第一行。如果最后一個查詢不返回任何行，那么該函數(shù)將返回NULL值。如果需要該函數(shù)返回最后一條SELECT語句的所有行，可以將函數(shù)的返回值定義為集合，即SETOF sometype。
SQL函數(shù)的函數(shù)體應(yīng)該是用分號分隔的SQL語句列表，其中最后一條語句之后的分號是可選的。除非函數(shù)聲明為返回void，否則最后一條語句必須是SELECT。事實上，在SQL函數(shù)中，不僅可以包含SELECT查詢語句，也可以包含INSERT、UPDATE和DELETE等其他標(biāo)準(zhǔn)的SQL語句，但是和事物相關(guān)的語句不能包含其中，如BEGIN、COMMIT、ROLLBACK和SAVEPOINT等。
CREATE FUNCTION命令的語法要求函數(shù)體寫成一個字符串文本。通常來說，該文本字符串常量使用美元符($$)圍住，如：
CREATE FUNCTION clean_emp() RETURNS void AS $$
DELETE FROM emp WHERE salary < 0;
$$ LANGUAGE SQL;
最后需要說明的是SQL函數(shù)中的參數(shù)，PostgreSQL定義$1表示第一個參數(shù)，$2為第二個參數(shù)并以此類推。如果參數(shù)是復(fù)合類型，則可以使用點表示法，即$1.name訪問復(fù)合類型參數(shù)中的name字段。需要注意的是函數(shù)參數(shù)只能用作數(shù)據(jù)值，而不能用于標(biāo)識符，如：
INSERT INTO mytable VALUES ($1); --合法
INSERT INTO $1 VALUES (42); --不合法(表名屬于標(biāo)示符之一)
二、基本類型：
最簡單的SQL函數(shù)可能就是沒有參數(shù)且返回基本類型的函數(shù)了，如：
CREATE FUNCTION one() RETURNS integer AS $$
SELECT 1 AS result;
$$ LANGUAGE SQL;
下面的例子聲明了基本類型作為函數(shù)的參數(shù)。
CREATE FUNCTION add_em(integer, integer) RETURNS integer AS $$
SELECT $1 + $2;
$$ LANGUAGE SQL;
# 通過select調(diào)用函數(shù)。
postgres=# SELECT add_em(1,2) AS answer;
answer
--------
3
(1 row)
在下面的例子中，函數(shù)體內(nèi)包含多個SQL語句，它們之間是用分號進行分隔的。
CREATE FUNCTION tf1 (integer, numeric) RETURNS numeric AS $$
UPDATE bank SET balance = balance - $2 WHERE accountno = $1;
SELECT balance FROM bank WHERE accountno = $1;
$$ LANGUAGE SQL;
三、復(fù)合類型：
見如下示例：
1). 創(chuàng)建數(shù)據(jù)表，這樣與之對應(yīng)的復(fù)合類型也隨之生成。
CREATE TABLE emp (
name text,
salary numeric,
age integer,
);
2). 創(chuàng)建函數(shù)，其參數(shù)為復(fù)合類型。在函數(shù)體內(nèi)，可以像引用基本類型參數(shù)那樣引用復(fù)合類型，如$1。訪問復(fù)合類型的字段使用點表達式即可，如：$1.salary。
CREATE FUNCTION double_salary(emp) RETURNS integer AS $$
SELECT ($1.salary * 2)::integer AS salary;
$$ LANGUAGE SQL;
3). 在select語句中，可以使用emp.*表示emp表的一整行數(shù)據(jù)。
SELECT name, double_salary(emp.*) AS dream FROM emp WHERE age > 30;
4). 我們也可以使用ROW表達式構(gòu)造自定義的復(fù)合類型，如：
SELECT name, double_salary(ROW(name, salary*1.1, age)) AS dream FROM emp;
5). 創(chuàng)建一個函數(shù)，其返回值為復(fù)合類型，如：
CREATE FUNCTION new_emp() RETURNS emp AS $$
SELECT ROW('None', 1000.0, 25)::emp;
$$ LANGUAGE SQL;
6). 調(diào)用返回復(fù)合類型的函數(shù)。
SELECT new_emp();
7). 調(diào)用返回復(fù)合類型的函數(shù)，同時訪問該返回值的某個字段。
SELECT (new_emp()).name;
四、帶輸出參數(shù)的函數(shù)：
還有一種方法可以用于返回函數(shù)執(zhí)行的結(jié)果，即輸出參數(shù)，如：
CREATE FUNCTION add_em2 (IN x int, IN y int, OUT sum int) AS $$
SELECT $1 + $2
$$ LANGUAGE SQL;
調(diào)用方法和返回結(jié)果與add_em(帶有返回值的函數(shù))完全一致，如：
SELECT add_em(3,7);
這個帶有輸出參數(shù)的函數(shù)和之前的add_em函數(shù)沒有本質(zhì)的區(qū)別。事實上，輸出參數(shù)的真正價值在于它為函數(shù)提供了返回多個字段的途徑。如，
CREATE FUNCTION sum_n_product (x int, y int, OUT sum int, OUT product int) AS $$
SELECT $1 + $2, $1 * $2
$$ LANGUAGE SQL;
調(diào)用方式?jīng)]有改變，只是返回結(jié)果多出一列。
SELECT * FROM sum_n_product(11,42);
sum | product
-----+---------
53 | 462
(1 row)
在上面的示例中，IN用于表示該函數(shù)參數(shù)為輸入?yún)?shù)(缺省值，可以忽略)，OUT則表示該參數(shù)為輸出參數(shù)。
五、返回結(jié)果作為表數(shù)據(jù)源：
所有SQL函數(shù)都可以在查詢的FROM子句里使用。該方法對于返回復(fù)合類型的函數(shù)而言特別有用，如果該函數(shù)定義為返回一個基本類型，那么該函數(shù)生成一個單字段表，如果該函數(shù)定義為返回一個復(fù)合類型，那么該函數(shù)生成一個復(fù)合類型里每個屬性組成的行。見如下示例：
1). 創(chuàng)建一個數(shù)據(jù)表。
CREATE TABLE foo (
fooid int,
foosubid int,
fooname text
);
2). 創(chuàng)建SQL函數(shù)，其返回值為與foo表對應(yīng)的復(fù)合類型。
CREATE FUNCTION getfoo(int) RETURNS foo AS $$
SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;
3). 在FROM子句中調(diào)用該函數(shù)。
SELECT *, upper(fooname) FROM getfoo(1) AS t1;
六、返回集合的SQL函數(shù)：
如果SQL函數(shù)的返回值為SETOF sometype，那么在調(diào)用該函數(shù)時，將返回最后一個SELECT查詢的全部數(shù)據(jù)。這個特性通常用于把函數(shù)放在FROM子句里調(diào)用，見如下示例：
CREATE FUNCTION getfoo(int) RETURNS setof foo AS $$
SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;
在FROM子句中調(diào)用了返回復(fù)合類型集合的函數(shù)，其結(jié)果等同于：SELECT * FROM (SELECT * FROM foo WHERE fooid = 1) t1;
SELECT * FROM getfoo(1) AS t1;
七、多態(tài)的SQL函數(shù)：
SQL函數(shù)可以聲明為接受多態(tài)類型(anyelement和anyarray)的參數(shù)或返回多態(tài)類型的返回值，見如下示例：
1). 函數(shù)參數(shù)和返回值均為多態(tài)類型。
CREATE FUNCTION make_array(anyelement, anyelement) RETURNS anyarray AS $$
SELECT ARRAY[$1, $2];
$$ LANGUAGE SQL;
其調(diào)用方式和調(diào)用其它類型的SQL函數(shù)完全相同，只是在傳遞字符串類型的參數(shù)時，需要顯式轉(zhuǎn)換到目標(biāo)類型，否則將會被視為unknown類型，如：
SELECT make_array(1, 2) AS intarray, make_array('a'::text, 'b') AS textarray;
2). 函數(shù)的參數(shù)為多態(tài)類型，而返回值則為基本類型。
CREATE FUNCTION is_greater(anyelement, anyelement) RETURNS boolean AS $$
SELECT $1 > $2;
$$ LANGUAGE SQL;
3). 多態(tài)類型用于函數(shù)的輸出參數(shù)。
CREATE FUNCTION dup (f1 anyelement, OUT f2 anyelement, OUT f3 anyarray) AS $$
SELECT $1, ARRAY[$1,$1]
$$ LANGUAGE sql;
八、函數(shù)重載：
多個函數(shù)可以定義成相同的函數(shù)名，但是它們的參數(shù)一定要有所區(qū)分。換句話說，函數(shù)名可以重載，此規(guī)則有些類似于面向?qū)ο笳Z言中的函數(shù)重載，見如下示例：
CREATE FUNCTION test(int, real) RETURNS ...
CREATE FUNCTION test(smallint, double) RETURNS ...
由于在PostgreSQL中函數(shù)支持重載，因此在刪除函數(shù)時，也必須指定參數(shù)列表，如：
DROP FUNCTION test(int, real);
DROP FUNCTION test(smallint,double);
相關(guān)文章
postgreSQL 使用timestamp轉(zhuǎn)成date格式
這篇文章主要介紹了postgreSQL 使用timestamp轉(zhuǎn)成date格式，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
這篇文章主要介紹了詳解PostgreSQL 語法中關(guān)鍵字的添加的相關(guān)資料,這里說明下在parser語法解析模塊添加關(guān)鍵字，需要的朋友可以參考下2017-08-08
淺談PostgreSQL中的孤兒文件用法(orphaned data files)
這篇文章主要介紹了淺談PostgreSQL中的孤兒文件用法(orphaned data files)，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
在PostgreSQL中實現(xiàn)數(shù)據(jù)的自動清理和過期清理
在 PostgreSQL 中,可以通過多種方式實現(xiàn)數(shù)據(jù)的自動清理和過期處理,以確保數(shù)據(jù)庫不會因為存儲過多過時或不再需要的數(shù)據(jù)而導(dǎo)致性能下降和存儲空間浪費,本文給大家介紹了一些常見的方法及詳細示例,需要的朋友可以參考下2024-07-07
基于PostgreSQL的時序數(shù)據(jù)庫TimescaleDB的基本用法和概念
時序數(shù)據(jù)是指按照時間順序存儲的數(shù)據(jù),TimescaleDB是一個開源的、擴展了PostgreSQL的時序數(shù)據(jù)庫擴展,本文就給大家詳細的介紹一下基于PostgreSQL的時序數(shù)據(jù)庫TimescaleDB的基本用法和概念,需要的朋友可以參考下2023-06-06
本文將深入剖析PostgreSQL表分區(qū)與繼承的實現(xiàn)機理,結(jié)合最新版本（16版本）的特性演進,通過大量生產(chǎn)級代碼示例,揭示如何設(shè)計高效的分區(qū)方案、優(yōu)化分區(qū)查詢性能,并巧妙運用繼承特性構(gòu)建靈活的數(shù)據(jù)模型,感興趣的朋友一起看看吧2025-04-04
這篇文章主要介紹了淺談PostgreSQL和SQLServer的一些差異，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
這篇文章主要介紹了PostgreSQL中關(guān)閉死鎖進程的方法,本文給出兩種解決這問題的方法,需要的朋友可以參考下2015-02-02
Postgresql去重函數(shù)distinct的用法說明
這篇文章主要介紹了Postgresql去重函數(shù)distinct的用法說明，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
在pg里表分區(qū)是通過表繼承來實現(xiàn)的，一般都是建立一個主表，里面是空，然后每個分區(qū)都去繼承它。2009-09-09