当前位置:   article > 正文

一天学会PostgreSQL应用开发与管理 - 7 函数、存储过程和触发器

postgresql case when 类型转换

本章大纲

一、运算符与函数

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  
aba AND ba OR b
TRUETRUETRUETRUE
TRUEFALSEFALSETRUE
TRUENULLNULLTRUE
FALSEFALSEFALSEFALSE
FALSENULLFALSENULL
NULLNULLNULLNULL
aNOT a
TRUEFALSE
FALSETRUE
NULLNULL
比较运算
  1. a BETWEEN x AND y
  2. a >= x AND a <= y
  3. a NOT BETWEEN x AND y
  4. a < x OR a > y

比较操作符

OperatorDescription
<less than

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

断言语法

PredicateDescription
a BETWEEN x AND ybetween
a NOT BETWEEN x AND ynot between
a BETWEEN SYMMETRIC x AND ybetween, after sorting the comparison values
a NOT BETWEEN SYMMETRIC x AND ynot between, after sorting the comparison values
a IS DISTINCT FROM bnot equal, treating null like an ordinary value
a IS NOT DISTINCT FROM bequal, treating null like an ordinary value
expression IS NULLis null
expression IS NOT NULLis not null
expression ISNULLis null (nonstandard syntax)
expression NOTNULLis not null (nonstandard syntax)
boolean_expression IS TRUEis true
boolean_expression IS NOT TRUEis false or unknown
boolean_expression IS FALSEis false
boolean_expression IS NOT FALSEis true or unknown
boolean_expression IS UNKNOWNis unknown
boolean_expression IS NOT UNKNOWNis true or false

计算空值或非空个数

FunctionDescriptionExampleExample Result
num_nonnulls(VARIADIC "any")returns the number of non-null argumentsnum_nonnulls(1, NULL, 2)2
num_nulls(VARIADIC "any")returns the number of null argumentsnum_nulls(1, NULL, 2)1
算数

运算符

pic

函数

pic

pic

随机值函数

pic

三角函数

pic

字符串

常用字符串操作符与函数

pic

字符串处理函数

pic

pic

pic

编码转换函数

pic

pic

pic

pic

格式化函数

常用于构建动态SQL

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

嵌入格式

  1. %[position][flags][width]type
  2. type:
  3. s formats the argument value as a simple string. A null value is treated as an empty string.
  4. 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).
  5. L quotes the argument value as an SQL literal. A null value is displayed as the string NULL, without quotes (equivalent to quote_nullable).

例子

  1. SELECT format('Hello %s', 'World');
  2. Result: Hello World
  3. SELECT format('Testing %s, %s, %s, %%', 'one', 'two', 'three');
  4. Result: Testing one, two, three, %
  5. SELECT format('INSERT INTO %I VALUES(%L)', 'Foo bar', E'O\'Reilly');
  6. Result: INSERT INTO "Foo bar" VALUES('O''Reilly')
  7. SELECT format('INSERT INTO %I VALUES(%L)', 'locations', E'C:\\Program Files');
  8. Result: INSERT INTO locations VALUES(E'C:\\Program Files')
bytea

函数和操作符

pic

pic

bit

操作符

pic

INT与BIT的转换 , 例子

  1. 44::bit(10) 0000101100
  2. 44::bit(3) 100
  3. cast(-44 as bit(12)) 111111010100
  4. '1110'::bit(4)::integer 14
规则表达式

正则操作符

pic

例子

  1. 'abc' ~ 'abc' true
  2. 'abc' ~ '^a' true
  3. 'abc' ~ '(b|d)' true
  4. 'abc' ~ '^(b|c)' false

转义字符

pic

快捷字符

pic

例子, 提取匹配字符

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

例子, 行列变换

  1. 一个或多个空格隔开
  2. SELECT foo FROM regexp_split_to_table('the quick brown fox jumps over the lazy dog', E'\\s+') AS foo;
  3. foo
  4. -------
  5. the
  6. quick
  7. brown
  8. fox
  9. jumps
  10. over
  11. the
  12. lazy
  13. dog
  14. (9 rows)
  15. 0个或多个空格隔开
  16. SELECT foo FROM regexp_split_to_table('the quick brown fox', E'\\s*') AS foo;
  17. foo
  18. -----
  19. t
  20. h
  21. e
  22. q
  23. u
  24. i
  25. c
  26. k
  27. b
  28. r
  29. o
  30. w
  31. n
  32. f
  33. o
  34. x
  35. (16 rows)

例子, 行转数组

  1. SELECT regexp_split_to_array('the quick brown fox jumps over the lazy dog', E'\\s+');
  2. regexp_split_to_array
  3. -----------------------------------------------
  4. {the,quick,brown,fox,jumps,over,the,lazy,dog}
  5. (1 row)
日期、数字、字符串格式化输出

格式化函数

pic

时间、日期格式化样式

pic

pic

时间日期格式化前缀

pic

数字格式化样式

pic

数字格式化前缀

pic

to_char例子

pic

pic

时间

操作符

pic

函数

pic

pic

时区转换函数

pic

例子

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

获取事务、语句调用时的时间

  1. 事务时间
  2. transaction_timestamp()
  3. now()
  4. 语句时间
  5. statement_timestamp()
  6. clock_timestamp()
  7. timeofday()

获取当前事务时间或日期

  1. CURRENT_DATE
  2. 带时区
  3. CURRENT_TIME
  4. CURRENT_TIMESTAMP
  5. CURRENT_TIME(precision)
  6. CURRENT_TIMESTAMP(precision)
  7. 不带时区
  8. LOCALTIME
  9. LOCALTIMESTAMP
  10. LOCALTIME(precision)
  11. LOCALTIMESTAMP(precision)

睡眠函数

  1. pg_sleep(seconds)
  2. pg_sleep_for(interval)
  3. pg_sleep_until(timestamp with time zone)

例子

  1. SELECT pg_sleep(1.5);
  2. SELECT pg_sleep_for('5 minutes');
  3. SELECT pg_sleep_until('tomorrow 03:00');
枚举

枚举类型函数

pic

例子

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

操作符

pic

函数

pic

类型转换函数

pic

网络地址

操作符

pic

函数

pic

pic

全文检索

操作符

pic

函数

pic

pic

调试函数

pic

XML

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

JSON、JSONB

json\jsonb 通用操作符

pic

jsonb 操作符

pic

构建JSON值的函数

pic

JSON值处理函数

pic

pic

pic

序列

函数

pic

例子

  1. SELECT setval('foo', 42); Next nextval will return 43
  2. SELECT setval('foo', 42, true); Same as above
  3. SELECT setval('foo', 42, false); Next nextval will return 42
条件表达式

CASE表达式 1

  1. CASE WHEN condition THEN result
  2. [WHEN ...]
  3. [ELSE result]
  4. END

例子

  1. SELECT * FROM test;
  2. a
  3. ---
  4. 1
  5. 2
  6. 3
  7. SELECT a,
  8. CASE WHEN a=1 THEN 'one'
  9. WHEN a=2 THEN 'two'
  10. ELSE 'other'
  11. END
  12. FROM test;
  13. a | case
  14. ---+-------
  15. 1 | one
  16. 2 | two
  17. 3 | other

CASE表达式 2

  1. CASE expression
  2. WHEN value THEN result
  3. [WHEN ...]
  4. [ELSE result]
  5. END

例子

  1. SELECT a,
  2. CASE a WHEN 1 THEN 'one'
  3. WHEN 2 THEN 'two'
  4. ELSE 'other'
  5. END
  6. FROM test;
  7. a | case
  8. ---+-------
  9. 1 | one
  10. 2 | two
  11. 3 | other
  12. 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。

  1. 返回最大值
  2. GREATEST(value [, ...])
  3. 返回最小值
  4. LEAST(value [, ...])
数组

操作符

pic

函数

pic

pic

范围

操作符

pic

函数

pic

聚合

参考章节 - 高级SQL

pic

pic

pic

pic

pic

窗口

参考章节 - 高级SQL

pic

子查询表达式

exists表达式

EXISTS (subquery)  

例子

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

in表达式

  1. expression IN (subquery)
  2. row_constructor IN (subquery)

not in表达式

  1. expression NOT IN (subquery)
  2. row_constructor NOT IN (subquery)

any, some表达式(一个意思)

  1. expression operator ANY (subquery)
  2. expression operator SOME (subquery)
  3. row_constructor operator ANY (subquery)
  4. row_constructor operator SOME (subquery)

all表达式

  1. expression operator ALL (subquery)
  2. row_constructor operator ALL (subquery)

OP单行子查询表达式

row_constructor operator (subquery)  
行与数组表达式

in表达式

  1. expression IN (value [, ...])
  2. 等同于
  3. expression = value1
  4. OR
  5. expression = value2
  6. OR
  7. ...

not in表达式

  1. expression NOT IN (value [, ...])
  2. 等同于
  3. expression <> value1
  4. AND
  5. expression <> value2
  6. AND
  7. ...

any, some(array)表达式

  1. expression operator ANY (array expression)
  2. expression operator SOME (array expression)

all(array)表达式

expression operator ALL (array expression)  

行与行构造器的比较操作表达式

  1. row_constructor operator row_constructor
  2. operator is =, <>, <, <=, > or >=
  3. row_constructor IS DISTINCT FROM row_constructor
  4. row_constructor IS NOT DISTINCT FROM row_constructor

复合类型比较表达式

record operator record  
返回集合的函数

返回多行(集合)的函数,常用于构造测试数据

pic

pic

例子

  1. SELECT * FROM generate_series(2,4);
  2. generate_series
  3. -----------------
  4. 2
  5. 3
  6. 4
  7. (3 rows)
  8. SELECT * FROM generate_series('2008-03-01 00:00'::timestamp,
  9. '2008-03-04 12:00', '10 hours');
  10. generate_series
  11. ---------------------
  12. 2008-03-01 00:00:00
  13. 2008-03-01 10:00:00
  14. 2008-03-01 20:00:00
  15. 2008-03-02 06:00:00
  16. 2008-03-02 16:00:00
  17. 2008-03-03 02:00:00
  18. 2008-03-03 12:00:00
  19. 2008-03-03 22:00:00
  20. 2008-03-04 08:00:00
  21. (9 rows)
  22. -- basic usage
  23. SELECT generate_subscripts('{NULL,1,NULL,2}'::int[], 1) AS s;
  24. s
  25. ---
  26. 1
  27. 2
  28. 3
  29. 4
  30. (4 rows)
  31. -- set returning function WITH ORDINALITY
  32. SELECT * FROM pg_ls_dir('.') WITH ORDINALITY AS t(ls,n);
  33. ls | n
  34. -----------------+----
  35. pg_serial | 1
  36. pg_twophase | 2
  37. postmaster.opts | 3
  38. pg_notify | 4
  39. postgresql.conf | 5
  40. pg_tblspc | 6
  41. logfile | 7
  42. base | 8
  43. postmaster.pid | 9
  44. pg_ident.conf | 10
  45. global | 11
  46. pg_clog | 12
  47. pg_snapshots | 13
  48. pg_multixact | 14
  49. PG_VERSION | 15
  50. pg_xlog | 16
  51. pg_hba.conf | 17
  52. pg_stat_tmp | 18
  53. pg_subtrans | 19
  54. (19 rows)
系统信息函数

系统信息函数,可以帮助用户了解数据库的运行情况,配置情况等。

很多系统信息查看函数返回的是record类型,关于返回的结构解释,请参考

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

会话信息函数

pic

检查是否有访问指定对象的权限

pic

检查资源是否可见

pic

查看系统表相关的信息

pic

pic

获得数据库对象的信息或address等信息

pic

获得对象的注释信息

pic

获得数据库当前的事务快照信息

pic

已提交事务的时间戳等信息

pic

数据库的控制信息

pic

系统管理函数

读取、设置参数值

pic

信号相关函数(cancel query,杀死会话,reload配置文件,切换log文件)

pic

物理备份、备份控制、redo文件 相关函数

pic

物理、流式recovery信息读取、恢复控制 相关函数

pic

pic

快照导出函数(生成一致性快照)

pic

流复制相关函数(创建slot, decode等)

pic

pic

获取数据库对象的空间占用情况的函数

pic

获得对象物理存储位置(文件名)函数

pic

索引维护函数

pic

服务端文件操作函数

pic

用户AD lock函数

pic

2. 过程语言

PostgreSQL支持多种过程语言,包括plpgsql, sql, c, python, perl, java等等。

其中plpgsql是使用较多的过程语言,与Oracle的PL/SQL功能类似。

语法
  1. CREATE [ OR REPLACE ] FUNCTION
  2. name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
  3. [ RETURNS rettype
  4. | RETURNS TABLE ( column_name column_type [, ...] ) ]
  5. { LANGUAGE lang_name
  6. | TRANSFORM { FOR TYPE type_name } [, ... ]
  7. | WINDOW
  8. | IMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF
  9. | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
  10. | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
  11. | PARALLEL { UNSAFE | RESTRICTED | SAFE }
  12. | COST execution_cost
  13. | ROWS result_rows
  14. | SET configuration_parameter { TO value | = value | FROM CURRENT }
  15. | AS 'definition'
  16. | AS 'obj_file', 'link_symbol'
  17. } ...
  18. [ WITH ( attribute [, ...] ) ]

plpgsql支持任意参数、任意返回值、返回多行、返回数组、复合类型等。

plpgsql函数内部结构
  1. [ <<label>> ]
  2. [ DECLARE
  3. declarations ]
  4. BEGIN
  5. statements
  6. END [ label ];

例子1

  1. CREATE FUNCTION somefunc() RETURNS integer AS $$
  2. << outerblock >>
  3. DECLARE
  4. quantity integer := 30;
  5. BEGIN
  6. RAISE NOTICE 'Quantity here is %', quantity; -- Prints 30
  7. quantity := 50;
  8. --
  9. -- Create a subblock
  10. --
  11. DECLARE
  12. quantity integer := 80;
  13. BEGIN
  14. RAISE NOTICE 'Quantity here is %', quantity; -- Prints 80
  15. RAISE NOTICE 'Outer quantity here is %', outerblock.quantity; -- Prints 50
  16. END;
  17. RAISE NOTICE 'Quantity here is %', quantity; -- Prints 50
  18. RETURN quantity;
  19. END;
  20. $$ LANGUAGE plpgsql;

变量声明语法

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

例子

  1. DECLARE
  2. user_id integer;
  3. quantity numeric(5);
  4. url varchar;
  5. myrow tablename%ROWTYPE;
  6. myfield tablename.columnname%TYPE;
  7. arow RECORD;
  8. quantity integer DEFAULT 32;
  9. url varchar := 'http://mysite.com';
  10. user_id CONSTANT integer := 10;

参数使用的例子,可以使用位置参数,或者别名。

  1. CREATE FUNCTION instr(varchar, integer) RETURNS integer AS $$
  2. DECLARE
  3. v_string ALIAS FOR $1;
  4. index ALIAS FOR $2;
  5. BEGIN
  6. -- some computations using v_string and index here
  7. END;
  8. $$ LANGUAGE plpgsql;
  9. CREATE FUNCTION concat_selected_fields(in_t sometablename) RETURNS text AS $$
  10. BEGIN
  11. RETURN in_t.f1 || in_t.f3 || in_t.f5 || in_t.f7;
  12. END;
  13. $$ LANGUAGE plpgsql;

基本表达式

赋值

variable { := | = } expression;  

执行无结果调用

PERFORM query;  

通常被用来判断是否有记录,例如

  1. PERFORM select 1 from tbl where xxx limit 1;
  2. if found then
  3. xxx
  4. else
  5. xxx
  6. end if;

执行返回单行的表达式或QUERY,并将结果存入变量。

  1. SELECT select_expressions INTO [STRICT] target FROM ...;
  2. INSERT ... RETURNING expressions INTO [STRICT] target;
  3. UPDATE ... RETURNING expressions INTO [STRICT] target;
  4. 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.

  1. SELECT * INTO myrec FROM emp WHERE empname = myname;
  2. IF NOT FOUND THEN
  3. RAISE EXCEPTION 'employee % not found', myname;
  4. 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).

  1. BEGIN
  2. SELECT * INTO STRICT myrec FROM emp WHERE empname = myname;
  3. EXCEPTION
  4. WHEN NO_DATA_FOUND THEN
  5. RAISE EXCEPTION 'employee % not found', myname;
  6. WHEN TOO_MANY_ROWS THEN
  7. RAISE EXCEPTION 'employee % not unique', myname;
  8. END;

执行动态SQL

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

例子

  1. EXECUTE 'SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= $2'
  2. INTO c
  3. USING checked_user, checked_date;
  4. EXECUTE 'SELECT count(*) FROM '
  5. || quote_ident(tabname)
  6. || ' WHERE inserted_by = $1 AND inserted <= $2'
  7. INTO c
  8. USING checked_user, checked_date;
  9. EXECUTE format('SELECT count(*) FROM %I '
  10. 'WHERE inserted_by = $1 AND inserted <= $2', tabname)
  11. INTO c
  12. USING checked_user, checked_date;
  13. EXECUTE format('UPDATE tbl SET %I = %L '
  14. 'WHERE key = %L', colname, newvalue, keyvalue);

获取上一条SQL的执行状态

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

支持的变量如下

pic

FOUND变量取值原理

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

NULL; 忽略执行,什么也不干

  1. BEGIN
  2. y := x / 0;
  3. EXCEPTION
  4. WHEN division_by_zero THEN
  5. NULL; -- ignore the error
  6. END;
  7. BEGIN
  8. y := x / 0;
  9. EXCEPTION
  10. WHEN division_by_zero THEN -- ignore the error
  11. END;
控制结构

返回单行

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

返回多行(returns setof)

  1. RETURN NEXT expression;
  2. RETURN QUERY query;
  3. RETURN QUERY EXECUTE command-string [ USING expression [, ... ] ];
  4. 例子
  5. CREATE TABLE foo (fooid INT, foosubid INT, fooname TEXT);
  6. INSERT INTO foo VALUES (1, 2, 'three');
  7. INSERT INTO foo VALUES (4, 5, 'six');
  8. CREATE OR REPLACE FUNCTION get_all_foo() RETURNS SETOF foo AS
  9. $BODY$
  10. DECLARE
  11. r foo%rowtype;
  12. BEGIN
  13. FOR r IN
  14. SELECT * FROM foo WHERE fooid > 0
  15. LOOP
  16. -- can do some processing here
  17. RETURN NEXT r; -- return current row of SELECT
  18. END LOOP;
  19. RETURN;
  20. END
  21. $BODY$
  22. LANGUAGE plpgsql;
  23. SELECT * FROM get_all_foo();

条件控制

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

循环

  1. [ <<label>> ]
  2. LOOP
  3. statements
  4. END LOOP [ label ];
EXIT [ label ] [ WHEN boolean-expression ];  

例子

  1. LOOP
  2. -- some computations
  3. IF count > 0 THEN
  4. EXIT; -- exit loop
  5. END IF;
  6. END LOOP;
  7. LOOP
  8. -- some computations
  9. EXIT WHEN count > 0; -- same result as previous example
  10. END LOOP;
  11. <<ablock>>
  12. BEGIN
  13. -- some computations
  14. IF stocks > 100000 THEN
  15. EXIT ablock; -- causes exit from the BEGIN block
  16. END IF;
  17. -- computations here will be skipped when stocks > 100000
  18. END;

for 循环

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

数组循环

  1. [ <<label>> ]
  2. FOREACH target [ SLICE number ] IN ARRAY expression LOOP
  3. statements
  4. END LOOP [ label ];

例子

  1. CREATE FUNCTION sum(int[]) RETURNS int8 AS $$
  2. DECLARE
  3. s int8 := 0;
  4. x int;
  5. BEGIN
  6. FOREACH x IN ARRAY $1
  7. LOOP
  8. s := s + x;
  9. END LOOP;
  10. RETURN s;
  11. END;
  12. $$ LANGUAGE plpgsql;

异常捕获

  1. [ <<label>> ]
  2. [ DECLARE
  3. declarations ]
  4. BEGIN
  5. statements
  6. EXCEPTION
  7. WHEN condition [ OR condition ... ] THEN
  8. handler_statements
  9. [ WHEN condition [ OR condition ... ] THEN
  10. handler_statements
  11. ... ]
  12. END;

例子

  1. WHEN division_by_zero THEN ...
  2. WHEN SQLSTATE '22012' THEN ...
  1. INSERT INTO mytab(firstname, lastname) VALUES('Tom', 'Jones');
  2. BEGIN
  3. UPDATE mytab SET firstname = 'Joe' WHERE lastname = 'Jones';
  4. x := x + 1;
  5. y := x / 0;
  6. EXCEPTION
  7. WHEN division_by_zero THEN
  8. RAISE NOTICE 'caught division_by_zero';
  9. RETURN x;
  10. END;
  11. CREATE TABLE db (a INT PRIMARY KEY, b TEXT);
  12. CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
  13. $$
  14. BEGIN
  15. LOOP
  16. -- first try to update the key
  17. UPDATE db SET b = data WHERE a = key;
  18. IF found THEN
  19. RETURN;
  20. END IF;
  21. -- not there, so try to insert the key
  22. -- if someone else inserts the same key concurrently,
  23. -- we could get a unique-key failure
  24. BEGIN
  25. INSERT INTO db(a,b) VALUES (key, data);
  26. RETURN;
  27. EXCEPTION WHEN unique_violation THEN
  28. -- Do nothing, and loop to try the UPDATE again.
  29. END;
  30. END LOOP;
  31. END;
  32. $$
  33. LANGUAGE plpgsql;
  34. SELECT merge_db(1, 'david');
  35. SELECT merge_db(1, 'dennis');

捕获异常,输出异常结构指定属性

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

异常结构变量如下

pic

例子

  1. DECLARE
  2. text_var1 text;
  3. text_var2 text;
  4. text_var3 text;
  5. BEGIN
  6. -- some processing which might cause an exception
  7. ...
  8. EXCEPTION WHEN OTHERS THEN
  9. GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT,
  10. text_var2 = PG_EXCEPTION_DETAIL,
  11. text_var3 = PG_EXCEPTION_HINT;
  12. END;

输出异常位置(PG_CONTEXT)

  1. CREATE OR REPLACE FUNCTION outer_func() RETURNS integer AS $$
  2. BEGIN
  3. RETURN inner_func();
  4. END;
  5. $$ LANGUAGE plpgsql;
  6. CREATE OR REPLACE FUNCTION inner_func() RETURNS integer AS $$
  7. DECLARE
  8. stack text;
  9. BEGIN
  10. GET DIAGNOSTICS stack = PG_CONTEXT;
  11. RAISE NOTICE E'--- Call Stack ---\n%', stack;
  12. RETURN 1;
  13. END;
  14. $$ LANGUAGE plpgsql;
  15. SELECT outer_func();
  16. NOTICE: --- Call Stack ---
  17. PL/pgSQL function inner_func() line 5 at GET DIAGNOSTICS
  18. PL/pgSQL function outer_func() line 3 at RETURN
  19. CONTEXT: PL/pgSQL function outer_func() line 3 at RETURN
  20. outer_func
  21. ------------
  22. 1
  23. (1 row)
游标

声明游标变量

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

例子

  1. DECLARE
  2. curs1 refcursor;
  3. curs2 CURSOR FOR SELECT * FROM tenk1;
  4. curs3 CURSOR (key integer) FOR SELECT * FROM tenk1 WHERE unique1 = key;

打开游标例子1, 普通QUERY

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

打开游标例子2, 动态QUERY

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

打开bound游标

  1. OPEN bound_cursorvar [ ( [ argument_name := ] argument_value [, ...] ) ];
  2. 例子
  3. OPEN curs2;
  4. OPEN curs3(42);
  5. OPEN curs3(key := 42);
  6. DECLARE
  7. key integer;
  8. curs4 CURSOR FOR SELECT * FROM tenk1 WHERE unique1 = key;
  9. BEGIN
  10. key := 42;
  11. OPEN curs4;

使用游标

获取数据

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

移动游标位置

  1. MOVE [ direction { FROM | IN } ] cursor;
  2. 例子
  3. MOVE curs1;
  4. MOVE LAST FROM curs3;
  5. MOVE RELATIVE -2 FROM curs4;
  6. MOVE FORWARD 2 FROM curs4;

更新、删除游标当前记录

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

关闭游标

  1. CLOSE cursor;
  2. 例子
  3. CLOSE curs1;

返回游标

  1. CREATE TABLE test (col text);
  2. INSERT INTO test VALUES ('123');
  3. CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS '
  4. BEGIN
  5. OPEN $1 FOR SELECT col FROM test;
  6. RETURN $1;
  7. END;
  8. ' LANGUAGE plpgsql;
  9. BEGIN;
  10. SELECT reffunc('funccursor');
  11. FETCH ALL IN funccursor;
  12. COMMIT;
  13. CREATE FUNCTION reffunc2() RETURNS refcursor AS '
  14. DECLARE
  15. ref refcursor;
  16. BEGIN
  17. OPEN ref FOR SELECT col FROM test;
  18. RETURN ref;
  19. END;
  20. ' LANGUAGE plpgsql;
  21. -- need to be in a transaction to use cursors.
  22. BEGIN;
  23. SELECT reffunc2();
  24. reffunc2
  25. --------------------
  26. <unnamed cursor 1>
  27. (1 row)
  28. FETCH ALL IN "<unnamed cursor 1>";
  29. COMMIT;
  30. CREATE FUNCTION myfunc(refcursor, refcursor) RETURNS SETOF refcursor AS $$
  31. BEGIN
  32. OPEN $1 FOR SELECT * FROM table_1;
  33. RETURN NEXT $1;
  34. OPEN $2 FOR SELECT * FROM table_2;
  35. RETURN NEXT $2;
  36. END;
  37. $$ LANGUAGE plpgsql;
  38. -- need to be in a transaction to use cursors.
  39. BEGIN;
  40. SELECT * FROM myfunc('a', 'b');
  41. FETCH ALL FROM a;
  42. FETCH ALL FROM b;
  43. COMMIT;

for游标循环

  1. [ <<label>> ]
  2. FOR recordvar IN bound_cursorvar [ ( [ argument_name := ] argument_value [, ...] ) ] LOOP
  3. statements
  4. END LOOP [ label ];
异常消息处理

抛出异常

  1. RAISE [ level ] 'format' [, expression [, ... ]] [ USING option = expression [, ... ] ];
  2. RAISE [ level ] condition_name [ USING option = expression [, ... ] ];
  3. RAISE [ level ] SQLSTATE 'sqlstate' [ USING option = expression [, ... ] ];
  4. RAISE [ level ] USING option = expression [, ... ];
  5. RAISE ;

option取值范围

  1. MESSAGE
  2. Sets the error message text.
  3. This option can't be used in the form of RAISE that includes a format string before USING.
  4. DETAIL
  5. Supplies an error detail message.
  6. HINT
  7. Supplies a hint message.
  8. ERRCODE
  9. Specifies the error code (SQLSTATE) to report, either by condition name, as shown in Appendix A,
  10. or directly as a five-character SQLSTATE code.
  11. COLUMN
  12. CONSTRAINT
  13. DATATYPE
  14. TABLE
  15. SCHEMA
  16. Supplies the name of a related object.

例子

  1. RAISE EXCEPTION 'Nonexistent ID --> %', user_id
  2. USING HINT = 'Please check your user ID';
  3. RAISE 'Duplicate user ID: %', user_id USING ERRCODE = 'unique_violation';
  4. RAISE 'Duplicate user ID: %', user_id USING ERRCODE = '23505';
  5. RAISE division_by_zero;
  6. RAISE SQLSTATE '22012';
  7. RAISE unique_violation USING MESSAGE = 'Duplicate user ID: ' || user_id;

断言

ASSERT condition [ , message ];  
plpgsql触发器函数

参考触发器部分

3. 解析一个存储过程

检查存储过程(函数)内容
  1. postgres=# create or replace function f() returns void as $$
  2. declare
  3. begin
  4. raise notice 'this is a test';
  5. end;
  6. $$ language plpgsql;
  7. CREATE FUNCTION
  8. postgres=# select * from pg_get_functiondef('f'::regproc);
  9. pg_get_functiondef
  10. ---------------------------------------
  11. CREATE OR REPLACE FUNCTION public.f()+
  12. RETURNS void +
  13. LANGUAGE plpgsql +
  14. AS $function$ +
  15. declare +
  16. begin +
  17. raise notice 'this is a test'; +
  18. end; +
  19. $function$ +
  20. (1 row)
调试plpgsql存储过程,输出每一次调用的QUERY详细执行计划

1. LOAD 'auto_explain' ;

只在当前SESSION生效,不需要重启数据库, 需要超级用户权限。

普通用户不允许加载auto_explain模块. (普通用户只允许加载$libdir/plugins目录下的模块,但是auto_explain即使拷贝到这个目录也不行)

  1. load 'auto_explain';
  2. set client_min_messages='log';
  3. set auto_explain.log_min_duration = 0;
  4. set auto_explain.log_analyze = true;
  5. set auto_explain.log_verbose = true;
  6. set auto_explain.log_buffers = true;
  7. set auto_explain.log_nested_statements = true;
  8. 输出函数内的所有被调用的QUERY的执行计划。
  9. postgres=# do language plpgsql $$
  10. declare
  11. begin
  12. perform 1 from pg_class where oid=1;
  13. end;
  14. $$;
  15. LOG: duration: 0.008 ms plan:
  16. Query Text: SELECT 1 from pg_class where oid=1
  17. 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)
  18. Output: 1
  19. Index Cond: (pg_class.oid = '1'::oid)
  20. Heap Fetches: 0
  21. Buffers: shared hit=2
  22. 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;  

调试函数

pic

pic

4. SQL 函数

sql函数支持的语法较少

例子

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

5. 触发器

触发器函数

PostgreSQL 触发器支持行级、语句级触发器。支持insert,update,delete,truncate触发。

语法

  1. postgres=# \h create trigger
  2. Command: CREATE TRIGGER
  3. Description: define a new trigger
  4. Syntax:
  5. CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
  6. ON table_name
  7. [ FROM referenced_table_name ]
  8. [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
  9. [ FOR [ EACH ] { ROW | STATEMENT } ]
  10. [ WHEN ( condition ) ]
  11. EXECUTE PROCEDURE function_name ( arguments )
  12. where event can be one of:
  13. INSERT
  14. UPDATE [ OF column_name [, ... ] ]
  15. DELETE
  16. TRUNCATE

触发器分类

WhenEventRow-levelStatement-level
BEFOREINSERT/UPDATE/DELETETablesTables and views
TRUNCATETables-
AFTERINSERT/UPDATE/DELETETablesTables and views
TRUNCATETables-
INSTEAD OFINSERT/UPDATE/DELETEViews
TRUNCATE

同一个表或视图上可以创建多个触发器, 调用的顺序和触发器的类型有关.

表上各种触发器的调用先后顺序如下 :

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

视图上各种触发器的调用先后顺序如下 :

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

同类触发器如果有多个, 调用顺序则和触发器的名字有关, 按照名字的排序进行调用.

首先需要编写触发器函数,支持plpgsql, plpython, C等函数编写的触发器函数。然后创建触发器。

plpgsql支持的触发器函数变量

  1. NEW
  2. Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level triggers.
  3. This variable is NULL in statement-level triggers and for DELETE operations.
  4. OLD
  5. Data type RECORD; variable holding the old database row for UPDATE/DELETE operations in row-level triggers.
  6. This variable is NULL in statement-level triggers and for INSERT operations.
  7. TG_NAME
  8. Data type name; variable that contains the name of the trigger actually fired.
  9. TG_WHEN
  10. Data type text; a string of BEFORE, AFTER, or INSTEAD OF, depending on the trigger's definition.
  11. TG_LEVEL
  12. Data type text; a string of either ROW or STATEMENT depending on the trigger's definition.
  13. TG_OP
  14. Data type text; a string of INSERT, UPDATE, DELETE, or TRUNCATE telling for which operation the trigger was fired.
  15. TG_RELID
  16. Data type oid; the object ID of the table that caused the trigger invocation.
  17. TG_RELNAME
  18. Data type name; the name of the table that caused the trigger invocation.
  19. This is now deprecated, and could disappear in a future release. Use TG_TABLE_NAME instead.
  20. TG_TABLE_NAME
  21. Data type name; the name of the table that caused the trigger invocation.
  22. TG_TABLE_SCHEMA
  23. Data type name; the name of the schema of the table that caused the trigger invocation.
  24. TG_NARGS
  25. Data type integer; the number of arguments given to the trigger procedure in the CREATE TRIGGER statement.
  26. TG_ARGV[]
  27. Data type array of text; the arguments from the CREATE TRIGGER statement.
  28. The index counts from 0. Invalid indexes (less than 0 or greater than or equal to tg_nargs) result in a null value.
  29. 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;  

创建通用的存储跟踪记录的记录表

  1. CREATE TABLE table_change_rec (
  2. id serial8 primary key,
  3. relid oid,
  4. table_schema text,
  5. table_name text,
  6. when_tg text,
  7. level text,
  8. op text,
  9. old_rec hstore,
  10. new_rec hstore,
  11. crt_time timestamp without time zone DEFAULT now(),
  12. username text,
  13. client_addr inet,
  14. client_port int
  15. );

创建通用的触发器函数

  1. CREATE OR REPLACE FUNCTION dml_trace()
  2. RETURNS trigger
  3. LANGUAGE plpgsql
  4. AS $BODY$
  5. DECLARE
  6. v_new_rec hstore;
  7. v_old_rec hstore;
  8. v_username text := session_user;
  9. v_client_addr inet := inet_client_addr();
  10. v_client_port int := inet_client_port();
  11. BEGIN
  12. case TG_OP
  13. when 'DELETE' then
  14. v_old_rec := hstore(OLD.*);
  15. insert into table_change_rec (relid, table_schema, table_name, when_tg, level, op, old_rec, username, client_addr, client_port)
  16. 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);
  17. when 'INSERT' then
  18. v_new_rec := hstore(NEW.*);
  19. insert into table_change_rec (relid, table_schema, table_name, when_tg, level, op, new_rec, username, client_addr, client_port)
  20. 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);
  21. when 'UPDATE' then
  22. v_old_rec := hstore(OLD.*);
  23. v_new_rec := hstore(NEW.*);
  24. insert into table_change_rec (relid, table_schema, table_name, when_tg, level, op, old_rec, new_rec, username, client_addr, client_port)
  25. 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);
  26. else
  27. return null;
  28. end case;
  29. RETURN null;
  30. END;
  31. $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)

  1. postgres=# insert into test values (1, 'digoal', now());
  2. INSERT 0 1
  3. postgres=# select * from test;
  4. id | info | crt_time
  5. ----+--------+---------------------
  6. 1 | digoal | 2012-06-25 10:54:43
  7. (1 row)
  8. postgres=# select * from table_change_rec;
  9. id | relid | table_schema | table_name | when_tg | level | op | old_rec | new_rec
  10. | crt_time | username
  11. ----+-------+--------------+------------+---------+-------+--------+---------+------------------------------------------------------
  12. ----------+----------------------------+----------
  13. 4 | 23731 | public | test | AFTER | ROW | INSERT | | "id"=>"1", "info"=>"digoal", "crt_time"=>"2012-06-25
  14. 10:54:43" | 2012-06-25 10:54:42.839553 | postgres
  15. (1 row)
  16. postgres=# update test set info='DIGOAL' where id=1;
  17. UPDATE 1
  18. postgres=# select * from test;
  19. id | info | crt_time
  20. ----+--------+---------------------
  21. 1 | DIGOAL | 2012-06-25 10:54:43
  22. (1 row)
  23. postgres=# select * from table_change_rec;
  24. id | relid | table_schema | table_name | when_tg | level | op | old_rec
  25. | new_rec | crt_time | username
  26. ----+-------+--------------+------------+---------+-------+--------+----------------------------------------------------------------
  27. +----------------------------------------------------------------+----------------------------+----------
  28. 4 | 23731 | public | test | AFTER | ROW | INSERT |
  29. | "id"=>"1", "info"=>"digoal", "crt_time"=>"2012-06-25 10:54:43" | 2012-06-25 10:54:42.839553 | postgres
  30. 5 | 23731 | public | test | AFTER | ROW | UPDATE | "id"=>"1", "info"=>"digoal", "crt_time"=>"2012-06-25 10:54:43"
  31. | "id"=>"1", "info"=>"DIGOAL", "crt_time"=>"2012-06-25 10:54:43" | 2012-06-25 10:55:41.006069 | postgres
  32. (2 rows)
  33. postgres=# delete from test where id=1;
  34. DELETE 1
  35. postgres=# select * from test;
  36. id | info | crt_time
  37. ----+------+----------
  38. (0 rows)
  39. postgres=# select * from table_change_rec;
  40. id | relid | table_schema | table_name | when_tg | level | op | old_rec
  41. | new_rec | crt_time | username
  42. ----+-------+--------------+------------+---------+-------+--------+----------------------------------------------------------------
  43. +----------------------------------------------------------------+----------------------------+----------
  44. 4 | 23731 | public | test | AFTER | ROW | INSERT |
  45. | "id"=>"1", "info"=>"digoal", "crt_time"=>"2012-06-25 10:54:43" | 2012-06-25 10:54:42.839553 | postgres
  46. 5 | 23731 | public | test | AFTER | ROW | UPDATE | "id"=>"1", "info"=>"digoal", "crt_time"=>"2012-06-25 10:54:43"
  47. | "id"=>"1", "info"=>"DIGOAL", "crt_time"=>"2012-06-25 10:54:43" | 2012-06-25 10:55:41.006069 | postgres
  48. 6 | 23731 | public | test | AFTER | ROW | DELETE | "id"=>"1", "info"=>"DIGOAL", "crt_time"=>"2012-06-25 10:54:43"
  49. | | 2012-06-25 10:56:00.862319 | postgres
  50. (3 rows)

使用each函数分解显示hstore存储的信息.

  1. postgres=# select id,(each(old_rec)).* from table_change_rec;
  2. id | key | value
  3. ----+----------+---------------------
  4. 5 | id | 1
  5. 5 | info | digoal
  6. 5 | crt_time | 2012-06-25 10:54:43
  7. 6 | id | 1
  8. 6 | info | DIGOAL
  9. 6 | crt_time | 2012-06-25 10:54:43
  10. (6 rows)
  11. postgres=# select id,(each(new_rec)).* from table_change_rec;
  12. id | key | value
  13. ----+----------+---------------------
  14. 4 | id | 1
  15. 4 | info | digoal
  16. 4 | crt_time | 2012-06-25 10:54:43
  17. 5 | id | 1
  18. 5 | info | DIGOAL
  19. 5 | crt_time | 2012-06-25 10:54:43
  20. (6 rows)
事件触发器函数

事件触发器,当数据库中发生某些DDL或DCL事件(ddl_command_start,ddl_command_end,sql_drop,table_rewrite)时,可以被捕获,并触发调用用户定义的事件触发器函数。

语法

  1. postgres=# \h create event
  2. Command: CREATE EVENT TRIGGER
  3. Description: define a new event trigger
  4. Syntax:
  5. CREATE EVENT TRIGGER name
  6. ON event
  7. [ WHEN filter_variable IN (filter_value [, ... ]) [ AND ... ] ]
  8. EXECUTE PROCEDURE function_name()

目前事件触发器,支持的command tag如下

pic

pic

pic

首先要创建返回event_trigger的函数,然后创建事件触发器。

例子1,禁止postgres用户在数据库digoal中执行CREATE TABLE和DROP TABLE命令.

创建触发器函数 :

  1. CREATE OR REPLACE FUNCTION abort()
  2. RETURNS event_trigger
  3. LANGUAGE plpgsql
  4. AS $$
  5. BEGIN
  6. if current_user = 'postgres' then
  7. RAISE EXCEPTION 'event:%, command:%', tg_event, tg_tag;
  8. end if;
  9. END;
  10. $$;

创建事件触发器 :

  1. digoal=# create event trigger a on ddl_command_start when TAG IN ('CREATE TABLE', 'DROP TABLE') execute procedure abort();
  2. CREATE EVENT TRIGGER
  3. digoal=# select * from pg_event_trigger ;
  4. evtname | evtevent | evtowner | evtfoid | evtenabled | evttags
  5. ---------+-------------------+----------+---------+------------+-------------------------------
  6. a | ddl_command_start | 10 | 16683 | O | {"CREATE TABLE","DROP TABLE"}
  7. (1 row)

测试postgres用户是否可以使用create table和drop table .

  1. digoal=# \c digoal postgres
  2. You are now connected to database "digoal" as user "postgres".

无法新建表了

  1. digoal=# create table new(id int);
  2. ERROR: event:ddl_command_start, command:CREATE TABLE
  3. digoal=# \d new
  4. Did not find any relation named "new".
  5. digoal=# \dt
  6. List of relations
  7. Schema | Name | Type | Owner
  8. --------+---------+-------+----------
  9. public | digoal | table | postgres
  10. public | digoal1 | table | postgres
  11. public | test | table | postgres
  12. (3 rows)

无法删表了

  1. digoal=# drop table digoal;
  2. ERROR: event:ddl_command_start, command:DROP TABLE
  3. digoal=# \d digoal
  4. Table "public.digoal"
  5. Column | Type | Modifiers
  6. --------+---------+-----------
  7. id | integer |

测试其他用户是否会有影响 (未受到影响)

  1. digoal=# \c digoal digoal
  2. You are now connected to database "digoal" as user "digoal".
  3. digoal=> create table tbl(id int);
  4. CREATE TABLE
  5. digoal=> drop table tbl;
  6. DROP TABLE

例子2,用事件触发器来实现recycle bin
我们创建一个schema来存放重命名的对象。

用一个表来记录重命名的前后关系。

  1. postgres=# create schema recyclebin;
  2. 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);
  3. CREATE TABLE

创建一个事件触发器函数进行测试:

在遇到sql_drops事件时被触发,执行rename的动作,而不是真实的DROP TABLE。从而实现recycle bin的功能。

  1. CREATE OR REPLACE FUNCTION test_event_trigger_for_drops()
  2. RETURNS event_trigger LANGUAGE plpgsql AS $$
  3. DECLARE
  4. obj record;
  5. v_type_name text[] := '{}'::text[];
  6. v_nsp_name text[] := '{}'::text[];
  7. v_obj_name text[] := '{}'::text[];
  8. v_tg_tag text := TG_TAG;
  9. v_crt_time timestamp := now();
  10. i int := 1;
  11. v_new_nsp_name text := 'recyclebin';
  12. v_new_obj_name text;
  13. have_table boolean := false;
  14. x text;
  15. tt text := '';
  16. v_sql text[];
  17. v_sqlend text := '';
  18. v_cname text;
  19. BEGIN
  20. FOR obj IN SELECT * FROM pg_event_trigger_dropped_objects()
  21. LOOP
  22. RAISE NOTICE '% dropped object: % %.% %',
  23. v_tg_tag,
  24. obj.object_type,
  25. obj.schema_name,
  26. obj.object_name,
  27. obj.object_identity;
  28. v_type_name := array_append(v_type_name, obj.object_type);
  29. v_nsp_name := array_append(v_nsp_name, obj.schema_name);
  30. v_obj_name := array_append(v_obj_name, obj.object_name);
  31. if (obj.object_type = 'table') then
  32. have_table := true;
  33. end if;
  34. END LOOP;
  35. if ( have_table = true ) then
  36. RAISE NOTICE 'Move table to recycle bin';
  37. RAISE exception 'Found table in drop list.';
  38. end if;
  39. EXCEPTION WHEN others then
  40. raise notice 'begin: ';
  41. raise notice '-----------------------------------------------------------';
  42. FOREACH x in ARRAY v_obj_name LOOP
  43. if v_type_name[i] = 'table' then
  44. v_new_obj_name := 'md5'||md5(random()::text||clock_timestamp()::text);
  45. v_sql[1] := 'alter table '||v_nsp_name[i]||'.'||x||' set schema '||v_new_nsp_name||';';
  46. v_sql[2] := 'alter table '||v_new_nsp_name||'.'||x||' rename to '||v_new_obj_name||';';
  47. v_sql[3] := 'insert into recyclebin.trace(type_name,nsp_name,obj_name,tg_tag,new_nsp_name,new_obj_name,crt_time)
  48. 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)||');';
  49. v_sqlend := v_sqlend||v_sql[1]||v_sql[2]||v_sql[3];
  50. end if;
  51. i := i+1;
  52. END LOOP;
  53. v_cname := 'myconn';
  54. if (dblink_get_connections() @> array[v_cname] is not true) then
  55. perform dblink_connect(v_cname, 'dbname='||current_database());
  56. else
  57. perform dblink_disconnect(v_cname);
  58. perform dblink_connect(v_cname, 'dbname='||current_database());
  59. end if;
  60. perform dblink_send_query(v_cname, v_sqlend);
  61. perform dblink_disconnect(v_cname);
  62. raise notice 'BEGIN; % COMMIT;', v_sqlend;
  63. raise notice '-----------------------------------------------------------';
  64. raise exception 'Good Luck.';
  65. END;
  66. $$;

创建事件触发器:

  1. CREATE EVENT TRIGGER test_event_trigger_for_drops
  2. ON sql_drop
  3. EXECUTE PROCEDURE test_event_trigger_for_drops();

测试:

  1. postgres=# create table test(id int);
  2. CREATE TABLE
  3. postgres=# create table test1(id int) inherits(test);
  4. NOTICE: merging column "id" with inherited definition
  5. CREATE TABLE
  6. postgres=# create table test2(id int) inherits(test);
  7. NOTICE: merging column "id" with inherited definition
  8. CREATE TABLE
  9. postgres=# create table test3(id int) inherits(test);
  10. NOTICE: merging column "id" with inherited definition
  11. CREATE TABLE

删除

  1. postgres=# drop table test cascade;
  2. NOTICE: drop cascades to 3 other objects
  3. DETAIL: drop cascades to table test1
  4. drop cascades to table test2
  5. drop cascades to table test3
  6. NOTICE: DROP TABLE dropped object: table public.test public.test
  7. NOTICE: DROP TABLE dropped object: type public.test public.test
  8. NOTICE: DROP TABLE dropped object: type public._test public.test[]
  9. NOTICE: DROP TABLE dropped object: table public.test1 public.test1
  10. NOTICE: DROP TABLE dropped object: type public.test1 public.test1
  11. NOTICE: DROP TABLE dropped object: type public._test1 public.test1[]
  12. NOTICE: DROP TABLE dropped object: table public.test2 public.test2
  13. NOTICE: DROP TABLE dropped object: type public.test2 public.test2
  14. NOTICE: DROP TABLE dropped object: type public._test2 public.test2[]
  15. NOTICE: DROP TABLE dropped object: table public.test3 public.test3
  16. NOTICE: DROP TABLE dropped object: type public.test3 public.test3
  17. NOTICE: DROP TABLE dropped object: type public._test3 public.test3[]
  18. NOTICE: Move table to recycle bin
  19. NOTICE: begin:
  20. NOTICE: -----------------------------------------------------------
  21. 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)
  22. 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)
  23. 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)
  24. 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)
  25. values ('table','public','test3','DROP TABLE','recyclebin','md5de3d73b4c8d0afe47cbcd61ba9a8f7e1','2016-12-24 11:58:23.497347'); COMMIT;
  26. NOTICE: -----------------------------------------------------------
  27. ERROR: Good Luck.
  28. CONTEXT: PL/pgSQL function test_event_trigger_for_drops() line 66 at RAISE

表已经被转移到recyclebin了。

  1. postgres=# \dt
  2. No relations found.
  3. postgres=# select * from recyclebin.trace;
  4. id | type_name | nsp_name | obj_name | tg_tag | new_nsp_name | new_obj_name | crt_time
  5. ----+-----------+------------+----------+------------+--------------+-------------------------------------+----------------------------
  6. 1 | table | public | test | DROP TABLE | recyclebin | md56015e0b40b55526be0bc35591f749438 | 2016-12-24 11:12:39.535339
  7. 2 | table | public | test | DROP TABLE | recyclebin | md5697b9f6a4ddca7c5d3a5e555bc13524e | 2016-12-24 11:17:56.478331
  8. 3 | table | public | test | DROP TABLE | recyclebin | md56e003aa8d4580d773dfb9ed218095350 | 2016-12-24 11:18:26.072341
  9. 4 | table | public | test | DROP TABLE | recyclebin | md5987a31420a41c0335096e13bf3104bee | 2016-12-24 11:24:28.805337
  10. 7 | table | public | test | DROP TABLE | recyclebin | md5ab75b9adad19028dc139c9594c1ad283 | 2016-12-24 11:30:57.128355
  11. 9 | table | recyclebin | test | DROP TABLE | recyclebin | md556dc1d9f932e19958780ec9088015531 | 2016-12-24 11:42:34.987422
  12. 10 | table | public | test | DROP TABLE | recyclebin | md5dfe0ecb69b7f0c559b5512ac2cc3ac5f | 2016-12-24 11:43:00.849329
  13. 11 | table | public | test | DROP TABLE | recyclebin | md5be413eefcb0a06359a813ea235aec991 | 2016-12-24 11:51:46.498338
  14. 12 | table | public | test1 | DROP TABLE | recyclebin | md5c703b51dfd054b08b092df4e4ad24074 | 2016-12-24 11:51:46.498338
  15. 13 | table | public | test2 | DROP TABLE | recyclebin | md50b59927a9a61e0324d4434a246da79b6 | 2016-12-24 11:51:46.498338
  16. 14 | table | public | test | DROP TABLE | recyclebin | md584b8a4bd192773d3bfe554016b4f4b46 | 2016-12-24 11:58:23.497347
  17. 15 | table | public | test1 | DROP TABLE | recyclebin | md59e63afc3c45a334f054e1a04711c1b9b | 2016-12-24 11:58:23.497347
  18. 16 | table | public | test2 | DROP TABLE | recyclebin | md599fc68c328a54d5a4ded5cb98b5468eb | 2016-12-24 11:58:23.497347
  19. 17 | table | public | test3 | DROP TABLE | recyclebin | md5de3d73b4c8d0afe47cbcd61ba9a8f7e1 | 2016-12-24 11:58:23.497347
  20. (14 rows)

查看当前数据库中存在的事件触发器

digoal=# select * from pg_event_trigger ;    

6. 类型转换

例如要从一个类型转换为另一个类型。

语法

  1. CAST ( expression AS type )
  2. expression::type

例子

  1. postgres=# select cast('2017-01-01' as date);
  2. date
  3. ------------
  4. 2017-01-01
  5. (1 row)
  6. postgres=# select '2017-01-01'::date;
  7. date
  8. ------------
  9. 2017-01-01
  10. (1 row)

自定义转换函数

语法

  1. postgres=# \h create cast
  2. Command: CREATE CAST
  3. Description: define a new cast
  4. Syntax:
  5. CREATE CAST (source_type AS target_type)
  6. WITH FUNCTION function_name (argument_type [, ...])
  7. [ AS ASSIGNMENT | AS IMPLICIT ]
  8. CREATE CAST (source_type AS target_type)
  9. WITHOUT FUNCTION
  10. [ AS ASSIGNMENT | AS IMPLICIT ]
  11. CREATE CAST (source_type AS target_type)
  12. WITH INOUT
  13. [ AS ASSIGNMENT | AS IMPLICIT ]

例子

  1. postgres=# select '12.1a.1'::text::numeric;
  2. ERROR: invalid input syntax for type numeric: "12.1a.1"
  3. postgres=# create or replace function text_to_numeric(text) returns numeric as $$
  4. select to_number($1,'9999999999999999999999999.99999999999999');
  5. $$ language sql strict;
  6. CREATE FUNCTION
  7. postgres=# select text_to_numeric('12.1a.1');
  8. text_to_numeric
  9. -----------------
  10. 12.11
  11. (1 row)
  12. postgres=# create cast (text as numeric) with function text_to_numeric(text) ;
  13. CREATE CAST
  14. postgres=# select '12.1a.1'::text::numeric;
  15. numeric
  16. ---------
  17. 12.11
  18. (1 row)
声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/我家小花儿/article/detail/781549
推荐阅读
相关标签
  

闽ICP备14008679号