PostgreSQL触发器函数
1 触发器函数管理命令
管理函数的快捷命令
\ef 创建一个新的函数。
\df 显示已经创建的函数。
\df+ somefunc 显示这个函数的详细定义
\ef somefunc 编辑这个函数， 编辑保存退出之后，要执行 \g ,刚才的修改才会生效。
\g 使刚才的修改生效。
查看触发器函数详细定义SQL
\x on;
select * from pg_proc where proname='log_history_insert_trigger';2 继承式分区-创建触发器函数
2.1 创建触发器函数
在继承式表分区会用到触发器函数，分区表从主表继承，主表不包含数据，不在主表上定义任何检查约束，此时需要通过触发器函数将数据指定相应的分区。
创建主表和分区
create table log_history(id int not null,logdate date not null,num int);
create table log_history_2011(check (logdate >= date'2011-01-01' and logdate < date'2012-01-01')) inherits(log_history);
create table log_history_2012(check (logdate >= date'2012-01-01' and logdate < date'2013-01-01')) inherits(log_history);
create table log_history_2013(check (logdate >= date'2013-01-01' and logdate < date'2014-01-01')) inherits(log_history);
create table log_history_2014(check (logdate >= date'2014-01-01' and logdate < date'2015-01-01')) inherits(log_history);
create table log_history_2015(check (logdate >= date'2015-01-01' and logdate < date'2016-01-01')) inherits(log_history);
create table log_history_2016(check (logdate >= date'2016-01-01' and logdate < date'2017-01-01')) inherits(log_history);
create table log_history_2017(check (logdate >= date'2017-01-01' and logdate < date'2018-01-01')) inherits(log_history);
create table log_history_2018(check (logdate >= date'2018-01-01' and logdate < date'2019-01-01')) inherits(log_history);
create table log_history_2019(check (logdate >= date'2019-01-01' and logdate < date'2020-01-01')) inherits(log_history);
create table log_history_2020(check (logdate >= date'2020-01-01' and logdate < date'2021-01-01')) inherits(log_history);
create table log_history_2021(check (logdate >= date'2021-01-01' and logdate < date'2022-01-01')) inherits(log_history);此时插入数据不会插入到分区中
postgres=# insert into log_history values(1,'2021-09-01',1);
INSERT 0 1
postgres=# select * from log_history;
id | logdate | num
----+------------+-----
1 | 2021-09-01 | 1
(1 row)
postgres=# select * from log_history_2021;
id | logdate | num
----+---------+-----
(0 rows)
postgres=# select * from log_history_2021;
id | logdate | num
----+---------+-----
(0 rows)创建触发器函数
#vi log_history_insert_trigger.sql
CREATE OR REPLACE FUNCTION log_history_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF ( NEW.logdate >= DATE '2011-01-01' AND
NEW.logdate < DATE '2012-01-01' ) THEN
INSERT INTO log_history_2011 VALUES (NEW.*);
ELSIF ( NEW.logdate >= DATE '2012-01-01' AND
NEW.logdate < DATE '2013-01-01' ) THEN
INSERT INTO log_history_2012 VALUES (NEW.*);
ELSIF ( NEW.logdate >= DATE '2013-01-01' AND
NEW.logdate < DATE '2014-01-01' ) THEN
INSERT INTO log_history_2013 VALUES (NEW.*);
ELSIF ( NEW.logdate >= DATE '2014-01-01' AND
NEW.logdate < DATE '2015-01-01' ) THEN
INSERT INTO log_history_2014 VALUES (NEW.*);
ELSIF ( NEW.logdate >= DATE '2016-01-01' AND
NEW.logdate < DATE '2017-01-01' ) THEN
INSERT INTO log_history_2016 VALUES (NEW.*);
ELSIF ( NEW.logdate >= DATE '2017-01-01' AND
NEW.logdate < DATE '2018-01-01' ) THEN
INSERT INTO log_history_2017 VALUES (NEW.*);
ELSIF ( NEW.logdate >= DATE '2018-01-01' AND
NEW.logdate < DATE '2019-01-01' ) THEN
INSERT INTO log_history_2018 VALUES (NEW.*);
ELSIF ( NEW.logdate >= DATE '2019-01-01' AND
NEW.logdate < DATE '2020-01-01' ) THEN
INSERT INTO log_history_2019 VALUES (NEW.*);
ELSIF ( NEW.logdate >= DATE '2021-01-01' AND
NEW.logdate < DATE '2022-01-01' ) THEN
INSERT INTO log_history_2021 VALUES (NEW.*);
ELSE
RAISE EXCEPTION 'Date out of range. Fix the log_history_insert_trigger() function!';
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
创建函数
postgres=# \i log_history_insert_trigger.sql重新插入数据
postgres=# insert into log_history values(1,'2021-09-01',1);
INSERT 0 1
postgres=# select * from log_history;
id | logdate | num
----+------------+-----
1 | 2021-09-01 | 1
(1 row)
postgres=# select * from log_history_2021;
id | logdate | num
----+------------+-----
1 | 2021-09-01 | 1
(0 rows)2.2 更改触发器函数
继承式分区添加2023年的新分区
postgres=# create table log_history_2023(check (logdate >= date'2023-01-01' and logdate < date'2024-01-01')) INHERITS (log_history);
CREATE TABLE
postgres=# select inhrelid::regclass,inhparent::regclass,inhseqno from pg_inherits;
inhrelid | inhparent | inhseqno
------------------+-------------+----------
log_history_2011 | log_history | 1
log_history_2012 | log_history | 1
log_history_2013 | log_history | 1
log_history_2014 | log_history | 1
log_history_2015 | log_history | 1
log_history_2016 | log_history | 1
log_history_2017 | log_history | 1
log_history_2018 | log_history | 1
log_history_2019 | log_history | 1
log_history_2020 | log_history | 1
log_history_2021 | log_history | 1
log_history_2023 | log_history | 1更新触发器函数，添加2023年新分区约束
postgres=# \ef log_history_insert_trigger
#进入编辑模式，添加2023年分区约束
CREATE OR REPLACE FUNCTION public.log_history_insert_trigger()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
BEGIN
IF ( NEW.logdate >= DATE '2011-01-01' AND
NEW.logdate < DATE '2012-01-01' ) THEN
INSERT INTO log_history_2011 VALUES (NEW.*);
ELSIF ( NEW.logdate >= DATE '2012-01-01' AND
NEW.logdate < DATE '2013-01-01' ) THEN
INSERT INTO log_history_2012 VALUES (NEW.*);
ELSIF ( NEW.logdate >= DATE '2013-01-01' AND
NEW.logdate < DATE '2014-01-01' ) THEN
INSERT INTO log_history_2013 VALUES (NEW.*);
ELSIF ( NEW.logdate >= DATE '2014-01-01' AND
NEW.logdate < DATE '2015-01-01' ) THEN
INSERT INTO log_history_2014 VALUES (NEW.*);
ELSIF ( NEW.logdate >= DATE '2016-01-01' AND
NEW.logdate < DATE '2017-01-01' ) THEN
INSERT INTO log_history_2016 VALUES (NEW.*);
ELSIF ( NEW.logdate >= DATE '2017-01-01' AND
NEW.logdate < DATE '2018-01-01' ) THEN
INSERT INTO log_history_2017 VALUES (NEW.*);
ELSIF ( NEW.logdate >= DATE '2018-01-01' AND
NEW.logdate < DATE '2019-01-01' ) THEN
INSERT INTO log_history_2018 VALUES (NEW.*);
ELSIF ( NEW.logdate >= DATE '2019-01-01' AND
NEW.logdate < DATE '2020-01-01' ) THEN
INSERT INTO log_history_2019 VALUES (NEW.*);
ELSIF ( NEW.logdate >= DATE '2021-01-01' AND
NEW.logdate < DATE '2022-01-01' ) THEN
INSERT INTO log_history_2021 VALUES (NEW.*);
ELSIF ( NEW.logdate >= DATE '2022-01-01' AND
NEW.logdate < DATE '2023-01-01' ) THEN
INSERT INTO log_history_2022 VALUES (NEW.*);
ELSIF ( NEW.logdate >= DATE '2023-01-01' AND
NEW.logdate < DATE '2024-01-01' ) THEN
INSERT INTO log_history_2023 VALUES (NEW.*);
ELSE
RAISE EXCEPTION 'Date out of range. Fix the log_history_insert_trigger() function!';
END IF;
RETURN NULL;
END;
$function$
#然后输入“wq”保存退出
#然后执行\g使触发器函数变更生效
postgres-# \g
CREATE FUNCTION查看触发器函数定义，可以看到2023年分区已添加
postgres=# \df+ log_history_insert_trigger
List of functions
-[ RECORD 1 ]-------+----------------------------------------------------------------------------------------------
Schema | public
Name | log_history_insert_trigger
Result data type | trigger
Argument data types |
Type | func
Volatility | volatile
Parallel | unsafe
Owner | postgres12
Security | invoker
Access privileges |
Language | plpgsql
Source code | +
| BEGIN +
| IF ( NEW.logdate >= DATE '2011-01-01' AND +
| NEW.logdate < DATE '2012-01-01' ) THEN +
| INSERT INTO log_history_2011 VALUES (NEW.*); +
| ELSIF ( NEW.logdate >= DATE '2012-01-01' AND +
| NEW.logdate < DATE '2013-01-01' ) THEN +
| INSERT INTO log_history_2012 VALUES (NEW.*); +
| ELSIF ( NEW.logdate >= DATE '2013-01-01' AND +
| NEW.logdate < DATE '2014-01-01' ) THEN +
| INSERT INTO log_history_2013 VALUES (NEW.*); +
| ELSIF ( NEW.logdate >= DATE '2014-01-01' AND +
| NEW.logdate < DATE '2015-01-01' ) THEN +
| INSERT INTO log_history_2014 VALUES (NEW.*); +
| ELSIF ( NEW.logdate >= DATE '2016-01-01' AND +
| NEW.logdate < DATE '2017-01-01' ) THEN +
| INSERT INTO log_history_2016 VALUES (NEW.*); +
| ELSIF ( NEW.logdate >= DATE '2017-01-01' AND +
| NEW.logdate < DATE '2018-01-01' ) THEN +
| INSERT INTO log_history_2017 VALUES (NEW.*); +
| ELSIF ( NEW.logdate >= DATE '2018-01-01' AND +
| NEW.logdate < DATE '2019-01-01' ) THEN +
| INSERT INTO log_history_2018 VALUES (NEW.*); +
| ELSIF ( NEW.logdate >= DATE '2019-01-01' AND +
| NEW.logdate < DATE '2020-01-01' ) THEN +
| INSERT INTO log_history_2019 VALUES (NEW.*); +
| ELSIF ( NEW.logdate >= DATE '2021-01-01' AND +
| NEW.logdate < DATE '2022-01-01' ) THEN +
| INSERT INTO log_history_2021 VALUES (NEW.*); +
| ELSIF ( NEW.logdate >= DATE '2022-01-01' AND +
| NEW.logdate < DATE '2023-01-01' ) THEN +
| INSERT INTO log_history_2022 VALUES (NEW.*); +
| ELSIF ( NEW.logdate >= DATE '2023-01-01' AND +
| NEW.logdate < DATE '2024-01-01' ) THEN +
| INSERT INTO log_history_2023 VALUES (NEW.*); +
| ELSE +
| RAISE EXCEPTION 'Date out of range. Fix the log_history_insert_trigger() function!';+
| END IF; +
| RETURN NULL; +
| END; +
|
Description |