31.4. 查詢語言（SQL）函數

31.4.1. 基本類型上的 SQL 函數

最簡單的 SQL 函數可能就是沒有參數並且返回一個 基本類型，比如 integer 的函數：

CREATE FUNCTION one() RETURNS integer AS $$
    SELECT 1 AS result;
$$ LANGUAGE SQL;

-- 另外一種字串文本的語法：
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

下面是一個更有用的函數，我們可以用它對一個銀行帳號做扣款（借記消費 debit）動作：

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

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

SELECT tf1( 17,100.0);

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

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;

它修改餘額並返回新的餘額。

31.4.2. 復合類型的SQL函數

當我們書寫使用用復合類型做參數的函數時， 我們不僅要聲明我們需要哪個參數（像上面我們使用 $1和$2一樣），而且要聲明參數的字串（資料域）。比如， 假設 emp 是一個包含僱員訊息的資料表，並且因此也是該資料表每行的復合類型的名字。這裡就是一個函數 double_salary，它計算某人薪水翻番之後的數值：

CREATE TABLE emp (
    name        text,
    salary      numeric,
    age         integer,
    cubicle     point
);

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
------+-------
 Bill |  8400

請注意這裡使用 $1.salary 的語法選擇參數行數值的一個字串。 還要注意SELECT命令是如何使用 * 資料表示該資料表的整個目前行作為復合數值。 資料表裡面的行也可以用資料表名字引用，像下面這樣：

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

不過這個用法已經廢棄了，因為很容易混淆。

有時候我們動態地構造一個復合參數值很有用。 我們可以用 ROW 構造器實現這個功能。 比如，我們可以調節傳遞給函數的資料：

SELECT name, double_salary(ROW(name, salary*1.1, age, cubicle)) AS dream
    FROM emp;

我們也可以寫一個返回復合類型的函數。 下面是一個返回一行 emp 函數的例子：

CREATE FUNCTION new_emp() RETURNS emp AS $$
    SELECT text 'None' AS name,
        1000.0 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

另外一個定義同樣函數的方法是：

CREATE FUNCTION new_emp() RETURNS emp AS $$
    SELECT ROW('None', 1000.0, 25, '(2,2)')::emp;
$$ LANGUAGE SQL;

這裡我們寫一個置返回一個對應復合類型字串的 SELECT。 在這種情況下，這麼做並沒有任何好處，但是它在某些場合是一個很好用的東西 — 比如，如果我們需要透過調用另外一個返回需要的復合類型數值的函數來計算結果。

我們可以用兩種方法來直接調用這個函數：

SELECT new_emp();

         new_emp
--------------------------
 (None,1000.0,25,"(2,2)")

SELECT * FROM new_emp();

 name | salary | age | cubicle
------+--------+-----+---------
 None | 1000.0 |  25 | (2,2)

第二種方法在 Section 31.4.3 裡有更完整的描述。

在使用一個返回復合類型的函數的時候，您可能想從結果中只抽取一個字串。 您可以用下面的語法來做：

SELECT (new_emp()).name;

 name
------
 None

我們需要一個額外的圓括弧以防止分析器誤解。 如果您想省略這對括弧，您會看見類似下面這樣的東西：

SELECT new_emp().name;
ERROR:  syntax error at or near "." at character 17
LINE 1: SELECT new_emp().name;
                         ^

另外一個選擇是使用函數資料表示法進行字串抽取。解釋這些問題的簡單方法是我們通常交互使用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
 Andy

另外一個使用函數返回行結果的情況是把結果傳遞給另外一個該行類型輸入的函數：

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

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

另外一個把使用返回復合類型的函數的方法是把它當作一個資料表函數使用，如下所述。

31.4.3. 當做資料表資料源的 SQL 函數

所有 SQL 函數都可以在查詢的 FROM 子句裡使用。 但是它對於返回復合類型的函數特別有用。如果該函數定義為返回一個基本類型， 那麼資料表函數生成一個單字串資料表。如果該函數定義為返回一個復合類型，那麼該資料表函數生成一個復合類型裡每個屬性組成的行。

這裡是一個例子：

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。 這個問題我們在下一節描述。

31.4.4. 返回集合的 SQL 函數

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

這個特性通常用於把函數放在FROM子句裡調用的時候。 這個時候函數返回的每一行都成為查詢可見的該資料表的一行。 比如，假設資料表 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)

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

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 為這些參數返回一個空集合， 因此不生成任何結果行。

31.4.5. 多態的 SQL 函數

SQL 函數可以聲明為接受並返回多態的類型 anyelement 和 anyarray。參閱 Section 31.2.5 獲取有關多態函數的更多細節。 這裡是一個多態的函數 make_array，它從兩個任意資料類型元素中建立一個數組：

CREATE FUNCTION make_array(anyelement, anyelement) RETURNS anyarray AS $$
    SELECT ARRAY[$1, $2];
$$ LANGUAGE SQL;

SELECT make_array(1, 2) AS intarray, make_array('a'::text, 'b') AS textarray;
 intarray | textarray
----------+-----------
 {1,2}    | {a,b}
(1 row)

請注意我們使用了類型轉換 'a'::text 聲明參數是類型 text。 如果參數只是一個字串文本，這是要求的動作，否則它就會被當作類型 unknown， 並且 unknown 不是一種有效的類型。如果沒有類型轉換，那麼就會看到類似下面這樣的錯誤訊息：

ERROR:  could not determine "anyarray"/"anyelement" type because input has type "unknown"

PostgreSQL 允許有多態的參數的函數返回一個固定類型，但是反過來不行。比如：

CREATE FUNCTION is_greater(anyelement, anyelement) RETURNS boolean AS $$
    SELECT $1 > $2;
$$ LANGUAGE SQL;

SELECT is_greater(1, 2);
 is_greater
------------
 f
(1 row)

CREATE FUNCTION invalid_func() RETURNS anyelement AS $$
    SELECT 1;
$$ LANGUAGE SQL;
ERROR:  cannot determine result data type
DETAIL:  A function returning "anyarray" or "anyelement" must have at least one argument of either type.