天天看點
傳回

【學習資料】快速入門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 b a AND b a OR b

TRUE TRUE TRUE TRUE

TRUE FALSE FALSE TRUE

TRUE NULL NULL TRUE

FALSE FALSE FALSE FALSE

FALSE NULL FALSE NULL

NULL NULL NULL NULL

a NOT a

TRUE FALSE

FALSE TRUE

NULL NULL

比較運算

a BETWEEN x AND y

a >= x AND a <= y

a NOT BETWEEN x AND y

a < x OR a > y

比較操作符

Operator Description

< less than

| greater than

<=| less than or equal to

=| greater than or equal to

=| equal

<> or !=| not equal

斷言文法

Predicate Description

a BETWEEN x AND y between

a NOT BETWEEN x AND y not between

a BETWEEN SYMMETRIC x AND y between, after sorting the comparison values

a NOT BETWEEN SYMMETRIC x AND y not between, after sorting the comparison values

a IS DISTINCT FROM b not equal, treating null like an ordinary value

a IS NOT DISTINCT FROM b equal, treating null like an ordinary value

expression IS NULL is null

expression IS NOT NULL is not null

expression ISNULL is null (nonstandard syntax)

expression NOTNULL is not null (nonstandard syntax)

boolean_expression IS TRUE is true

boolean_expression IS NOT TRUE is false or unknown

boolean_expression IS FALSE is false

boolean_expression IS NOT FALSE is true or unknown

boolean_expression IS UNKNOWN is unknown

boolean_expression IS NOT UNKNOWN is true or false

計算空值或非空個數

Function Description Example Example Result

num_nonnulls(VARIADIC "any") returns the number of non-null arguments num_nonnulls(1, NULL, 2) 2

num_nulls(VARIADIC "any") returns the number of null arguments num_nulls(1, NULL, 2) 1

算數

運算符

pic

函數

随機值函數

三角函數

字元串

常用字元串操作符與函數

字元串處理函數

編碼轉換函數

格式化函數

常用于建構動态SQL

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

嵌入格式

%position[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;

t

h

e

q

u

i

c

k

b

r

o

w

n

f

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;
case
one
two
other

CASE表達式 2

CASE expression

WHEN value THEN result  
[WHEN ...]  
[ELSE result]             
CASE a WHEN 1 THEN 'one'  
          WHEN 2 THEN 'two'  
          ELSE 'other'  
   END  
FROM test;

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

視窗

子查詢表達式

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)

行與數組表達式

expression IN (value [, ...])

等同于

expression = value1

OR

expression = value2

...

expression NOT IN (value [, ...])

expression <> value1

AND

expression <> value2

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

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

-- basic usage

SELECT generate_subscripts('{NULL,1,NULL,2}'::int[], 1) AS s;

s

4

(4 rows)

-- set returning function WITH ORDINALITY

SELECT * FROM pg_ls_dir('.') WITH ORDINALITY AS t(ls,n);

ls        | n
pg_serial
pg_twophase
postmaster.opts
pg_notify
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函數

  1. 過程語言

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函數内部結構

[ <> ]

[ DECLARE

declarations ]

BEGIN

statements

END [ label ];

例子1

CREATE FUNCTION somefunc() RETURNS integer AS $$

<< outerblock >>

DECLARE

quantity integer := 30;             
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 := '

http://mysite.com

';

user_id CONSTANT integer := 10;

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

CREATE FUNCTION instr(varchar, integer) RETURNS integer AS $$ 

v_string ALIAS FOR $1;  
index ALIAS FOR $2;             
-- some computations using v_string and index here

CREATE FUNCTION concat_selected_fields(in_t sometablename) RETURNS text AS $$ 

RETURN in_t.f1 || in_t.f3 || in_t.f5 || in_t.f7;

基本表達式

指派

variable { := | = } expression;

執行無結果調用

PERFORM query;

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

PERFORM 1 from tbl where xxx limit 1;

if found then

xxx

else

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

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;

執行動态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

EXECUTE format('SELECT count(*) FROM %I '

'WHERE inserted_by = $1 AND inserted <= $2', tabname)

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; 忽略執行，什麼也不幹

y := x / 0;

EXCEPTION

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

控制結構

傳回單行

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$ 

r foo%rowtype;             
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;

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

循環

LOOP

statements

END LOOP [ label ];

EXIT [ label ] [ WHEN boolean-expression ]; 

-- some computations  
IF count > 0 THEN  
    EXIT;  -- exit loop  
END IF;

END LOOP; 

-- some computations  
EXIT WHEN count > 0;  -- same result as previous example

<> 

-- some computations  
IF stocks > 100000 THEN  
    EXIT ablock;  -- causes exit from the BEGIN block  
END IF;  
-- computations here will be skipped when stocks > 100000

for 循環

FOR target IN EXECUTE text_expression [ USING expression [, ... ] ] LOOP

statements

數組循環

FOREACH target [ SLICE number ] IN ARRAY expression LOOP

statements

CREATE FUNCTION sum(int[]) RETURNS int8 AS $$

s int8 := 0;

x int;

BEGIN

FOREACH x IN ARRAY $1

LOOP

s := s + x;

RETURN s;

異常捕獲

declarations ]             
statements             
WHEN condition [ OR condition ... ] THEN  
    handler_statements  
[ WHEN condition [ OR condition ... ] THEN  
      handler_statements  
  ... ]

WHEN division_by_zero THEN ...

WHEN SQLSTATE '22012' THEN ...

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;

CREATE TABLE db (a INT PRIMARY KEY, b TEXT);

CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS

$$

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

EXCEPTION WHEN unique_violation THEN

-- Do nothing, and loop to try the UPDATE again.

END;

END LOOP;

SELECT merge_db(1, 'david');

SELECT merge_db(1, 'dennis');

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

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

異常結構變量如下

text_var1 text;

text_var2 text;

text_var3 text;

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

輸出異常位置(PG_CONTEXT)

CREATE OR REPLACE FUNCTION outer_func() RETURNS integer AS $$

RETURN inner_func();

CREATE OR REPLACE FUNCTION inner_func() RETURNS integer AS $$

stack text;

GET DIAGNOSTICS stack = PG_CONTEXT;

RAISE NOTICE E'--- Call Stack ---\n%', stack;

RETURN 1;

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

遊标

聲明遊标變量

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

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

key integer;  
curs4 CURSOR FOR SELECT * FROM tenk1 WHERE unique1 = key;             
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 ' 

OPEN $1 FOR SELECT col FROM test;  
RETURN $1;

' LANGUAGE plpgsql;

BEGIN;

SELECT reffunc('funccursor');

FETCH ALL IN funccursor;

COMMIT;

CREATE FUNCTION reffunc2() RETURNS refcursor AS ' 

ref refcursor;             
OPEN ref FOR SELECT col FROM test;  
RETURN ref;

-- need to be in a transaction to use cursors.

SELECT reffunc2();

reffunc2

FETCH ALL IN "";

CREATE FUNCTION myfunc(refcursor, refcursor) RETURNS SETOF refcursor AS $$ 

OPEN $1 FOR SELECT * FROM table_1;  
RETURN NEXT $1;  
OPEN $2 FOR SELECT * FROM table_2;  
RETURN NEXT $2;

SELECT * FROM myfunc('a', 'b');

FETCH ALL FROM a;

FETCH ALL FROM b;

for遊标循環

FOR recordvar IN bound_cursorvar [ ( [ argument_name := ] argument_value [, ...] ) ] LOOP

statements

異常消息處理

抛出異常

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觸發器函數

參考觸發器部分

  1. 解析一個存儲過程

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

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$ +

調試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 $$

perform 1 from pg_class where oid=1;

$$;

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;

  1. SQL 函數

sql函數支援的文法較少

postgres=# create or replace function f1(int,int) returns int as $$

select $1+$2;

$$ language sql strict;

postgres=# select f1(1,2);

f1

(1 row)

  1. 觸發器

觸發器函數

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

觸發器分類

When Event Row-level Statement-level

BEFORE INSERT/UPDATE/DELETE Tables Tables and views

TRUNCATE — Tables -

AFTER INSERT/UPDATE/DELETE Tables Tables and views

INSTEAD OF INSERT/UPDATE/DELETE Views

TRUNCATE — —

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

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

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

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

  2. instead for each row

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

首先需要編寫觸發器函數，支援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 triggers.

This variable is NULL in statement-level triggers and for INSERT operations.

TG_NAME

Data type name; variable that contains the name of the trigger actually fired.

TG_WHEN

Data type text; a string of BEFORE, AFTER, or INSTEAD OF, depending on the trigger's definition.

TG_LEVEL

Data type text; a string of either ROW or STATEMENT depending on the trigger's definition.

TG_OP

Data type text; a string of INSERT, UPDATE, DELETE, or TRUNCATE telling for which operation the trigger was fired.

TG_RELID

Data type oid; the object ID of the table that caused the trigger invocation.

TG_RELNAME

Data type name; the name of the table that caused the trigger invocation.

This is now deprecated, and could disappear in a future release. Use TG_TABLE_NAME instead.

TG_TABLE_NAME

Data type name; the name of the table that caused the trigger invocation.

TG_TABLE_SCHEMA

Data type name; the name of the schema of the table that caused the trigger invocation.

TG_NARGS

Data type integer; the number of arguments given to the trigger procedure in the CREATE TRIGGER statement.

TG_ARGV[]

Data type array of text; the arguments from the CREATE TRIGGER statement.

The index counts from 0. Invalid indexes (less than 0 or greater than or equal to tg_nargs) result in a null value.

A trigger function must return either NULL or a record/row value having exactly the structure of the table the trigger was fired for.

以plpgsql為例，觸發器函數範例。

使用hstore 和觸發器跟蹤敏感資料的DML。

建立需要被跟蹤的測試表

CREATE TABLE test (id int primary key, info text, crt_time timestamp(0));

建立hstore extension;

CREATE EXTENSION hstore;

建立通用的存儲跟蹤記錄的記錄表

CREATE TABLE table_change_rec (

id serial8 primary key,

relid oid,

table_schema text,

table_name text,

when_tg text,

level text,

op text,

old_rec hstore,

new_rec hstore,

crt_time timestamp without time zone DEFAULT now(),

username text,

client_addr inet,

client_port int

);

建立通用的觸發器函數

CREATE OR REPLACE FUNCTION dml_trace()

RETURNS trigger

LANGUAGE plpgsql

AS $BODY$

v_new_rec hstore;

v_old_rec hstore;

v_username text := session_user;

v_client_addr inet := inet_client_addr();

v_client_port int := inet_client_port();

case TG_OP

when 'DELETE' then

v_old_rec := hstore(OLD.*);

insert into table_change_rec (relid, table_schema, table_name, when_tg, level, op, old_rec, username, client_addr, client_port)

values (tg_relid, tg_table_schema, tg_table_name, tg_when, tg_level, tg_op, v_old_rec, v_username, v_client_addr, v_client_port);

when 'INSERT' then

v_new_rec := hstore(NEW.*);

insert into table_change_rec (relid, table_schema, table_name, when_tg, level, op, new_rec, username, client_addr, client_port)

values (tg_relid, tg_table_schema, tg_table_name, tg_when, tg_level, tg_op, v_new_rec, v_username, v_client_addr, v_client_port);

when 'UPDATE' then

insert into table_change_rec (relid, table_schema, table_name, when_tg, level, op, old_rec, new_rec, username, client_addr, client_port)

values (tg_relid, tg_table_schema, tg_table_name, tg_when, tg_level, tg_op, v_old_rec, v_new_rec, v_username, v_client_addr, v_client_port);

return null;

end case;

RETURN null;

$BODY$ strict;

在測試表上分别建立插入, 更新, 删除的三個觸發器.

CREATE TRIGGER tg AFTER DELETE or INSERT or UPDATE ON test FOR EACH ROW EXECUTE PROCEDURE dml_trace();

測試插入, 删除, 更新操作是否被跟蹤.

(已更新dml_trace, 以下例子未包含client_addr和client_port)

postgres=# insert into test values (1, 'digoal', now());

INSERT 0 1

postgres=# select * from test;

id info crt_time
digoal 2012-06-25 10:54:43

postgres=# select * from table_change_rec;

id | relid | table_schema | table_name | when_tg | level | op | old_rec | new_rec

|          crt_time          | username
23731 public test AFTER ROW INSERT "id"=>"1", "info"=>"digoal", "crt_time"=>"2012-06-25
10:54:43" 2012-06-25 10:54:42.839553 postgres

postgres=# update test set info='DIGOAL' where id=1;

UPDATE 1

DIGOAL

id | relid | table_schema | table_name | when_tg | level | op | old_rec

new_rec username
"id"=>"1", "info"=>"digoal", "crt_time"=>"2012-06-25 10:54:43"
UPDATE
"id"=>"1", "info"=>"DIGOAL", "crt_time"=>"2012-06-25 10:54:43" 2012-06-25 10:55:41.006069

(2 rows)

postgres=# delete from test where id=1;

DELETE 1

(0 rows)

DELETE
2012-06-25 10:56:00.862319

使用each函數分解顯示hstore存儲的資訊.

postgres=# select id,(each(old_rec)).* from table_change_rec;

key value

(6 rows)

postgres=# select id,(each(new_rec)).* from table_change_rec;

事件觸發器函數

事件觸發器，當資料庫中發生某些DDL或DCL事件（ddl_command_start，ddl_command_end，sql_drop，table_rewrite）時，可以被捕獲，并觸發調用使用者定義的事件觸發器函數。

postgres=# \h create event

Command: CREATE EVENT TRIGGER

Description: define a new event trigger

CREATE EVENT TRIGGER name

ON event  
[ WHEN filter_variable IN (filter_value [, ... ]) [ AND ... ] ]  
EXECUTE PROCEDURE function_name()

目前事件觸發器，支援的command tag如下

首先要建立傳回event_trigger的函數，然後建立事件觸發器。

例子1，禁止postgres使用者在資料庫digoal中執行CREATE TABLE和DROP TABLE指令.

建立觸發器函數 :

CREATE OR REPLACE FUNCTION abort()

RETURNS event_trigger

LANGUAGE plpgsql

AS $$

BEGIN

if current_user = 'postgres' then

RAISE EXCEPTION 'event:%, command:%', tg_event, tg_tag;

end if;

END;

$$;

建立事件觸發器 :

digoal=# create event trigger a on ddl_command_start when TAG IN ('CREATE TABLE', 'DROP TABLE') execute procedure abort();

CREATE EVENT TRIGGER

digoal=# select * from pg_event_trigger ;

evtname evtevent evtowner evtfoid evtenabled evttags
ddl_command_start 16683 O {"CREATE TABLE","DROP TABLE"}

(1 row)

測試postgres使用者是否可以使用create table和drop table .

digoal=# \c digoal postgres

You are now connected to database "digoal" as user "postgres".

無法建立表了

digoal=# create table new(id int);

ERROR: event:ddl_command_start, command:CREATE TABLE

digoal=# \d new

Did not find any relation named "new".

digoal=# \dt

List of relations
Schema Name Type Owner
table
digoal1

(3 rows)

無法删表了

digoal=# drop table digoal;

ERROR: event:ddl_command_start, command:DROP TABLE

digoal=# \d digoal

Table "public.digoal"
Column Modifiers
integer

測試其他使用者是否會有影響 (未受到影響)

digoal=# \c digoal digoal

You are now connected to database "digoal" as user "digoal".

digoal=> create table tbl(id int);

CREATE TABLE

digoal=> drop table tbl;

DROP TABLE

例子2，用事件觸發器來實作recycle bin

我們建立一個schema來存放重命名的對象。

用一個表來記錄重命名的前後關系。

postgres=# create schema recyclebin;

postgres=# create table recyclebin.trace(id serial8 primary key,type_name text,nsp_name text,obj_name text,tg_tag text,new_nsp_name text,new_obj_name text,crt_time timestamp);

建立一個事件觸發器函數進行測試：

在遇到sql_drops事件時被觸發，執行rename的動作，而不是真實的DROP TABLE。進而實作recycle bin的功能。

CREATE OR REPLACE FUNCTION test_event_trigger_for_drops()

RETURNS event_trigger LANGUAGE plpgsql AS $$               
obj record;    
v_type_name text[] := '{}'::text[];    
v_nsp_name text[] := '{}'::text[];    
v_obj_name text[] := '{}'::text[];    
v_tg_tag text := TG_TAG;    
v_crt_time timestamp := now();    
i int := 1;    
v_new_nsp_name text := 'recyclebin';    
v_new_obj_name text;    
have_table boolean := false;    
x text;    
tt text := '';    
v_sql text[];    
v_sqlend text := '';    
v_cname text;               
FOR obj IN SELECT * FROM pg_event_trigger_dropped_objects()    
LOOP    
    RAISE NOTICE '% dropped object: % %.% %',    
                 v_tg_tag,    
                 obj.object_type,    
                 obj.schema_name,    
                 obj.object_name,    
                 obj.object_identity;    
    v_type_name := array_append(v_type_name, obj.object_type);    
    v_nsp_name := array_append(v_nsp_name, obj.schema_name);    
    v_obj_name := array_append(v_obj_name, obj.object_name);    
    if (obj.object_type = 'table') then    
      have_table := true;    
    end if;    
END LOOP;    
if ( have_table = true ) then    
  RAISE NOTICE 'Move table to recycle bin';    
  RAISE exception 'Found table in drop list.';    
end if;    
    
EXCEPTION WHEN others then    
  raise notice 'begin: ';    
  raise notice '-----------------------------------------------------------';    
  FOREACH x in ARRAY v_obj_name LOOP    
    if v_type_name[i] = 'table' then    
      v_new_obj_name := 'md5'||md5(random()::text||clock_timestamp()::text);    
      v_sql[1] := 'alter table '||v_nsp_name[i]||'.'||x||' set schema '||v_new_nsp_name||';';    
      v_sql[2] := 'alter table '||v_new_nsp_name||'.'||x||' rename to '||v_new_obj_name||';';              
      v_sql[3] := 'insert into recyclebin.trace(type_name,nsp_name,obj_name,tg_tag,new_nsp_name,new_obj_name,crt_time)     
        values ('||quote_nullable(v_type_name[i])||','||quote_nullable(v_nsp_name[i])||','||quote_nullable(v_obj_name[i])||','||quote_nullable(v_tg_tag)||','||quote_nullable(v_new_nsp_name)||','||quote_nullable(v_new_obj_name)||','||quote_nullable(v_crt_time)||');';    
      v_sqlend := v_sqlend||v_sql[1]||v_sql[2]||v_sql[3];    
    end if;    
    i := i+1;    
  END LOOP;    

      v_cname := 'myconn';    
      if (dblink_get_connections() @> array[v_cname] is not true) then    
        perform dblink_connect(v_cname, 'dbname='||current_database());    
      else    
        perform dblink_disconnect(v_cname);    
        perform dblink_connect(v_cname, 'dbname='||current_database());    
      end if;    
      perform dblink_send_query(v_cname, v_sqlend);    
      perform dblink_disconnect(v_cname);    

  raise notice 'BEGIN; % COMMIT;', v_sqlend;    
  raise notice '-----------------------------------------------------------';    
  raise exception 'Good Luck.';

END;

$$;

建立事件觸發器：

CREATE EVENT TRIGGER test_event_trigger_for_drops

ON sql_drop

EXECUTE PROCEDURE test_event_trigger_for_drops();

測試：

postgres=# create table test(id int);

postgres=# create table test1(id int) inherits(test);

NOTICE: merging column "id" with inherited definition

postgres=# create table test2(id int) inherits(test);

postgres=# create table test3(id int) inherits(test);

删除

postgres=# drop table test cascade;

NOTICE: drop cascades to 3 other objects

DETAIL: drop cascades to table test1

drop cascades to table test2

drop cascades to table test3

NOTICE: DROP TABLE dropped object: table public.test public.test

NOTICE: DROP TABLE dropped object: type public.test public.test

NOTICE: DROP TABLE dropped object: type public._test public.test[]

NOTICE: DROP TABLE dropped object: table public.test1 public.test1

NOTICE: DROP TABLE dropped object: type public.test1 public.test1

NOTICE: DROP TABLE dropped object: type public._test1 public.test1[]

NOTICE: DROP TABLE dropped object: table public.test2 public.test2

NOTICE: DROP TABLE dropped object: type public.test2 public.test2

NOTICE: DROP TABLE dropped object: type public._test2 public.test2[]

NOTICE: DROP TABLE dropped object: table public.test3 public.test3

NOTICE: DROP TABLE dropped object: type public.test3 public.test3

NOTICE: DROP TABLE dropped object: type public._test3 public.test3[]

NOTICE: Move table to recycle bin

NOTICE: begin:

NOTICE: -----------------------------------------------------------

NOTICE: BEGIN; alter table public.test set schema recyclebin;alter table recyclebin.test rename to md584b8a4bd192773d3bfe554016b4f4b46;insert into recyclebin.trace(type_name,nsp_name,obj_name,tg_tag,new_nsp_name,new_obj_name,crt_time)

values ('table','public','test','DROP TABLE','recyclebin','md584b8a4bd192773d3bfe554016b4f4b46','2016-12-24 11:58:23.497347');alter table public.test1 set schema recyclebin;alter table recyclebin.test1 rename to md59e63afc3c45a334f054e1a04711c1b9b;insert into recyclebin.trace(type_name,nsp_name,obj_name,tg_tag,new_nsp_name,new_obj_name,crt_time)     
values ('table','public','test1','DROP TABLE','recyclebin','md59e63afc3c45a334f054e1a04711c1b9b','2016-12-24 11:58:23.497347');alter table public.test2 set schema recyclebin;alter table recyclebin.test2 rename to md599fc68c328a54d5a4ded5cb98b5468eb;insert into recyclebin.trace(type_name,nsp_name,obj_name,tg_tag,new_nsp_name,new_obj_name,crt_time)     
values ('table','public','test2','DROP TABLE','recyclebin','md599fc68c328a54d5a4ded5cb98b5468eb','2016-12-24 11:58:23.497347');alter table public.test3 set schema recyclebin;alter table recyclebin.test3 rename to md5de3d73b4c8d0afe47cbcd61ba9a8f7e1;insert into recyclebin.trace(type_name,nsp_name,obj_name,tg_tag,new_nsp_name,new_obj_name,crt_time)     
values ('table','public','test3','DROP TABLE','recyclebin','md5de3d73b4c8d0afe47cbcd61ba9a8f7e1','2016-12-24 11:58:23.497347'); COMMIT;

ERROR: Good Luck.

CONTEXT: PL/pgSQL function test_event_trigger_for_drops() line 66 at RAISE

表已經被轉移到recyclebin了。

postgres=# \dt

No relations found.

postgres=# select * from recyclebin.trace;

type_name nsp_name obj_name tg_tag new_nsp_name new_obj_name
DROP TABLE recyclebin md56015e0b40b55526be0bc35591f749438 2016-12-24 11:12:39.535339
md5697b9f6a4ddca7c5d3a5e555bc13524e 2016-12-24 11:17:56.478331
md56e003aa8d4580d773dfb9ed218095350 2016-12-24 11:18:26.072341
md5987a31420a41c0335096e13bf3104bee 2016-12-24 11:24:28.805337
md5ab75b9adad19028dc139c9594c1ad283 2016-12-24 11:30:57.128355
md556dc1d9f932e19958780ec9088015531 2016-12-24 11:42:34.987422
md5dfe0ecb69b7f0c559b5512ac2cc3ac5f 2016-12-24 11:43:00.849329
md5be413eefcb0a06359a813ea235aec991 2016-12-24 11:51:46.498338
test1 md5c703b51dfd054b08b092df4e4ad24074
test2 md50b59927a9a61e0324d4434a246da79b6
md584b8a4bd192773d3bfe554016b4f4b46 2016-12-24 11:58:23.497347
md59e63afc3c45a334f054e1a04711c1b9b
md599fc68c328a54d5a4ded5cb98b5468eb
test3 md5de3d73b4c8d0afe47cbcd61ba9a8f7e1

(14 rows)

檢視目前資料庫中存在的事件觸發器

  1. 類型轉換

例如要從一個類型轉換為另一個類型。

CAST ( expression AS type )

expression::type

postgres=# select cast('2017-01-01' as date);

date

2017-01-01

postgres=# select '2017-01-01'::date;

date

自定義轉換函數

postgres=# \h create cast

Command: CREATE CAST

Description: define a new cast

CREATE CAST (source_type AS target_type)

WITH FUNCTION function_name (argument_type [, ...])  
[ AS ASSIGNMENT | AS IMPLICIT ]  
           
WITHOUT FUNCTION  
[ AS ASSIGNMENT | AS IMPLICIT ]  
           
WITH INOUT  
[ AS ASSIGNMENT | AS IMPLICIT ]

postgres=# select '12.1a.1'::text::numeric;

ERROR: invalid input syntax for type numeric: "12.1a.1"

postgres=# create or replace function text_to_numeric(text) returns numeric as $$

select to_number($1,'9999999999999999999999999.99999999999999');

postgres=# select text_to_numeric('12.1a.1');

text_to_numeric 

12.11

postgres=# create cast (text as numeric) with function text_to_numeric(text) ;

CREATE CAST

postgres=# select '12.1a.1'::text::numeric;

numeric

12.11

sql 存儲 xml json 關系型資料庫 資料庫 資料格式 postgresql 資料庫管理 perl mysql管理學習筆記 存儲管理磁盤 mysql系統學習redo 對象存儲oss管理 sql基礎管理
上一篇: 關于Serverless 未來對前端開發影響的具體看法
下一篇: 阿裡雲acp證書有啥用 阿裡雲acp證書考試試題解析

繼續閱讀