1006      阿裏雲  技術社區[雲棲]

一天學會PostgreSQL應用開發與管理 - 7 函數、存儲過程和觸發器

本章大綱

一、運算符與函數

1 邏輯運算

2 比較運算

3 算數

4 字符串

5 bytea

6 bit

7 規則表達式

8 日期、數字、字符串格式化輸出

9 時間

10 枚舉

11 幾何

12 網絡地址

13 全文檢索

14 XML

15 JSON、JSONB

16 序列

17 條件表達式

18 數組

19 範圍

20 聚合

21 窗口

22 子查詢表達式

23 行與數組表達式

24 返回集合的函數

25 係統信息函數

26 係統管理函數

二、過程語言

1 語法

2 plpgsql函數內部結構

3 控製結構

4 遊標

5 異常消息處理

6 plpgsql觸發器函數

三、解析一個存儲過程

1 檢查存儲過程(函數)內容

2 調試plpgsql存儲過程，輸出每一次調用的QUERY詳細執行計劃

3 調試plpgsql函數

四、SQL 函數

五、觸發器

1 觸發器函數

2 事件觸發器函數

六、類型轉換

第五章：函數、存儲過程和觸發器

https://www.postgresql.org/docs/9.6/static/functions.html

1. 運算符與函數

邏輯運算

and, or, not  

比較運算

a BETWEEN x AND y  
a >= x AND a <= y  
  
a NOT BETWEEN x AND y  
a < x OR a > y  

比較操作符

| greater than
<=| less than or equal to
=| greater than or equal to
=| equal
<> or !=| not equal

斷言語法

計算空值或非空個數

算數

運算符

函數

隨機值函數

三角函數

字符串

常用字符串操作符與函數

字符串處理函數

編碼轉換函數

格式化函數

常用於構建動態SQL

format(formatstr text [, formatarg "any" [, ...] ])  

嵌入格式

%[position][flags][width]type  
  
type:  
  
s formats the argument value as a simple string. A null value is treated as an empty string.  
  
I treats the argument value as an SQL identifier, double-quoting it if necessary. It is an error for the value to be null (equivalent to quote_ident).  
  
L quotes the argument value as an SQL literal. A null value is displayed as the string NULL, without quotes (equivalent to quote_nullable).  

例子

SELECT format('Hello %s', 'World');  
Result: Hello World  
  
SELECT format('Testing %s, %s, %s, %%', 'one', 'two', 'three');  
Result: Testing one, two, three, %  
  
SELECT format('INSERT INTO %I VALUES(%L)', 'Foo bar', E'O\'Reilly');  
Result: INSERT INTO "Foo bar" VALUES('O''Reilly')  
  
SELECT format('INSERT INTO %I VALUES(%L)', 'locations', E'C:\\Program Files');  
Result: INSERT INTO locations VALUES(E'C:\\Program Files')  

bytea

函數和操作符

bit

操作符

INT與BIT的轉換 , 例子

44::bit(10)                    0000101100  
44::bit(3)                     100  
cast(-44 as bit(12))           111111010100  
'1110'::bit(4)::integer        14  

規則表達式

正則操作符

例子

'abc' ~ 'abc'    true  
'abc' ~ '^a'     true  
'abc' ~ '(b|d)'  true  
'abc' ~ '^(b|c)' false  

轉義字符

快捷字符

例子, 提取匹配字符

SELECT SUBSTRING('XY1234Z', 'Y*([0-9]{1,3})');  
Result: 123  

例子, 行列變換

一個或多個空格隔開  
SELECT foo FROM regexp_split_to_table('the quick brown fox jumps over the lazy dog', E'\\s+') AS foo;  
  foo     
-------  
 the      
 quick    
 brown    
 fox      
 jumps   
 over     
 the      
 lazy     
 dog      
(9 rows)  
  
0個或多個空格隔開  
SELECT foo FROM regexp_split_to_table('the quick brown fox', E'\\s*') AS foo;  
 foo   
-----  
 t           
 h           
 e           
 q           
 u           
 i           
 c           
 k           
 b           
 r           
 o           
 w           
 n           
 f           
 o           
 x           
(16 rows)  

例子, 行轉數組

SELECT regexp_split_to_array('the quick brown fox jumps over the lazy dog', E'\\s+');  
              regexp_split_to_array               
-----------------------------------------------  
 {the,quick,brown,fox,jumps,over,the,lazy,dog}  
(1 row)  

日期、數字、字符串格式化輸出

格式化函數

時間、日期格式化樣式

時間日期格式化前綴

數字格式化樣式

數字格式化前綴

to_char例子

時間

操作符

函數

時區轉換函數

例子

SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'MST';  
Result: 2001-02-16 19:38:40-08  
  
SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST';  
Result: 2001-02-16 18:38:40  

獲取事務、語句調用時的時間

事務時間  
transaction_timestamp()  
now()  
  
語句時間  
statement_timestamp()  
clock_timestamp()  
timeofday()  

獲取當前事務時間或日期

CURRENT_DATE  
  
帶時區  
CURRENT_TIME  
CURRENT_TIMESTAMP  
CURRENT_TIME(precision)  
CURRENT_TIMESTAMP(precision)  
  
不帶時區  
LOCALTIME  
LOCALTIMESTAMP  
LOCALTIME(precision)  
LOCALTIMESTAMP(precision)  

睡眠函數

pg_sleep(seconds)  
pg_sleep_for(interval)  
pg_sleep_until(timestamp with time zone)  

例子

SELECT pg_sleep(1.5);  
SELECT pg_sleep_for('5 minutes');  
SELECT pg_sleep_until('tomorrow 03:00');  

枚舉

枚舉類型函數

例子

CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple');  

幾何

操作符

函數

類型轉換函數

網絡地址

操作符

函數

全文檢索

操作符

函數

調試函數

XML

https://www.postgresql.org/docs/9.6/static/functions-xml.html

JSON、JSONB

json\jsonb 通用操作符

jsonb 操作符

構建JSON值的函數

JSON值處理函數

序列

函數

例子

SELECT setval('foo', 42);           Next nextval will return 43  
SELECT setval('foo', 42, true);     Same as above  
SELECT setval('foo', 42, false);    Next nextval will return 42  

條件表達式

CASE表達式 1

CASE WHEN condition THEN result  
     [WHEN ...]  
     [ELSE result]  
END  

例子

SELECT * FROM test;  
  
 a  
---  
 1  
 2  
 3  
  
  
SELECT a,  
       CASE WHEN a=1 THEN 'one'  
            WHEN a=2 THEN 'two'  
            ELSE 'other'  
       END  
    FROM test;  
  
 a | case  
---+-------  
 1 | one  
 2 | two  
 3 | other  

CASE表達式 2

CASE expression  
    WHEN value THEN result  
    [WHEN ...]  
    [ELSE result]  
END  

例子

SELECT a,  
       CASE a WHEN 1 THEN 'one'  
              WHEN 2 THEN 'two'  
              ELSE 'other'  
       END  
    FROM test;  
  
 a | case  
---+-------  
 1 | one  
 2 | two  
 3 | other  
  
  
SELECT ... WHERE CASE WHEN x <> 0 THEN y/x > 1.5 ELSE false END;  -- 不符合條件的then不會被執行，所以這裏不會報除以0的錯誤  

COALESCE表達式

輸出第一個不為空的參數，都為空則輸出空

COALESCE(value [, ...])  

例子

SELECT COALESCE(description, short_description, '(none)') ...  

NULLIF表達式

v1,v2相等時返回NULL, 否則返回v1。

NULLIF(value1, value2)  

GREATEST and LEAST表達式

忽略NULL，僅僅當所有值都為NULL時返回NULL。

返回最大值  
GREATEST(value [, ...])  
  
  
返回最小值  
LEAST(value [, ...])  

數組

操作符

函數

範圍

操作符

函數

聚合

參考章節 - 高級SQL

窗口

參考章節 - 高級SQL

子查詢表達式

exists表達式

EXISTS (subquery)  

例子

SELECT col1  
FROM tab1  
WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);  

in表達式

expression IN (subquery)  
  
row_constructor IN (subquery)  

not in表達式

expression NOT IN (subquery)  
  
row_constructor NOT IN (subquery)  

any, some表達式(一個意思)

expression operator ANY (subquery)  
expression operator SOME (subquery)  
  
row_constructor operator ANY (subquery)  
row_constructor operator SOME (subquery)  

all表達式

expression operator ALL (subquery)  
  
row_constructor operator ALL (subquery)  

OP單行子查詢表達式

row_constructor operator (subquery)  

行與數組表達式

in表達式

expression IN (value [, ...])  
  
等同於  
  
expression = value1  
OR  
expression = value2  
OR  
...  

not in表達式

expression NOT IN (value [, ...])  
  
等同於  
  
expression <> value1  
AND  
expression <> value2  
AND  
...  

any, some(array)表達式

expression operator ANY (array expression)  
expression operator SOME (array expression)  

all(array)表達式

expression operator ALL (array expression)  

行與行構造器的比較操作表達式

row_constructor operator row_constructor  
  
operator is =, <>, <, <=, > or >=  
  
row_constructor IS DISTINCT FROM row_constructor  
  
row_constructor IS NOT DISTINCT FROM row_constructor  

複合類型比較表達式

record operator record  

返回集合的函數

返回多行（集合）的函數，常用於構造測試數據

例子

SELECT * FROM generate_series(2,4);  
 generate_series  
-----------------  
               2  
               3  
               4  
(3 rows)  
  
SELECT * FROM generate_series('2008-03-01 00:00'::timestamp,  
                              '2008-03-04 12:00', '10 hours');  
   generate_series     
---------------------  
 2008-03-01 00:00:00  
 2008-03-01 10:00:00  
 2008-03-01 20:00:00  
 2008-03-02 06:00:00  
 2008-03-02 16:00:00  
 2008-03-03 02:00:00  
 2008-03-03 12:00:00  
 2008-03-03 22:00:00  
 2008-03-04 08:00:00  
(9 rows)  
  
-- basic usage  
SELECT generate_subscripts('{NULL,1,NULL,2}'::int[], 1) AS s;  
 s   
---  
 1  
 2  
 3  
 4  
(4 rows)  
  
-- set returning function WITH ORDINALITY  
SELECT * FROM pg_ls_dir('.') WITH ORDINALITY AS t(ls,n);  
       ls        | n  
-----------------+----  
 pg_serial       |  1  
 pg_twophase     |  2  
 postmaster.opts |  3  
 pg_notify       |  4  
 postgresql.conf |  5  
 pg_tblspc       |  6  
 logfile         |  7  
 base            |  8  
 postmaster.pid  |  9  
 pg_ident.conf   | 10  
 global          | 11  
 pg_clog         | 12  
 pg_snapshots    | 13  
 pg_multixact    | 14  
 PG_VERSION      | 15  
 pg_xlog         | 16  
 pg_hba.conf     | 17  
 pg_stat_tmp     | 18  
 pg_subtrans     | 19  
(19 rows)  

係統信息函數

係統信息函數，可以幫助用戶了解數據庫的運行情況，配置情況等。

很多係統信息查看函數返回的是record類型，關於返回的結構解釋，請參考

https://www.postgresql.org/docs/9.6/static/functions-info.html

會話信息函數

檢查是否有訪問指定對象的權限

檢查資源是否可見

查看係統表相關的信息

獲得數據庫對象的信息或address等信息

獲得對象的注釋信息

獲得數據庫當前的事務快照信息

已提交事務的時間戳等信息

數據庫的控製信息

係統管理函數

讀取、設置參數值

信號相關函數（cancel query,殺死會話,reload配置文件,切換log文件）

物理備份、備份控製、redo文件 相關函數

物理、流式recovery信息讀取、恢複控製 相關函數

快照導出函數（生成一致性快照）

流複製相關函數(創建slot, decode等)

獲取數據庫對象的空間占用情況的函數

獲得對象物理存儲位置（文件名）函數

索引維護函數

服務端文件操作函數

用戶AD lock函數

2. 過程語言

PostgreSQL支持多種過程語言，包括plpgsql, sql, c, python, perl, java等等。

其中plpgsql是使用較多的過程語言，與Oracle的PL/SQL功能類似。

語法

CREATE [ OR REPLACE ] FUNCTION  
    name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )  
    [ RETURNS rettype  
      | RETURNS TABLE ( column_name column_type [, ...] ) ]  
  { LANGUAGE lang_name  
    | TRANSFORM { FOR TYPE type_name } [, ... ]  
    | WINDOW  
    | IMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF  
    | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT  
    | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER  
    | PARALLEL { UNSAFE | RESTRICTED | SAFE }  
    | COST execution_cost  
    | ROWS result_rows  
    | SET configuration_parameter { TO value | = value | FROM CURRENT }  
    | AS 'definition'  
    | AS 'obj_file', 'link_symbol'  
  } ...  
    [ WITH ( attribute [, ...] ) ]  

plpgsql支持任意參數、任意返回值、返回多行、返回數組、複合類型等。

plpgsql函數內部結構

[ <<label>> ]  
[ DECLARE  
    declarations ]  
BEGIN  
    statements  
END [ label ];  

例子1

CREATE FUNCTION somefunc() RETURNS integer AS $$  
<< outerblock >>  
DECLARE  
    quantity integer := 30;  
BEGIN  
    RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 30  
    quantity := 50;  
    --  
    -- Create a subblock  
    --  
    DECLARE  
        quantity integer := 80;  
    BEGIN  
        RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 80  
        RAISE NOTICE 'Outer quantity here is %', outerblock.quantity;  -- Prints 50  
    END;  
  
    RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 50  
  
    RETURN quantity;  
END;  
$$ LANGUAGE plpgsql;  

變量聲明語法

name [ CONSTANT ] type [ COLLATE collation_name ] [ NOT NULL ] [ { DEFAULT | := | = } expression ];  

例子

DECLARE  
  
user_id integer;  
quantity numeric(5);  
url varchar;  
myrow tablename%ROWTYPE;  
myfield tablename.columnname%TYPE;  
arow RECORD;  
  
quantity integer DEFAULT 32;  
url varchar := 'https://mysite.com';  
user_id CONSTANT integer := 10;  

參數使用的例子，可以使用位置參數，或者別名。

CREATE FUNCTION instr(varchar, integer) RETURNS integer AS $$  
DECLARE  
    v_string ALIAS FOR $1;  
    index ALIAS FOR $2;  
BEGIN  
    -- some computations using v_string and index here  
END;  
$$ LANGUAGE plpgsql;  
  
  
CREATE FUNCTION concat_selected_fields(in_t sometablename) RETURNS text AS $$  
BEGIN  
    RETURN in_t.f1 || in_t.f3 || in_t.f5 || in_t.f7;  
END;  
$$ LANGUAGE plpgsql;  

基本表達式

賦值

variable { := | = } expression;  

執行無結果調用

PERFORM query;  

通常被用來判斷是否有記錄，例如

PERFORM select 1 from tbl where xxx limit 1;  
if found then  
  xxx  
else  
  xxx  
end if;  

執行返回單行的表達式或QUERY，並將結果存入變量。

SELECT select_expressions INTO [STRICT] target FROM ...;  
INSERT ... RETURNING expressions INTO [STRICT] target;  
UPDATE ... RETURNING expressions INTO [STRICT] target;  
DELETE ... RETURNING expressions INTO [STRICT] target;  

將結果存入變量時，如果沒有結果被存入，或者存入的結果超過1條，可以這樣來判斷

If STRICT is not specified in the INTO clause, then target will be set to the first row returned by the query, or to nulls if the query returned no rows.

SELECT * INTO myrec FROM emp WHERE empname = myname;  
IF NOT FOUND THEN  
    RAISE EXCEPTION 'employee % not found', myname;  
END IF;  

If the STRICT option is specified, the query must return exactly one row or a run-time error will be reported,

either NO_DATA_FOUND (no rows) or TOO_MANY_ROWS (more than one row).

BEGIN  
    SELECT * INTO STRICT myrec FROM emp WHERE empname = myname;  
    EXCEPTION  
        WHEN NO_DATA_FOUND THEN  
            RAISE EXCEPTION 'employee % not found', myname;  
        WHEN TOO_MANY_ROWS THEN  
            RAISE EXCEPTION 'employee % not unique', myname;  
END;  

執行動態SQL

EXECUTE command-string [ INTO [STRICT] target ] [ USING expression [, ... ] ];  

例子

EXECUTE 'SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= $2'  
   INTO c  
   USING checked_user, checked_date;  
  
EXECUTE 'SELECT count(*) FROM '  
    || quote_ident(tabname)  
    || ' WHERE inserted_by = $1 AND inserted <= $2'  
   INTO c  
   USING checked_user, checked_date;  
  
EXECUTE format('SELECT count(*) FROM %I '  
   'WHERE inserted_by = $1 AND inserted <= $2', tabname)  
   INTO c  
   USING checked_user, checked_date;  
  
EXECUTE format('UPDATE tbl SET %I = %L '  
   'WHERE key = %L', colname, newvalue, keyvalue);  

獲取上一條SQL的執行狀態

GET [ CURRENT ] DIAGNOSTICS variable { = | := } item [ , ... ];  
  
例子  
  
GET DIAGNOSTICS integer_var = ROW_COUNT;  

支持的變量如下

FOUND變量取值原理

A SELECT INTO statement sets FOUND true if a row is assigned, false if no row is returned.  
  
A PERFORM statement sets FOUND true if it produces (and discards) one or more rows, false if no row is produced.  
  
UPDATE, INSERT, and DELETE statements set FOUND true if at least one row is affected, false if no row is affected.  
  
A FETCH statement sets FOUND true if it returns a row, false if no row is returned.  
  
A MOVE statement sets FOUND true if it successfully repositions the cursor, false otherwise.  
  
A FOR or FOREACH statement sets FOUND true if it iterates one or more times, else false.   
FOUND is set this way when the loop exits; inside the execution of the loop, FOUND is not modified by the loop statement,   
although it might be changed by the execution of other statements within the loop body.  
  
RETURN QUERY and RETURN QUERY EXECUTE statements set FOUND true if the query returns at least one row, false if no row is returned.  
  
Other PL/pgSQL statements do not change the state of FOUND.   
Note in particular that EXECUTE changes the output of GET DIAGNOSTICS, but does not change FOUND.  
  
FOUND is a local variable within each PL/pgSQL function; any changes to it affect only the current function.  

NULL; 忽略執行，什麼也不幹

BEGIN  
    y := x / 0;  
EXCEPTION  
    WHEN division_by_zero THEN  
        NULL;  -- ignore the error  
END;  
  
  
BEGIN  
    y := x / 0;  
EXCEPTION  
    WHEN division_by_zero THEN  -- ignore the error  
END;  

控製結構

返回單行

RETURN expression;  
  
例子  
  
-- functions returning a scalar type  
RETURN 1 + 2;  
RETURN scalar_var;  
  
-- functions returning a composite type  
RETURN composite_type_var;  
RETURN (1, 2, 'three'::text);  -- must cast columns to correct types  

返回多行(returns setof)

RETURN NEXT expression;  
RETURN QUERY query;  
RETURN QUERY EXECUTE command-string [ USING expression [, ... ] ];  
  
例子  
  
CREATE TABLE foo (fooid INT, foosubid INT, fooname TEXT);  
INSERT INTO foo VALUES (1, 2, 'three');  
INSERT INTO foo VALUES (4, 5, 'six');  
  
CREATE OR REPLACE FUNCTION get_all_foo() RETURNS SETOF foo AS  
$BODY$  
DECLARE  
    r foo%rowtype;  
BEGIN  
    FOR r IN  
        SELECT * FROM foo WHERE fooid > 0  
    LOOP  
        -- can do some processing here  
        RETURN NEXT r; -- return current row of SELECT  
    END LOOP;  
    RETURN;  
END  
$BODY$  
LANGUAGE plpgsql;  
  
SELECT * FROM get_all_foo();  

條件控製

IF and CASE statements let you execute alternative commands based on certain conditions. PL/pgSQL has three forms of IF:  
  
IF ... THEN ... END IF  
  
IF ... THEN ... ELSE ... END IF  
  
IF ... THEN ... ELSIF ... THEN ... ELSE ... END IF  
  
and two forms of CASE:  
  
CASE ... WHEN ... THEN ... ELSE ... END CASE  
  
CASE WHEN ... THEN ... ELSE ... END CASE  

循環

[ <<label>> ]  
LOOP  
    statements  
END LOOP [ label ];  

EXIT [ label ] [ WHEN boolean-expression ];  

例子

LOOP  
    -- some computations  
    IF count > 0 THEN  
        EXIT;  -- exit loop  
    END IF;  
END LOOP;  
  
LOOP  
    -- some computations  
    EXIT WHEN count > 0;  -- same result as previous example  
END LOOP;  
  
<<ablock>>  
BEGIN  
    -- some computations  
    IF stocks > 100000 THEN  
        EXIT ablock;  -- causes exit from the BEGIN block  
    END IF;  
    -- computations here will be skipped when stocks > 100000  
END;  

for 循環

[ <<label>> ]  
FOR target IN EXECUTE text_expression [ USING expression [, ... ] ] LOOP  
    statements  
END LOOP [ label ];  

數組循環

[ <<label>> ]  
FOREACH target [ SLICE number ] IN ARRAY expression LOOP  
    statements  
END LOOP [ label ];  

例子

CREATE FUNCTION sum(int[]) RETURNS int8 AS $$  
DECLARE  
  s int8 := 0;  
  x int;  
BEGIN  
  FOREACH x IN ARRAY $1  
  LOOP  
    s := s + x;  
  END LOOP;  
  RETURN s;  
END;  
$$ LANGUAGE plpgsql;  

異常捕獲

[ <<label>> ]  
[ DECLARE  
    declarations ]  
BEGIN  
    statements  
EXCEPTION  
    WHEN condition [ OR condition ... ] THEN  
        handler_statements  
    [ WHEN condition [ OR condition ... ] THEN  
          handler_statements  
      ... ]  
END;  

例子

WHEN division_by_zero THEN ...  
WHEN SQLSTATE '22012' THEN ...  

INSERT INTO mytab(firstname, lastname) VALUES('Tom', 'Jones');  
BEGIN  
    UPDATE mytab SET firstname = 'Joe' WHERE lastname = 'Jones';  
    x := x + 1;  
    y := x / 0;  
EXCEPTION  
    WHEN division_by_zero THEN  
        RAISE NOTICE 'caught division_by_zero';  
        RETURN x;  
END;  
  
  
CREATE TABLE db (a INT PRIMARY KEY, b TEXT);  
  
CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS  
$$  
BEGIN  
    LOOP  
        -- first try to update the key  
        UPDATE db SET b = data WHERE a = key;  
        IF found THEN  
            RETURN;  
        END IF;  
        -- not there, so try to insert the key  
        -- if someone else inserts the same key concurrently,  
        -- we could get a unique-key failure  
        BEGIN  
            INSERT INTO db(a,b) VALUES (key, data);  
            RETURN;  
        EXCEPTION WHEN unique_violation THEN  
            -- Do nothing, and loop to try the UPDATE again.  
        END;  
    END LOOP;  
END;  
$$  
LANGUAGE plpgsql;  
  
SELECT merge_db(1, 'david');  
SELECT merge_db(1, 'dennis');  

捕獲異常，輸出異常結構指定屬性

GET STACKED DIAGNOSTICS variable { = | := } item [ , ... ];  

異常結構變量如下

例子

DECLARE  
  text_var1 text;  
  text_var2 text;  
  text_var3 text;  
BEGIN  
  -- some processing which might cause an exception  
  ...  
EXCEPTION WHEN OTHERS THEN  
  GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT,  
                          text_var2 = PG_EXCEPTION_DETAIL,  
                          text_var3 = PG_EXCEPTION_HINT;  
END;  

輸出異常位置(PG_CONTEXT)

CREATE OR REPLACE FUNCTION outer_func() RETURNS integer AS $$  
BEGIN  
  RETURN inner_func();  
END;  
$$ LANGUAGE plpgsql;  
  
CREATE OR REPLACE FUNCTION inner_func() RETURNS integer AS $$  
DECLARE  
  stack text;  
BEGIN  
  GET DIAGNOSTICS stack = PG_CONTEXT;  
  RAISE NOTICE E'--- Call Stack ---\n%', stack;  
  RETURN 1;  
END;  
$$ LANGUAGE plpgsql;  
  
SELECT outer_func();  
  
NOTICE:  --- Call Stack ---  
PL/pgSQL function inner_func() line 5 at GET DIAGNOSTICS  
PL/pgSQL function outer_func() line 3 at RETURN  
CONTEXT:  PL/pgSQL function outer_func() line 3 at RETURN  
 outer_func  
 ------------  
           1  
(1 row)  

遊標

聲明遊標變量

name [ [ NO ] SCROLL ] CURSOR [ ( arguments ) ] FOR query;  

例子

DECLARE  
    curs1 refcursor;  
    curs2 CURSOR FOR SELECT * FROM tenk1;  
    curs3 CURSOR (key integer) FOR SELECT * FROM tenk1 WHERE unique1 = key;  

打開遊標例子1, 普通QUERY

OPEN unbound_cursorvar [ [ NO ] SCROLL ] FOR query;  
  
OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;  

打開遊標例子2, 動態QUERY

OPEN unbound_cursorvar [ [ NO ] SCROLL ] FOR EXECUTE query_string  
                                     [ USING expression [, ... ] ];  
  
OPEN curs1 FOR EXECUTE format('SELECT * FROM %I WHERE col1 = $1',tabname) USING keyvalue;  

打開bound遊標

OPEN bound_cursorvar [ ( [ argument_name := ] argument_value [, ...] ) ];  
  
例子  
  
OPEN curs2;  
OPEN curs3(42);  
OPEN curs3(key := 42);  
  
DECLARE  
    key integer;  
    curs4 CURSOR FOR SELECT * FROM tenk1 WHERE unique1 = key;  
BEGIN  
    key := 42;  
    OPEN curs4;  

使用遊標

獲取數據

FETCH [ direction { FROM | IN } ] cursor INTO target;  
  
例子  
  
FETCH curs1 INTO rowvar;  
FETCH curs2 INTO foo, bar, baz;  
FETCH LAST FROM curs3 INTO x, y;  
FETCH RELATIVE -2 FROM curs4 INTO x;  

移動遊標位置

MOVE [ direction { FROM | IN } ] cursor;  
  
例子  
  
MOVE curs1;  
MOVE LAST FROM curs3;  
MOVE RELATIVE -2 FROM curs4;  
MOVE FORWARD 2 FROM curs4;  

更新、刪除遊標當前記錄

UPDATE table SET ... WHERE CURRENT OF cursor;  
DELETE FROM table WHERE CURRENT OF cursor;  
  
例子  
  
UPDATE foo SET dataval = myval WHERE CURRENT OF curs1;  

關閉遊標

CLOSE cursor;  
  
例子  
  
CLOSE curs1;  

返回遊標

CREATE TABLE test (col text);  
INSERT INTO test VALUES ('123');  
  
CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS '  
BEGIN  
    OPEN $1 FOR SELECT col FROM test;  
    RETURN $1;  
END;  
' LANGUAGE plpgsql;  
  
BEGIN;  
SELECT reffunc('funccursor');  
FETCH ALL IN funccursor;  
COMMIT;  
  
CREATE FUNCTION reffunc2() RETURNS refcursor AS '  
DECLARE  
    ref refcursor;  
BEGIN  
    OPEN ref FOR SELECT col FROM test;  
    RETURN ref;  
END;  
' LANGUAGE plpgsql;  
  
-- need to be in a transaction to use cursors.  
BEGIN;  
SELECT reffunc2();  
  
      reffunc2  
--------------------  
 <unnamed cursor 1>  
(1 row)  
  
FETCH ALL IN "<unnamed cursor 1>";  
COMMIT;  
  
CREATE FUNCTION myfunc(refcursor, refcursor) RETURNS SETOF refcursor AS $$  
BEGIN  
    OPEN $1 FOR SELECT * FROM table_1;  
    RETURN NEXT $1;  
    OPEN $2 FOR SELECT * FROM table_2;  
    RETURN NEXT $2;  
END;  
$$ LANGUAGE plpgsql;  
  
-- need to be in a transaction to use cursors.  
BEGIN;  
  
SELECT * FROM myfunc('a', 'b');  
  
FETCH ALL FROM a;  
FETCH ALL FROM b;  
COMMIT;  

for遊標循環

[ <<label>> ]  
FOR recordvar IN bound_cursorvar [ ( [ argument_name := ] argument_value [, ...] ) ] LOOP  
    statements  
END LOOP [ label ];  

異常消息處理

拋出異常

RAISE [ level ] 'format' [, expression [, ... ]] [ USING option = expression [, ... ] ];  
RAISE [ level ] condition_name [ USING option = expression [, ... ] ];  
RAISE [ level ] SQLSTATE 'sqlstate' [ USING option = expression [, ... ] ];  
RAISE [ level ] USING option = expression [, ... ];  
RAISE ;  

option取值範圍

MESSAGE  
  
    Sets the error message text.   
    This option can't be used in the form of RAISE that includes a format string before USING.  
  
DETAIL  
  
    Supplies an error detail message.  
  
HINT  
  
    Supplies a hint message.  
  
ERRCODE  
  
    Specifies the error code (SQLSTATE) to report, either by condition name, as shown in Appendix A,   
    or directly as a five-character SQLSTATE code.  
  
COLUMN  
CONSTRAINT  
DATATYPE  
TABLE  
SCHEMA  
  
    Supplies the name of a related object.  

例子

RAISE EXCEPTION 'Nonexistent ID --> %', user_id  
      USING HINT = 'Please check your user ID';  
  
RAISE 'Duplicate user ID: %', user_id USING ERRCODE = 'unique_violation';  
RAISE 'Duplicate user ID: %', user_id USING ERRCODE = '23505';  
  
RAISE division_by_zero;  
RAISE SQLSTATE '22012';  
  
RAISE unique_violation USING MESSAGE = 'Duplicate user ID: ' || user_id;  

斷言

ASSERT condition [ , message ];  

plpgsql觸發器函數

參考觸發器部分

3. 解析一個存儲過程

檢查存儲過程(函數)內容

postgres=# create or replace function f() returns void as $$  
declare  
begin  
  raise notice 'this is a test';  
end;  
$$ language plpgsql;  
CREATE FUNCTION  
  
postgres=# select * from pg_get_functiondef('f'::regproc);  
          pg_get_functiondef             
---------------------------------------  
 CREATE OR REPLACE FUNCTION public.f()+  
  RETURNS void                        +  
  LANGUAGE plpgsql                    +  
 AS $function$                        +  
 declare                              +  
 begin                                +  
   raise notice 'this is a test';     +  
 end;                                 +  
 $function$                           +  
   
(1 row)  

調試plpgsql存儲過程，輸出每一次調用的QUERY詳細執行計劃

1. LOAD 'auto_explain' ;

隻在當前SESSION生效，不需要重啟數據庫, 需要超級用戶權限。

普通用戶不允許加載auto_explain模塊. (普通用戶隻允許加載$libdir/plugins目錄下的模塊,但是auto_explain即使拷貝到這個目錄也不行)

load 'auto_explain';  
set client_min_messages='log';  
set auto_explain.log_min_duration = 0;  
set auto_explain.log_analyze = true;  
set auto_explain.log_verbose = true;  
set auto_explain.log_buffers = true;  
set auto_explain.log_nested_statements = true;  
  
輸出函數內的所有被調用的QUERY的執行計劃。  
  
postgres=# do language plpgsql $$          
declare  
begin  
  perform 1 from pg_class where oid=1;  
end;  
$$;  
  
LOG:  duration: 0.008 ms  plan:  
Query Text: SELECT 1 from pg_class where oid=1  
Index Only Scan using pg_class_oid_index on pg_catalog.pg_class  (cost=0.27..1.29 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=1)  
  Output: 1  
  Index Cond: (pg_class.oid = '1'::oid)  
  Heap Fetches: 0  
  Buffers: shared hit=2  
DO  

調試plpgsql函數

安裝plugin_debugger

https://git.postgresql.org/gitweb/?p=pldebugger.git;a=summary

配置postgresql.conf

shared_preload_libraries = '$libdir/plugin_debugger'  

重啟數據庫

創建extension

CREATE EXTENSION pldbgapi;  

調試函數

4. SQL 函數

sql函數支持的語法較少

例子

postgres=# create or replace function f1(int,int) returns int as $$  
select $1+$2;  
$$ language sql strict;  
CREATE FUNCTION  
postgres=# select f1(1,2);  
 f1   
----  
  3  
(1 row)  

5. 觸發器

觸發器函數

PostgreSQL 觸發器支持行級、語句級觸發器。支持insert,update,delete,truncate觸發。

語法

postgres=# \h create trigger  
Command:     CREATE TRIGGER  
Description: define a new trigger  
Syntax:  
CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }  
    ON table_name  
    [ FROM referenced_table_name ]  
    [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]  
    [ FOR [ EACH ] { ROW | STATEMENT } ]  
    [ WHEN ( condition ) ]  
    EXECUTE PROCEDURE function_name ( arguments )  
  
where event can be one of:  
  
    INSERT  
    UPDATE [ OF column_name [, ... ] ]  
    DELETE  
    TRUNCATE  

觸發器分類

同一個表或視圖上可以創建多個觸發器, 調用的順序和觸發器的類型有關.

表上各種觸發器的調用先後順序如下 :

1. before for each statement    
2. before for each row    
3. after for each row    
4. after for each statement    

視圖上各種觸發器的調用先後順序如下 :

1. before for each statement    
2. instead for each row    
3. after for each statement    

同類觸發器如果有多個, 調用順序則和觸發器的名字有關, 按照名字的排序進行調用.

首先需要編寫觸發器函數，支持plpgsql, plpython, C等函數編寫的觸發器函數。然後創建觸發器。

plpgsql支持的觸發器函數變量

NEW  
Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level triggers.   
This variable is NULL in statement-level triggers and for DELETE operations.  
  
OLD  
Data type RECORD; variable holding the old database row for UPDATE/DELETE operations in row-level
最後更新：2017-04-12 23:25:18
  上一篇： 中小公司雲遷移時的5個注意事項
  下一篇： 天泰OpenWAF開源防惡意爬蟲模塊
相關內容
  Linux shell 基礎之--基本腳本 
  Apache Storm 官方文檔 —— 在生產環境中運行拓撲 
  【盤點】中國十大大數據管理局
 excel文件讀取例子-jxl
 Android動態切換主題
  DRDS性能評估之Jmeter使用
  用了WifiManager這麼多年，今天才知道徹底用錯了 
 什麼是連接池，其工作原理是什麼？
  阿裏巴巴推出企業誠信查詢平台 cheng.xin 
  遊戲安全資訊精選 2017年第十二期 挖礦軟件WaterMiner潛伏在《俠盜獵車手》Carbon Black勒索軟件調查報告：2016年到2017年，勒索軟件市場增長了2502%，且勢頭繼續看漲
熱門內容
 PostgreSQL 路徑規劃插件 pgruoting 介紹
 人生不過如此
  《STM32庫開發實戰指南：基於STM32F4》----導讀
  解密區塊鏈：從基礎概念到技術實現的進階攻略 
 ZED Board從入門到精通（三）：從傳統ARM開發到PS開發的轉變
 [圖文]曆屆奧斯卡影帝(上)
  紮克伯格發6千字長文,談Facebook未來願景和世界該有的樣子
 VS 添加jQuery智能提示
 python3學習
  突破吞吐限製，多NAS性能聚合方案，數據上傳及讀寫
最新內容
  駐雲Gartner聯合發布人工智能時代的MSP白皮書
  雲端安全之三：最佳實踐
  突破吞吐限製，多NAS性能聚合方案，數據上傳及讀寫
  如何同步TableStore數據到OSS
  百億級全網輿情分析係統存儲設計
  在ECS中訪問各類阿裏雲雲資源的正確姿勢 
  不外傳的seo計劃教程方案 
  網站有收錄沒排名的原因與解決辦法 
  燃！阿裏的技術小哥們又又又拿冠軍了！
  如何將個性化需求變成大數據解決方案