pg資料庫基本操作

一、概述：

    PL/pgSQL函數在第一次被調用時，其函數内的源代碼(文本)将被解析為二進制指令樹，但是函數内的表達式和SQL指令隻有在首次用到它們的時候，PL/pgSQL解釋器才會為其建立一個準備好的執行規劃，随後對該表達式或SQL指令的通路都将使用該規劃。如果在一個條件語句中，有部分SQL指令或表達式沒有被用到，那麼PL/pgSQL解釋器在本次調用中将不會為其準備執行規劃，這樣的好處是可以有效地減少為PL/pgSQL函數裡的語句生成分析和執行規劃的總時間，然而缺點是某些表達式或SQL指令中的錯誤隻有在其被執行到的時候才能發現。

    由于PL/pgSQL在函數裡為一個指令制定了執行計劃，那麼在本次會話中該計劃将會被反複使用，這樣做往往可以得到更好的性能，但是如果你動态修改了相關的資料庫對象，那麼就有可能産生問題，如：

    CREATE FUNCTION populate() RETURNS integer AS $$

    DECLARE

        -- 聲明段

    BEGIN

        PERFORM my_function();

    END;  www.2cto.com  

    $$ LANGUAGE plpgsql;

    在調用以上函數時，PERFORM語句的執行計劃将引用my_function對象的OID。在此之後，如果你重建了my_function函數，那麼populate函數将無法再找到原有my_function函數的OID。要解決該問題，可以選擇重建populate函數，或者重新登入建立新的會話，以使PostgreSQL重新編譯該函數。要想規避此類問題的發生，在重建my_function時可以使用CREATE OR REPLACE FUNCTION指令。

    鑒于以上規則，在PL/pgSQL裡直接出現的SQL指令必須在每次執行時均引用相同的表和字段，換句話說，不能将函數的參數用作SQL指令的表名或字段名。如果想繞開該限制，可以考慮使用PL/pgSQL中的EXECUTE語句動态地構造指令，由此換來的代價是每次執行時都要構造一個新的命×××。

    使用PL/pgSQL函數的一個非常重要的優勢是可以提高程式的執行效率，由于原有的SQL調用不得不在用戶端與伺服器之間反複傳遞資料，這樣不僅增加了程序間通訊所産生的開銷，而且也會大大增加網絡IO的開銷。

二、PL/pgSQL的結構：

    PL/pgSQL是一種塊結構語言，函數定義的所有文本都必須在一個塊内，其中塊中的每個聲明和每條語句都是以分号結束，如果某一子塊在另外一個塊内，那麼該子塊的END關鍵字後面必須以分号結束，不過對于函數體的最後一個END關鍵字，分号可以省略，如：

    [ <<label>> ]

    [ DECLARE declarations ]

    BEGIN  www.2cto.com  

        statements

    END [ label ];

    在PL/pgSQL中有兩種注釋類型，雙破折号(--)表示單行注釋。/* */表示多行注釋，該注釋類型的規則等同于C語言中的多行注釋。

    在語句塊前面的聲明段中定義的變量在每次進入語句塊(BEGIN)時都會将聲明的變量初始化為它們的預設值，而不是每次函數調用時初始化一次。如：

    CREATE FUNCTION somefunc() RETURNS integer AS $$

       quantity integer := 30;

       RAISE NOTICE 'Quantity here is %', quantity;      --在這裡的數量是30

       quantity := 50;

       --

       -- 建立一個子塊

       DECLARE

          quantity integer := 80;

       BEGIN

          RAISE NOTICE 'Quantity here is %', quantity;   --在這裡的數量是80

       END;

       RAISE NOTICE 'Quantity here is %', quantity;      --在這裡的數量是50    

       RETURN quantity;

    #執行該函數以進一步觀察其執行的結果。

    postgres=# select somefunc();

    NOTICE:  Quantity here is 30

    NOTICE:  Quantity here is 80

    NOTICE:  Quantity here is 50

     somefunc

    ----------

           50

    (1 row)

    最後需要說明的是，目前版本的PostgreSQL并不支援嵌套事務，函數中的事物總是由外層指令(函數的調用者)來控制的，它們本身無法開始或送出事務。

三、聲明：

    所有在塊裡使用的變量都必須在塊的聲明段裡先進行聲明，唯一的例外是FOR循環裡的循環計數變量，該變量被自動聲明為整型。變量聲明的文法如下：

    variable_name [ CONSTANT ] variable_type [ NOT NULL ] [ { DEFAULT | := } expression ];

    1). SQL中的資料類型均可作為PL/pgSQL變量的資料類型，如integer、varchar和char等。

    2). 如果給出了DEFAULT子句，該變量在進入BEGIN塊時将被初始化為該預設值，否則被初始化為SQL空值。預設值是在每次進入該塊時進行計算的。是以，如果把now()賦予一個類型為timestamp的變量，那麼該變量的預設值将為函數實際調用時的時間，而不是函數預編譯時的時間。

    3). CONSTANT選項是為了避免該變量在進入BEGIN塊後被重新指派，以保證該變量為常量。

    4). 如果聲明了NOT NULL，那麼賦予NULL數值給該變量将導緻一個運作時錯誤。是以所有聲明為NOT NULL的變量也必須在聲明時定義一個非空的預設值。

    1. 函數參數的别名：

    傳遞給函數的參數都是用$1、$2這樣的辨別符來表示的。為了增加可讀性，我們可以為其聲明别名。之後别名和數字辨別符均可指向該參數值，見如下示例：

    1). 在函數聲明的同時給出參數變量名。

    CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$ 

        RETURN subtotal * 0.06;

    END;

    2). 在聲明段中為參數變量定義别名。

    CREATE FUNCTION sales_tax(REAL) RETURNS real AS $$

        subtotal ALIAS FOR $1;

    3). 對于輸出參數而言，我們仍然可以遵守1)和2)中的規則。

    CREATE FUNCTION sales_tax(subtotal real, OUT tax real) AS $$

        tax := subtotal * 0.06;

    $$ LANGUAGE plpgsql;    

    4). 如果PL/pgSQL函數的傳回類型為多态類型(anyelement或anyarray)，那麼函數就會建立一個特殊的參數：$0。我們仍然可以為該變量設定别名。

    CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement)

    RETURNS anyelement AS $$

        result ALIAS FOR $0;

        result := v1 + v2 + v3;

        RETURN result;

    2. 拷貝類型：

    見如下形式的變量聲明：

    variable%TYPE

    %TYPE表示一個變量或表字段的資料類型，PL/pgSQL允許通過該方式聲明一個變量，其類型等同于variable或表字段的資料類型，見如下示例：

    user_id users.user_id%TYPE;

    在上面的例子中，變量user_id的資料類型等同于users表中user_id字段的類型。

    通過使用%TYPE，一旦引用的變量類型今後發生改變，我們也無需修改該變量的類型聲明。最後需要說明的是，我們可以在函數的參數和傳回值中使用該方式的類型聲明。

    3. 行類型：

    name table_name%ROWTYPE;

    name composite_type_name;

    table_name%ROWTYPE表示指定表的行類型，我們在建立一個表的時候，PostgreSQL也會随之建立出一個與之相應的複合類型，該類型名等同于表名，是以，我們可以通過以上兩種方式來聲明行類型的變量。由此方式聲明的變量，可以儲存SELECT傳回結果中的一行。如果要通路變量中的某個域字段，可以使用點表示法，如rowvar.field，但是行類型的變量隻能通路自定義字段，無法通路系統提供的隐含字段，如OID等。對于函數的參數，我們隻能使用複合類型辨別變量的資料類型。最後需要說明的是，推薦使用%ROWTYPE的聲明方式，這樣可以具有更好的可移植性，因為在Oracle的PL/SQL中也存在相同的概念，其聲明方式也為%ROWTYPE。見如下示例：

    CREATE FUNCTION merge_fields(t_row table1) RETURNS text AS $$

        t2_row table2%ROWTYPE;

        SELECT * INTO t2_row FROM table2 WHERE id = 1 limit 1;

        RETURN t_row.f1 || t2_row.f3 || t_row.f5 || t2_row.f7;

    4. 記錄類型：

    name RECORD;

    記錄變量類似于行類型變量，但是它們沒有預定義的結構，隻能通過SELECT或FOR指令來擷取實際的行結構，是以記錄變量在被初始化之前無法通路，否則将引發運作時錯誤。

    注：RECORD不是真正的資料類型，隻是一個占位符。

四、基本語句：

    1. 指派：

    PL/pgSQL中指派語句的形式為：identIFier := expression，等号兩端的變量和表達式的類型或者一緻，或者可以通過PostgreSQL的轉換規則進行轉換，否則将會導緻運作時錯誤，見如下示例：

    user_id := 20;

    tax := subtotal * 0.06;

    2. SELECT INTO：

    通過該語句可以為記錄變量或行類型變量進行指派，其表現形式為：SELECT INTO target select_expressions FROM ...，該指派方式一次隻能指派一個變量。表達式中的target可以表示為是一個記錄變量、行變量，或者是一組用逗号分隔的簡單變量和記錄/行字段的清單。select_expressions以及剩餘部分和普通SQL一樣。

    如果将一行或者一個變量清單用做目标，那麼選出的數值必需精确比對目标的結構，否則就會産生運作時錯誤。如果目标是一個記錄變量，那麼它自動将自己構造成指令結果列的行類型。如果指令傳回零行，目标被賦予空值。如果指令傳回多行，那麼将隻有第一行被賦予目标，其它行将被忽略。在執行SELECT INTO語句之後，可以通過檢查内置變量FOUND來判斷本次指派是否成功，如：

    SELECT INTO myrec * FROM emp WHERE empname = myname;

    IF NOT FOUND THEN

        RAISE EXCEPTION 'employee % not found', myname;

    END IF;

    要測試一個記錄/行結果是否為空，可以使用IS NULL條件進行判斷，但是對于傳回多條記錄的情況則無法判斷，如：

        users_rec RECORD;

        SELECT INTO users_rec * FROM users WHERE user_id = 3;

        IF users_rec.homepage IS NULL THEN

            RETURN 'http://';

        END IF;

    3. 執行一個沒有結果的表達式或者指令：

    在調用一個表達式或執行一個指令時，如果對其傳回的結果不感興趣，可以考慮使用PERFORM語句：PERFORM query，該語句将執行PERFORM之後的指令并忽略其傳回的結果。其中query的寫法和普通的SQL SELECT指令是一樣的，隻是把開頭的關鍵字SELECT替換成PERFORM，如：

    PERFORM create_mv('cs_session_page_requests_mv', my_query);

    4. 執行動态指令：

    如果在PL/pgSQL函數中操作的表或資料類型在每次調用該函數時都可能會發生變化，在這樣的情況下，可以考慮使用PL/pgSQL提供的EXECUTE語句：EXECUTE command-string [ INTO target ]，其中command-string是用一段文本表示的表達式，它包含要執行的指令。而target是一個記錄變量、行變量或者一組用逗号分隔的簡單變量和記錄/行域的清單。這裡需要特别注意的是，該指令字元串将不會發生任何PL/pgSQL變量代換，變量的數值必需在構造指令字元串時插入到該字元串中。

    和所有其它PL/pgSQL指令不同的是，一個由EXECUTE語句運作的指令在伺服器内并不會隻prepare和儲存一次。相反，該語句在每次運作的時候，指令都會prepare一次。是以指令字元串可以在函數裡動态的生成以便于對各種不同的表和字段進行操作，進而提高函數的靈活性。然而由此換來的卻是性能上的折損。見如下示例：

    EXECUTE 'UPDATE tbl SET ' || quote_ident(columnname) || ' = ' || quote_literal(newvalue);   www.2cto.com  

五、控制結構：

    1. 函數傳回：

    1). RETURN expression

    該表達式用于終止目前的函數，然後再将expression的值傳回給調用者。如果傳回簡單類型，那麼可以使用任何表達式，同時表達式的類型也将被自動轉換成函數的傳回類型，就像我們在指派中描述的那樣。如果要傳回一個複合類型的數值，則必須讓表達式傳回記錄或者比對的行變量。

    2). RETURN NEXT expression

    如果PL/pgSQL函數聲明為傳回SETOF sometype，其行記錄是通過RETURN NEXT指令進行填充的，直到執行到不帶參數的RETURN時才表示該函數結束。是以對于RETURN NEXT而言，它實際上并不從函數中傳回，隻是簡單地把表達式的值儲存起來，然後繼續執行PL/pgSQL函數裡的下一條語句。随着RETURN NEXT指令的疊代執行，結果集最終被建立起來。該類函數的調用方式如下：

    SELECT * FROM some_func();

    它被放在FROM子句中作為資料源使用。最後需要指出的是，如果結果集數量很大，那麼通過該種方式來建構結果集将會導緻極大的性能損失。

    2. 條件：

    在PL/pgSQL中有以下三種形式的條件語句。

    1). IF-THEN

    IF boolean-expression THEN

    END IF;    

    2). IF-THEN-ELSE

    ELSE

    3). IF-THEN-ELSIF-ELSE

    ELSIF boolean-expression THEN

    ELSE  www.2cto.com  

    關于條件語句，這裡就不在做過多的贅述了。

    3. 循環：

    1). LOOP

    LOOP

    END LOOP [ label ];

    LOOP定義一個無條件的循環，直到由EXIT或者RETURN語句終止。可選的label可以由EXIT和CONTINUE語句使用，用于在嵌套循環中聲明應該應用于哪一層循環。 

    2). EXIT

    EXIT [ label ] [ WHEN expression ];

    如果沒有給出label，就退出最内層的循環，然後執行跟在END LOOP後面的語句。如果給出label，它必須是目前或更高層的嵌套循環塊或語句塊的标簽。之後該命名塊或循環就會終止，而控制則直接轉到對應循環/塊的END語句後面的語句上。

    如果聲明了WHEN，EXIT指令隻有在expression為真時才被執行，否則将直接執行EXIT後面的語句。見如下示例：

        -- do something

        EXIT WHEN count > 0;

    END LOOP;

    3). CONTINUE

    CONTINUE [ label ] [ WHEN expression ];

    如果沒有給出label，CONTINUE就會跳到最内層循環的開始處，重新進行判斷，以決定是否繼續執行循環内的語句。如果指定label，則跳到該label所在的循環開始處。如果聲明了WHEN，CONTINUE指令隻有在expression為真時才被執行，否則将直接執行CONTINUE後面的語句。見如下示例：

        EXIT WHEN count > 100;

        CONTINUE WHEN count < 50;

    END LOOP;    

    4). WHILE

    WHILE expression LOOP

    隻要條件表達式為真，其塊内的語句就會被循環執行。條件是在每次進入循環體時進行判斷的。見如下示例：

    WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP

        --do something

    5). FOR

    FOR name IN [ REVERSE ] expression .. expression LOOP

    變量name自動被定義為integer類型，其作用域僅為FOR循環的塊内。表示範圍上下界的兩個表達式隻在進入循環時計算一次。每次疊代name值自增1，但如果聲明了REVERSE，name變量在每次疊代中将自減1，見如下示例：

    FOR i IN 1..10 LOOP

        RAISE NOTICE 'i IS %', i;

    END LOOP;  www.2cto.com  

    FOR i IN REVERSE 10..1 LOOP

    4. 周遊指令結果：

    FOR record_or_row IN query LOOP

    這是另外一種形式的FOR循環，在該循環中可以周遊指令的結果并操作相應的資料，見如下示例：

    FOR rec IN SELECT * FROM some_table LOOP

        PERFORM some_func(rec.one_col);

    PL/pgSQL還提供了另外一種周遊指令結果的方式，和上面的方式相比，唯一的差别是該方式将SELECT語句存于字元串文本中，然後再交由EXECUTE指令動态的執行。和前一種方式相比，該方式的靈活性更高，但是效率較低。

    FOR record_or_row IN EXECUTE text_expression LOOP

    5. 異常捕獲：

    在PL/pgSQL函數中，如果沒有異常捕獲，函數會在發生錯誤時直接退出，與其相關的事物也會随之復原。我們可以通過使用帶有EXCEPTION子句的BEGIN塊來捕獲異常并使其從中恢複。見如下聲明形式：

    [ DECLARE

        declarations ]

    EXCEPTION

        WHEN condition [ OR condition ... ] THEN

            handler_statements

    如果沒有錯誤發生，隻有BEGIN塊中的statements會被正常執行，然而一旦這些語句中有任意一條發生錯誤，其後的語句都将被跳過，直接跳轉到EXCEPTION塊的開始處。此時系統将搜尋異常條件清單，尋找比對該異常的第一個條件，如果找到比對，則執行相應的handler_statements，之後再執行END的下一條語句。如果沒有找到比對，該錯誤就會被繼續向外抛出，其結果與沒有EXCEPTION子句完全等同。如果此時handler_statements中的語句發生新錯誤，它将不能被該EXCEPTION子句捕獲，而是繼續向外傳播，交由其外層的EXCEPTION子句捕獲并處理。見如下示例：

    INSERT INTO mytab(firstname, lastname) VALUES('Tom', 'Jones');

        UPDATE mytab SET firstname = 'Joe' WHERE lastname = 'Jones';

        x := x + 1;

        y := x / 0;

        WHEN division_by_zero THEN

            RAISE NOTICE 'caught division_by_zero';

            RETURN x;

    當以上函數執行到y := x / 0語句時，将會引發一個異常錯誤，代碼将跳轉到EXCEPTION塊的開始處，之後系統會尋找比對的異常捕捉條件，此時division_by_zero完全比對，這樣該條件内的代碼将會被繼續執行。需要說明的是，RETURN語句中傳回的x值為x := x + 1執行後的新值，但是在除零之前的update語句将會被復原，BEGIN之前的insert語句将仍然生效。

六、遊标：

    1. 聲明遊标變量：

    在PL/pgSQL中對遊标的通路都是通過遊标變量實作的，其資料類型為refcursor。 建立遊标變量的方法有以下兩種：

    1). 和聲明其他類型的變量一樣，直接聲明一個遊标類型的變量即可。

    2). 使用遊标專有的聲明文法，如：

    name CURSOR [ ( arguments ) ] FOR query;

    其中arguments為一組逗号分隔的name datatype清單，見如下示例：

    curs1 refcursor;  www.2cto.com  

    curs2 CURSOR FOR SELECT * FROM tenk1;

    curs3 CURSOR (key integer) IS SELECT * FROM tenk1 WHERE unique1 = key;

    在上面三個例子中，隻有第一個是未綁定遊标，剩下兩個遊标均已被綁定。

    2. 打開遊标：

    遊标在使用之前必須先被打開，在PL/pgSQL中有三種形式的OPEN語句，其中兩種用于未綁定的遊标變量，另外一種用于綁定的遊标變量。

    1). OPEN FOR: 

    其聲明形式為：

    OPEN unbound_cursor FOR query;

    該形式隻能用于未綁定的遊标變量，其查詢語句必須是SELECT，或其他傳回記錄行的語句，如EXPLAIN。在PostgreSQL中，該查詢和普通的SQL指令平等對待，即先替換變量名，同時也将該查詢的執行計劃緩存起來，以供後用。見如下示例：

    OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;

    2). OPEN FOR EXECUTE

    OPEN unbound_cursor FOR EXECUTE query-string;   

    和上面的形式一樣，該形式也僅适用于未綁定的遊标變量。EXECUTE将動态執行其後以文本形式表示的查詢字元串。

    OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1);

    3). 打開一個綁定的遊标

    OPEN bound_cursor [ ( argument_values ) ];   

    該形式僅适用于綁定的遊标變量，隻有當該變量在聲明時包含接收參數，才能以傳遞參數的形式打開該遊标，這些參數将被實際代入到遊标聲明的查詢語句中，見如下示例：

    OPEN curs2;

    OPEN curs3(42);    

    3. 使用遊标：

    遊标一旦打開，就可以按照以下方式進行讀取。然而需要說明的是，遊标的打開和讀取必須在同一個事物内，因為在PostgreSQL中，如果事物結束，事物内打開的遊标将會被隐含的關閉。

    1). FETCH

    FETCH cursor INTO target;

    FETCH指令從遊标中讀取下一行記錄的資料到目标中，其中目标可以是行變量、記錄變量，或者是一組逗号分隔的普通變量的清單，讀取成功與否，可通過PL/pgSQL内置變量FOUND來判斷，其規則等同于SELECT INTO。見如下示例：

    FETCH curs1 INTO rowvar;  --rowvar為行變量

    FETCH curs2 INTO foo, bar, baz;

    2). CLOSE

    CLOSE cursor;

    關閉目前已經打開的遊标，以釋放其占有的系統資源，見如下示例：

    CLOSE curs1;  www.2cto.com  

七、錯誤和消息：

    在PostgreSQL中可以利用RAISE語句報告資訊和抛出錯誤，其聲明形式為：

    RAISE level 'format' [, expression [, ...]];

    這裡包含的級别有DEBUG(向伺服器日志寫資訊)、LOG(向伺服器日志寫資訊，優先級更高)、INFO、NOTICE和WARNING(把資訊寫到伺服器日志以及轉發到用戶端應用，優先級逐漸升高)和EXCEPTION抛出一個錯誤(通常退出目前事務)。某個優先級别的資訊是報告給用戶端還是寫到伺服器日志，還是兩個均有，是由log_min_messages和client_min_messages這兩個系統初始化參數控制的。

    在format部分中，%表示為占位符，其實際值僅在RAISE指令執行時由後面的變量替換，如果要在format中表示%自身，可以使用%%的形式表示，見如下示例：