PostgreSQL學習手冊(SQL語言函數)

一、基本概念：

    SQL函數可以包含任意數量的查詢，但是函數只返回最後一個查詢(必須是SELECT)的結果。在簡單情況下，返回最後一條查詢結果的第一行。如果最後一個查詢不返回任何行，那麼該函數將返回NULL值。如果需要該函數返回最後一條SELECT語句的所有行，可以將函數的傳回值定義為集合，即SETOF sometype。
    SQL函數的函數體應該是用分號分隔的SQL語句列表，其中最後一條語句之後的分號是可選的。除非函式宣告為返回void，否則最後一條語句必須是SELECT。事實上，在SQL函數中，不僅可以包含SELECT查詢語句，也可以包含INSERT、UPDATE和DELETE等其他標準的SQL語句，但是和事物相關的語句不能包含其中，如BEGIN、COMMIT、ROLLBACK和SAVEPOINT等。
    CREATE FUNCTION命令的文法要求函數體寫成一個字串文本。通常來說，該文本字串常量使用美元符($$)圍住，如：

代碼如下	複製代碼
CREATE FUNCTION clean_emp() RETURNS void AS $$         DELETE FROM emp WHERE salary < 0;     $$ LANGUAGE SQL;

    最後需要說明的是SQL函數中的參數，PostgreSQL定義$1表示第一個參數，$2為第二個參數並以此類推。如果參數是複合類型，則可以使用點標記法，即$1.name訪問複合型別參數中的name欄位。需要注意的是函數參數只能用作資料值，而不能用於標識符，如：

代碼如下	複製代碼
INSERT INTO mytable VALUES ($1);   --合法     INSERT INTO $1 VALUES (42);          --不合法(表名屬於標示符之一)

   
二、基本類型：
   
    最簡單的SQL函數可能就是沒有參數且返回基本類型的函數了，如：

代碼如下	複製代碼
CREATE FUNCTION one() RETURNS integer AS $$         SELECT 1 AS result;     $$ LANGUAGE SQL;

    下面的例子聲明了基本類型作為函數的參數。
  

代碼如下	複製代碼
CREATE FUNCTION add_em(integer, integer) RETURNS integer AS $$         SELECT $1 + $2;     $$ LANGUAGE SQL;     # 通過select調用函數。     postgres=# SELECT add_em(1,2) AS answer;      answer     --------

          3
    (1 row)       
    在下面的例子中，函數體內包含多個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;

   
三、複合類型：

    見如下樣本：
    1). 建立資料表，這樣與之對應的複合類型也隨之產生。
   

代碼如下	複製代碼
CREATE TABLE emp (         name        text,         salary      numeric,         age         integer,     );

    2). 建立函數，其參數為複合類型。在函數體內，可以像引用基本型別參數那樣引用複合類型，如$1。訪問複合類型的欄位使用點運算式即可，如：$1.salary。
   

代碼如下	複製代碼
CREATE FUNCTION double_salary(emp) RETURNS integer AS $$         SELECT ($1.salary * 2)::integer AS salary;     $$ LANGUAGE SQL;

    3). 在select語句中，可以使用emp.*表示emp表的一整行資料。
   

代碼如下	複製代碼
SELECT name, double_salary(emp.*) AS dream FROM emp WHERE age > 30;

    4). 我們也可以使用ROW運算式構造自訂的複合類型，如：
   

代碼如下	複製代碼
SELECT name, double_salary(ROW(name, salary*1.1, age)) AS dream FROM emp;

    5). 建立一個函數，其傳回值為複合類型，如：
  

代碼如下	複製代碼
CREATE FUNCTION new_emp() RETURNS emp AS $$         SELECT ROW('None', 1000.0, 25)::emp;     $$ LANGUAGE SQL;

    6). 調用返回複合類型的函數。
   

代碼如下	複製代碼
SELECT new_emp();

    7). 調用返回複合類型的函數，同時訪問該傳回值的某個欄位。
   

代碼如下	複製代碼
SELECT (new_emp()).name;

四、帶輸出參數的函數：

    還有一種方法可以用於返回函數執行的結果，即輸出參數，如：
   

代碼如下	複製代碼
CREATE FUNCTION add_em2 (IN x int, IN y int, OUT sum int) AS $$         SELECT $1 + $2     $$ LANGUAGE SQL;

    調用方法和返回結果與add_em(帶有傳回值的函數)完全一致，如：
   

代碼如下	複製代碼
SELECT add_em(3,7);

    
    這個帶有輸出參數的函數和之前的add_em函數沒有本質的區別。事實上，輸出參數的真正價值在於它為函數提供了返回多個欄位的途徑。如，
   

代碼如下	複製代碼
CREATE FUNCTION sum_n_product (x int, y int, OUT sum int, OUT product int) AS $$         SELECT $1 + $2, $1 * $2     $$ LANGUAGE SQL;

    調用方式沒有改變，只是返回結果多出一列。

代碼如下	複製代碼
SELECT * FROM sum_n_product(11,42);      sum | product     -----+---------       53 |     462     (1 row)

    在上面的樣本中，IN用於表示該函數參數為輸入參數(預設值，可以忽略)，OUT則表示該參數為輸出參數。

五、返回結果作為表資料來源：

    所有SQL函數都可以在查詢的FROM子句裡使用。該方法對於返回複合類型的函數而言特別有用，如果該函數定義為返回一個基本類型，那麼該函數產生一個單欄位表，如果該函數定義為返回一個複合類型，那麼該函數產生一個複合類型裡每個屬性群組成的行。見如下樣本：
    1). 建立一個資料表。
   

代碼如下	複製代碼
CREATE TABLE foo (         fooid    int,         foosubid int,         fooname  text     );

    2). 建立SQL函數，其傳回值為與foo表對應的複合類型。
   

代碼如下	複製代碼
CREATE FUNCTION getfoo(int) RETURNS foo AS $$         SELECT * FROM foo WHERE fooid = $1;     $$ LANGUAGE SQL;

    3). 在FROM子句中調用該函數。
  

代碼如下	複製代碼
SELECT *, upper(fooname) FROM getfoo(1) AS t1;

   
六、返回集合的SQL函數：

    如果SQL函數的傳回值為SETOF sometype，那麼在調用該函數時，將返回最後一個SELECT查詢的全部資料。這個特性通常用於把函數放在FROM子句裡調用，見如下樣本：
  

代碼如下	複製代碼
CREATE FUNCTION getfoo(int) RETURNS setof foo AS $$         SELECT * FROM foo WHERE fooid = $1;     $$ LANGUAGE SQL;

 
    在FROM子句中調用了返回複合類型集合的函數，其結果等同於：

代碼如下	複製代碼
SELECT * FROM (SELECT * FROM foo WHERE fooid = 1) t1;     SELECT * FROM getfoo(1) AS t1;

 
   
七、多態的SQL函數：

    SQL函數可以聲明為接受多態類型(anyelement和anyarray)的參數或返回多態類型的傳回值，見如下樣本：
    1). 函數參數和傳回值均為多態類型。

代碼如下	複製代碼
CREATE FUNCTION make_array(anyelement, anyelement) RETURNS anyarray AS $$         SELECT ARRAY[$1, $2];     $$ LANGUAGE SQL;

    其調用方式和調用其它類型的SQL函數完全相同，只是在傳遞字串類型的參數時，需要顯式轉換到目標類型，否則將會被視為unknown類型，如：
   

代碼如下	複製代碼
SELECT make_array(1, 2) AS intarray, make_array('a'::text, 'b') AS textarray;

    2). 函數的參數為多態類型，而傳回值則為基本類型。

代碼如下	複製代碼
CREATE FUNCTION is_greater(anyelement, anyelement) RETURNS boolean AS $$         SELECT $1 > $2;     $$ LANGUAGE SQL;

    3). 多態類型用於函數的輸出參數。
 

代碼如下	複製代碼
CREATE FUNCTION dup (f1 anyelement, OUT f2 anyelement, OUT f3 anyarray) AS $$         SELECT $1, ARRAY[$1,$1]     $$ LANGUAGE sql;

八、函數重載：

    多個函數可以定義成相同的函數名，但是它們的參數一定要有所區分。換句話說，函數名可以重載，此規則有些類似於物件導向語言中的函數重載，見如下樣本：

代碼如下	複製代碼
CREATE FUNCTION test(int, real) RETURNS ...     CREATE FUNCTION test(smallint, double) RETURNS ...

    由於在PostgreSQL中函數支援重載，因此在刪除函數時，也必須指定參數列表，如：

代碼如下	複製代碼
DROP FUNCTION test(int, real);     DROP FUNCTION test(smallint,double);