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); 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9521459/viewspace-759320/，如需轉載，請註明出處，否則將追究法律責任。