9.2. 查詢語言（SQL）函數

9.2.1. 範例

看看下面這個簡單的 SQL 函數的範例， 它將用於對一個銀行帳號做扣款（借記消費 debit）動作：

CREATE FUNCTION tp1 (integer, numeric) RETURNS integer AS '
    UPDATE bank 
        SET balance = balance - $2
        WHERE acctountno = $1;
        SELECT 1;'
LANGUAGE 'sql';
     

一個使用者可以象下面這樣用這個函數給帳戶 17 扣款 $100.00：

SELECT tp1( 17,100.0);
     

實際上我們可能喜歡函數有一個比常數 "1" 更有用一些的結果． 所以更有可能的定義是

CREATE FUNCTION tp1 (integer, numeric) RETURNS numeric AS '
    UPDATE bank
        SET balance = balance - $2
        WHERE accountno = $1;
    SELECT balance FROM bank WHERE accountno = $1;
' LANGUAGE SQL;

它修改餘額並傳回新的餘額．

SQL 裡面的任何命令集都可以打成一個包， 做成一個函數．這些命令可以包含資料修改(也就是說， INSERT，UPDATE， 和DELETE)以及 SELECT 查詢． 不過，最後的命令必須是一條傳回函數宣告的傳回類型的 SELECT． 另外，如果你想定義那些執行動作但是不傳回有用的數值的 SQL 函數， 你可以把它定義成傳回 void。這時候它不能以 SELECT 為最後一條語句。比如：

CREATE FUNCTION clean_EMP () RETURNS void AS '
    DELETE FROM EMP
        WHERE EMP.salary <= 0;
' LANGUAGE SQL;

SELECT clean_EMP();

 clean_emp
-----------

(1 row)

9.2.2. 基本類型的 SQL 函數

最簡單的 SQL 函數可能是不帶參數，只是傳回一個基本類型如 integer 的函數：

CREATE FUNCTION one() 
    RETURNS integer
    AS 'SELECT 1 as RESULT;' 
    LANGUAGE 'sql';

SELECT one();

 one
-----
   1
  

注意我們給函數定義了目標列（名稱為 RESULT）， 但是啟動函數的查詢語句的目標列覆蓋了函數的目標 列．因此，結果的標記是one 而不是RESULT．

定義以基本類型為參數的 SQL 函數幾乎一樣簡單， 注意我們在函數內如何用$1和$2使用參數：

CREATE FUNCTION add_em(integer, integer) 
    RETURNS integer
    AS 'SELECT $1 + $2;' 
    LANGUAGE 'sql';

SELECT add_em(1, 2) AS answer;

+-------+
|answer |
+-------+
|3      |
+-------+
     

9.2.3. 復合類型的SQL函數

當我們宣告的函數用復合類型做參數時， 我們不僅要宣告我們需要哪個參數（像上面我們使用 $1和$2一樣），而且要宣告參數的欄位．比如， 假設 EMP 是一個包含雇員資訊的表，並且因此也是該表每行 的復合類型的名字．這裡就是一個函數 double_salary，它計算你薪水翻番之後的數值：

CREATE FUNCTION double_salary(EMP) RETURNS integer AS '
    SELECT $1.salary * 2 AS salary;
'LANGUAGE SQL;

SELECT name, double_salary(EMP) AS dream
    FROM EMP
    WHERE EMP.cubicle ~= point '(2,1)';

 name | dream
------+-------
 Sam  |  2400

請注意這裡使用 $1.salary 的語法 選擇參數行數值的一個欄位．還要注意SELECT命令是如何 使用一個表的名字表示該表的整個目前行作為復合數值．

我們也可以寫一個傳回復合類型的函數． 下面是一個傳回一行 EMP 函數的範例︰

CREATE FUNCTION new_emp() RETURNS EMP AS '
    SELECT text ''None'' AS name,
        1000 AS salary,
        25 AS age,
        point ''(2,2)'' AS cubicle'
    LANGUAGE 'sql';

在這個範例中我們給每個欄位都賦予了一個常數， 當然我們可以用任何計算或表達式來代替這些常數． 注意定義這樣的函數的兩個重要的問題︰

• 目標清單的順序必須和與該復合類型相關的表中欄位的順序完全一樣． (象我們上面那樣給欄位的命名和系統無關．)

• 你必須對表達式進行類型轉換以相符復合類型的定義． 否則你將看到下面的錯誤資訊：

  ERROR:  function declared to return emp returns varchar instead of text at column 1

傳回一行（復合類型）的函數可以用作一個表函數，象下面描述地那樣。 我們還可以在 SQL 表達式的環境裡調用它，但是只有在你從該行中 抽取一個欄位或者把整個行傳遞給另外一個接受同樣復合類型的函數中 才可以。比如，

SELECT (new_emp()).name;

 name
------
 None

我們需要一個額外的圓括弧以防止分析器誤解︰

SELECT new_emp().name;
ERROR:  parser: parse error at or near "."

另外一個選擇是使用函數表示法進行欄位抽取．解釋這些問題的簡單方法是 我們通常交互使用attribute(table)和 table.attribute 的表示法︰

SELECT name(new_emp());

 name
------
 None

--
-- 下面的與這句話相同︰
--  SELECT EMP.name AS youngster FROM EMP WHERE EMP.age < 30
--
SELECT name(EMP) AS youngster
    FROM EMP
    WHERE age(EMP) < 30;

 youngster
-----------
 Sam

另外一個使用函數傳回行結果的方法是宣告另外一個函數， 該函數接受一個行類型參數，然後把函數結果傳遞給這個第二個函數︰

CREATE FUNCTION getname(emp) RETURNS text AS
'SELECT $1.name;'
LANGUAGE SQL;

SELECT getname(new_emp());
 getname
---------
 None
(1 row)

9.2.4. SQL 表函數

一個表函數就是一個可以在查詢的 FROM 幾句裡使用的函數。 所有的 SQL 語言函數都可以用這種方法使用，但是它對於傳回復合類型的函數特別 有用。如果該函數定義為傳回一個基本類型，那麼表函數生成一個單欄位表。 如果該函數定義為傳回一個復合類型，那麼該表函數生成一個復合類型裡每個欄位 組成的行。

這裡是一個範例：

CREATE TABLE foo (fooid int, foosubid int, fooname text);
INSERT INTO foo VALUES(1,1,'Joe');
INSERT INTO foo VALUES(1,2,'Ed');
INSERT INTO foo VALUES(2,1,'Mary');

CREATE FUNCTION getfoo(int) RETURNS foo AS '
    SELECT * FROM foo WHERE fooid = $1;
' LANGUAGE SQL;

SELECT *, upper(fooname) FROM getfoo(1) AS t1;

 fooid | foosubid | fooname | upper
-------+----------+---------+-------
     1 |        1 | Joe     | JOE
(2 rows)

如這個範例顯示的那樣，我們可以象對待一個普通表的欄位一樣對待 函數的結果欄位。

請注意我們只從該函數中獲取了一行。這是因為我們沒有說 SETOF。

9.2.5. 傳回集合的 SQL 函數

如果一個 SQL 函數宣告為傳回 SETOF sometype． 這時候，該函數的最後的SELECT查詢一直執行到結束，並且它 輸出的每行都當做該集合的一個元素傳回．

這個特性通常用於把函數當作表函數調用。這個時候函數傳回的每一行 都成為查詢可見的該表的一行。比如，假設表 foo 有著和 上面一樣的內容，而我們說：

CREATE FUNCTION getfoo(int) RETURNS setof foo AS '
    SELECT * FROM foo WHERE fooid = $1;
' LANGUAGE SQL;

SELECT * FROM getfoo(1) AS t1;

 fooid | foosubid | fooname
-------+----------+---------
     1 |        1 | Joe
     1 |        2 | Ed
(2 rows)

目前，傳回集合的函數也可以在一個 SELECT 查詢的目標清單裡 調用。對於該 SELECT 自己生成的每一行，都會調用這個傳回集合 的函數，並且相對該函數的結果集中的每個元素都會生成一個輸出行。不過， 請注意，這個功能已經廢棄了，在將來的版本中可能會被刪除。下面就是一個 在目標清單中使用傳回集合的函數的範例：

CREATE FUNCTION listchildren(text) RETURNS SETOF text AS
'SELECT name FROM nodes WHERE parent = $1'
LANGUAGE SQL;

SELECT * FROM nodes;
   name    | parent
-----------+--------
 Top       |
 Child1    | Top
 Child2    | Top
 Child3    | Top
 SubChild1 | Child1
 SubChild2 | Child1
(6 rows)

SELECT listchildren('Top');
 listchildren
--------------
 Child1
 Child2
 Child3
(3 rows)

SELECT name, listchildren(name) FROM nodes;
  name  | listchildren
--------+--------------
 Top    | Child1
 Top    | Child2
 Top    | Child3
 Child1 | SubChild1
 Child1 | SubChild2
(5 rows)

在最後的SELECT裡，請注意沒有出現Child2， Child3等的行． 這是因為listchildren 為這些輸入傳回一個空集合， 因此不生成任何輸出行．