PostgreSQL-用户定义的函数
PostgreSQL-用户定义的函数
PostgreSQL是可扩展的，PostgreSQL服务器能够通过动态载入把用户编写的代码结合到自身中。也就是用户能够指定一个实现了新类型或函数的对象代码文件，并且PostgreSQL按要求载入它。
主要讲的是查询语言函数与过程语言函数中的PL/pgSQL(SQL过程语言)，其他函数只是概述。
用户定义的函数
PostgreSQL提供四种函数：
- 查询语言函数(用SQL编写的函数)
- 过程语言函数(用除SQL和C之外的语言编写的函数)
- 内部函数(由C编写，此类函数被静态链接到PostgreSQL服务器中)
- C语言函数(由C编写，此类函数被编译成动态载入对象，在PostgreSQL服务器需要时载入)
每一类函数可以采用基本类型、组合类型或者它们的组合作为参数。
每一类函数可以返回一个基本类型或一个组合类型，也可以返回一个基本类型或组合类型的集合。
允许函数重载，同一个名称用于多个不同函数，只有它们具有可区分的输入参数类型。
PostgreSQL允许用除SQL和c之外的语言编写函数，这些语言被称为过程语言(PL)。
动态载入是把内部函数与C语言函数区分开的特性，两者的编码习惯是一致的。
函数的SQL命令
创建函数(CREATE FUNCTION)
CREATE [OR REPLACE] FUNCTION
函数名 ([参数模式] [参数名] 参数类型)
[RETURNS 返回类型 | RETURNS TABLE (列名 列类型)]
AS $$
definition
$$ LANGUAGE 语言名;
CREATE FUNCTION :创建一个新函数
CREATE OR REPLACE FUNCTION :创建一个新函数或者替换一个现有的函数
参数模式 :IN、OUT、INOUT等等。如果省略，默认为IN。OUT参数不能和RETURNS一起使用
definition :一个定义该函数的字符串常量。可以是一个内部函数名、一个对象文件的路径、一个SQL命令或者用一种过程语言编写的文本
删除函数(DROP FUNCTION)
DROP FUNCTION [IF EXISTS] 函数名 [([参数模式] [参数名] 参数类型)] [CASCADE | RESTRICT]
DROP FUNCTION :删除一个函数
IF EXISTS : 如果函数不存在则不抛出错误而是报告消息
CASCADE :自动删除依赖于该函数的对象(如：触发器，操作符)，然后删除所有依赖于该对象的对象
RESTRICT :如果有任何对象依赖于该函数则拒绝删除，默认为 RESTRICT
eg: 删除多个重载函数
DROP FUNCTION test_sum(integer);
DROP FUNCTION test_sum(numeric);
查询语言函数(SQL函数)
SQL函数的主体必须是一个有分号(;)分隔的SQL语句的列表。
SQL函数被声明返回void，最后一个语句必须是一个SELECT或者一个带有RETURNING子句的INSERT、UPDATE、DELETE。
SQL函数的返回类型可以通过SETOF xx 或者 RETURNS TABLE() 方式声明返回一个集合(多个行)
SQL函数的参数
一个SQL函数的参数可以在函数体中用名称或编号引用
名称的方式 :
CREATE FUNCTION add_test1(a int,b int)
RETURNS int
AS $$
SELECT a + b;
$$ LANGUAGE SQL;
编号引用
CREATE FUNCTION add_test2(int,int)
RETURNS int
AS $$
SELECT $1 + $2;
$$ LANGUAGE SQL;
基本类型的SQL函数
无参函数
CREATE FUNCTION print_hello()
RETURNS text
AS $$
SELECT 'HELLO';
$$ LANGUAGE SQL;
有参函数
CREATE FUNCTION print_input_text(content text)
RETURNS text
AS $$
SELECT content;
$$ LANGUAGE SQL;
无返回值函数
CREATE FUNCTION no_return_test()
RETURNS void
AS $$
SELECT 'NO MEANING';
$$ LANGUAGE SQL;
带有输出参数的SQL函数
单个输出参数
CREATE FUNCTION add_test(IN x int, IN y int, OUT sum int)
AS $$
SELECT x + y;
$$ LANGUAGE SQL
多个输出参数
CREATE FUNCTION add_n_test(IN x int, IN y int, OUT sum1 int, OUT sum2 int)
AS $$
SELECT x + x , y + y;
$$ LANGUAGE SQL;
组合类型上的SQL函数
在使用组合类型作为参数时，需要指定参数的属性(域)
前提
新增一个emp表作为输入的组合类型
create TABLE emp(
name text,
salary numeric,
age integer;
);
INSERT INTO emp VALUES ('Sam', '5000', 45);
INSERT INTO emp VALUES ('James', '4000', 39);
组合类型作为参数
CREATE FUNCTION double_salary(emp)
RETURNS numeric
AS $$
SELECT $1.salary * 2;
$$ LANGUAGE SQL;
SELECT name , double_salary(emp.*) from emp;
注：
$1.xx :需要使用行值的一个域，如 $1.salary
table_name.* :表示用一个表的整个当前行作为一个组合值
组合类型作为返回值
CREATE FUNCTION new_emp()
RETURNS emp
AS $$
SELECT text 'test_name' AS name,
1000.0 AS salary,
35 AS age;
$$ LANGUAGE SQL;
将函数返回值作为一个值
select new_emp();
将函数返回值作为一个表
select (new_emp()).*;
select * from new_emp();
返回集合的SQL函数
当一个SQL函数被声明返回SETOF xx 时，该函数的最后一个查询执行完，会输出每一行
前提
新增一个foo表和tab表用于返回集合
CREATE TABLE foo (fooid int, foosubid int, fooname text);
INSERT INTO foo VALUES (1, 1, 'Joe');
INSERT INTO foo VALUES (1, 2, 'Ed');
INSERT INTO foo VALUES (2, 1, 'Mary');
CREATE TABLE tab (y int, z int);
INSERT INTO tab VALUES (1, 2), (3, 4), (5, 6), (7, 8);
返回所有表数据
CREATE OR REPLACE FUNCTION display_foo(int)
RETURNS SETOF foo
AS $$
SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;
select * from display_foo(1);
返回带有输出参数定义的列
CREATE FUNCTION sum_tab(x int, OUT sum1 int, OUT sum2 int)
RETURNS SETOF record
AS $$
SELECT $1 + tab.y, $1 + tab.y FROM tab;
$$ LANGUAGE SQL;
select * from sum_tab(10);
返回TABLE的SQL函数
RETURNS TABLE (列名 列类型)，此种方式与SETOF record等效
CREATE OR REPLACE FUNCTION sum_tab(x int)
RETURNS TABLE(sum1 int, sum2 int)
AS $$
SELECT $1 + tab.y, $1 + tab.y FROM tab;
$$ LANGUAGE SQL;
select * from sum_tab(10);
过程语言函数
PostgreSQL运行处理SQL和C之外的其他语言编写用户定义的函数。这些其他语言通常被称为过程语言(PL)，是一种用于PostgreSQL数据库系统的可载入的过程语言。并不内建在PostgreSQL服务器中，通过可装载模块提供
在PostgreSQL的标准发布中有四种过程语言可用：
- PL/pgSQL(SQL过程语言):用SQL语言编写函数
- PL/Tcl(Tcl过程语言):用Tcl语言编写函数
- PL/Perl(Perl过程语言):用Perl语言编写函数
- PL/Python(Python过程语言):用Python编写函数
PL/pgSQL(SQL过程语言)
PL/pgSQL的设计目的是创建一种这样的可载入过程语言
- 可以被用来创建函数和触发器过程
- 对SQL语言增加控制结构
- 可以执行复杂计算
- 继承所有用户定义类型、函数、操作符
- 可以被定义为受服务器信任
- 便于使用
注:
PostgreSQL9.0之后的版本，PL/pgSQL是默认被安装的
通过PL/pgSQL可以使用SQL中所有的数据类型、操作符、函数
PL/pgSQL-结构
函数的结构:
CREATE FUNCTION 函数名(参数名 参数类型)
RETURNS 返回类型
AS $$
函数体
$$ LANGUAGE plpgsql;
PL/pgSQL是一种块结构的语言。一个函数体的完整文本必须是一个块。
一个块的定义:
$$
[DECLARE 声明语句]
BEGIN
语句
END;
$$
PL/pgSQL-声明
在一个块中使用的所有变量必须在该块的声明语句下声明
DECLARE
变量名 [CONSTANT] 变量类型 [NOT NULL] [ {DEFAULT | := | =} 表达式];
给定DEFAULT则会指定进入该块时此变量的初始值，没有给定则此变量会初始化为SQL空值
CONSTANT会阻止该变量在初始化后被赋值
指定NOT NULL则此变量必须指定一个非空默认值，如果赋值为空值则会报错
eg：创建一个函数，声明两个text变量
CREATE FUNCTION display_sum_text(a text, b text)
RETURNS text
AS $$
DECLARE
local_a text := a;
local_b text := b;
BEGIN
RETURN local_a || local_b;
END;
$$ LANGUAGE plpgsql;
注：
':=' 为赋值字符
'||' 为字符串拼接字符
ALAS
新变量名 ALIAS FOR 旧变量名
可以为任意变量声明一个别名
eg:创建一个函数，在声明中起别名
CREATE OR REPLACE FUNCTION display_sum_text(text, b text)
RETURNS text
AS $$
DECLARE
local_a ALIAS FOR $1;
local_b ALIAS FOR b;
BEGIN
RETURN local_a || local_b;
END;
$$ LANGUAGE plpgsql;
复制类型
varibale%TYPE
%TYPE提供了一个变量或一列的数据类型
eg: 创建一个函数，声明与foo表的fooname列类型一致的变量
CREATE FUNCTION show_table_fooname()
RETURNS text
AS $$
DECLARE
name foo.fooname%TYPE;
BEGIN
SELECT fooname into name from foo where fooid = 1;
RETURN name;
END;
$$ LANGUAGE plpgsql;
行类型
变量名 表名%ROWTYPE
一个组合类型的变量被称为一个行变量
eg：创建一个函数，声明foo表字段类型相同的变量
CREATE FUNCTION show_table_foo()
RETURNS text
AS $$
DECLARE
foo_row foo%ROWTYPE;
BEGIN
SELECT * INTO foo_row from foo where fooid = 1;
RETURN 'fooid :' || foo_row.fooid::text ||' fooname :' || foo_row.fooname;
END;
$$ LANGUAGE plpgsql;
'::'强制转换符，相当于cast函数，如果类型无法被强制转换则会报错
记录类型
name RECORD
记录类型的变量与行类型变量类似，但没有预定义的结构。一个记录变量的子结构可以在每次被赋值时改变。
对于返回类型为record的函数，当调用查询时就已经决定了其结果，而记录变量能够随时改变其行结构
eg：创建一个函数，声明foo表字段类型相同的变量
CREATE FUNCTION show_table_foo()
RETURNS text
AS $$
DECLARE
foo_row RECORD;
BEGIN
SELECT * INTO foo_row from foo where fooid = 1;
RETURN 'fooid :' || foo_row.fooid::text ||' fooname :' || foo_row.fooname;
END;
$$ LANGUAGE plpgsql;
PL/pgSQL-基本语句
赋值
varibale [:= | =] expression
eg:
count := 10;
sum := 20 + 10;
执行一个没有结果的查询
有时计算一个表达式或者SELECT 查询需要抛弃结果
PERFORM query
执行query会丢掉结果，SELECT查询则会将SELECT 替换为PREFORM
如果此种查询产生至少一行记录则特殊变量 FOUND 会为真，反之则为假
eg: 创建一个函数，执行无结果查询，判断有没有查到foo表中的一条记录
CREATE FUNCTION test_foo_exist()
RETURNS boolean
AS $$
BEGIN
PERFORM 1 FROM foo WHERE fooid = 1;
return found;
END;
$$ LANGUAGE plpgsql;
执行一个有结果返回的查询
一个产生单一行的SQL命令的结果可以赋值给一个记录变量、行类型变量或标量变量列表
SELECT 表达式 INTO target FROM ...
INSERT ... RETURNING 表达式 INTO target;
UPDATE ... RETURNING 表达式 INTO target;
DELETE ... RETURNING 表达式 INTO target;
target可以是一个几率变量、行类型变量、标量变量列表
eg: 创建一个函数，将查询foo表的一条记录赋给记录变量
CREATE FUNCTION test_foo_assign_variable()
RETURNS RECORD
AS $$
DECLARE
foo_variable RECORD;
BEGIN
SELECT * INTO foo_variable from foo where fooid = 1;
RETURN foo_variable;
END;
$$ LANGUAGE plpgsql;
执行动态命令
当想要在函数中产生动态命令，每次执行涉及到不同表或不同数据类型，可以使用EXECUTE命令
EXECUTE 被执行的命令字符串 [INTO target] [USING 表达式]
target可以是记录变量、行变量、普通变量
USING表达式提供要被插入到执行命令中值
eg：创建一个函数，将带有条件的查询foo表并把结果返回
CREATE FUNCTION display_foo_one(id int, test name)
RETURNS text
AS $$
DECLARE
subid text;
BEGIN
EXECUTE 'select foosubid from foo where fooid = $1 and fooname = $2' INTO subid USING id,test;
RETURN subid;
END;
$$ LANGUAGE plpgsql;
判断执行命令的效果
检查一个名为FOUND的boolean类型的特殊变量，每次调用PL/pgSQL函数时，FOUND开始都为假
- 当SELECT INTO语句赋值了一行，FOUND赋值为真，如果没有返回行则为假
- 当PERFORM语句生成一行或多行，FOUND赋值为真，如果没有产生行则为假
- 当UPDATE、INSERT、DELETE语句影响了至少一行，FOUND赋值为真，如果没有影响则为假
- 如果FETCH语句返回了一行，FOUND赋值为真，如果没有返回则为假
- 如果MOVE语句成功重定位了游标，FOUND赋值为真，反之则为假
- 如果FOR或FOREACH迭代了一次或多次，FOUND赋值为真，反之则为假
- 如果查询返至少一行，RETURN QUERY和RETURN QUERYK EXECUTE语句，FOUND赋值为真，如果没有返回行则为假
其他PL/pgSQL语句不会改变FOUND的状态
FOUND是每个PL/pgSQL函数的局部变量，只影响当前的函数
什么也不做
NULL;
eg: 创建一个函数体，不做任何SQL操作
CREATE FUNCTION do_nothing()
RETURNS void
AS $$
BEGIN
NULL;
END;
$$ LANGUAGE plpgsql;
PL/pgSQL-报告消息和抛出错误
RAISE [level] 消息
level选项指定了错误的级别，有DEBUG、LOG、INFO、NOTICE、WARNING、EXCEPTION
默认级别是EXCEPTION，EXCEPTION会抛出错误(通常中止当前事务)，其他级别则仅产生不同级别的消息
这些消息是报告给客户端还是写到服务器日志，还是两者均记录，由配置变量log_min_messages和client_min_messages来控制
eg : 产生一个NOTICE级别的消息,关于变量i的值
RAISE NOTICE 'variable i = %',i;
PL/pgSQL-控制结构
从一个函数返回(RETURN)
RETURN 表达式
eg: 创建一个函数，返回两个数值类型的和
CREATE OR REPLACE FUNCTION sum_test(a int, b int)
RETURNS int
AS $$
DECLARE
sum int := 0;
BEGIN
sum := a + b;
RETURN sum;
END;
$$ LANGUAGE plpgsql;
RETURN QUERY 查询语句
用于返回集合
eg: 创建一个函数，返回集合
CREATE OR REPLACE FUNCTION display_foo()
RETURNS SETOF foo
AS $$
BEGIN
RETURN QUERY
SELECT * FROM foo;
END;
$$ LANGUAGE plpgsql;
select * from display_foo();
条件-IF
IF有三种形式
IF ... THEN ... END IF
IF ... ELSE ... END IF
IF ... THEN ... ELSIF ...THEN ... ELSE ...END IF
eg: 创建一个函数，判断一个数值类型的大小
CREATE OR REPLACE FUNCTION judge_num(num int)
RETURNS text
AS $$
DECLARE
result_num text := '';
BEGIN
IF num > 0 THEN
result_num := '大于0';
ELSIF num = 0 THEN
result_num := '等于0';
ELSE
result_num := '小于0';
END IF;
RETURN result_num;
END;
$$ LANGUAGE plpgsql;
条件-CASE
CASE有两种形式
简单CASE
CASE 搜索表达式
WHEN 表达式 THEN
语句
WHEN 表达式 THEN
语句
ELSE
语句
END CASE;
搜索CASE
CASE
WHEN 判断表达式 THEN
语句
WHEN 判断表达式 THEN
语句
ELSE
语句
END CASE;
eg: 创建一个函数，判断分数
CREATE FUNCTION judge_score(score numeric)
RETURNS text
AS $$
BEGIN
CASE
WHEN score < 60 THEN
RETURN '未及格';
ELSE
RETURN '及格';
END CASE;
END;
$$ LANGUAGE plpgsql;
循环-LOOP
...
LOOP
语句
END LOOP
...
LOOP定义一个无条件的循环，它会无限重复直到被 EXIT 或 RETURN 语句终止
EXIT ... [WHEN 判断表达式]
EXIT 此层循环被终止，如果指定了WHEN则在判断表达式为真时退出循环
CONTINUE ... [WHEN 判断表达式]
CONTINUE 此层循环的下一次迭代开始，如果指定了WHEN则在判断表达式为真时进行下一次迭代
eg: 创建一个函数，计算1-10之间的奇数之和
CREATE FUNCTION sum_odd_num()
RETURNS int
AS $$
DECLARE
odd_sum int := 0;
i int := 0;
BEGIN
LOOP
i := i + 1;
EXIT WHEN i > 10;
CONTINUE WHEN mod(i,2) = 0;
odd_sum := odd_sum + i;
END LOOP;
RETURN odd_sum;
END;
$$
LANGUAGE plpgsql;
注:
mod(a,b): 取模函数，计算a/b的余数
循环-FOR
...
FOR 变量名 IN [REVERSE] 表达式 .. 表达式 [ BY 表达式]
LOOP
语句
END LOOP;
...
FOR 会创建一个在一个整数范围上迭代的循环，变量名自动定义为integer类型。给出范围的上下界的两个表达式在进入循环时计算一次，如果没有指定BY子句，迭代步长默认为1，如果没有指定REVERSE,那么每次迭代后步长值会被增加
eg: 创建一个函数，计算1-10之间的偶数之和
CREATE FUNCTION sum_even_num()
RETURNS int
AS $$
DECLARE
even_sum int := 0;
i int := 0;
BEGIN
FOR i IN REVERSE 10 .. 1 BY 2
LOOP
even_sum := even_sum + i;
END LOOP;
RETURN even_sum;
END;
$$
LANGUAGE plpgsql;
循环-FOREACH(数组循环)
...
FOREACH target IN ARRAY 表达式
LOOP
语句
END LOOP;
...
target变量会被逐一赋予数组每一个元素值
eg: 创建一个函数，计算数组之和
CREATE FUNCTION sum_array()
RETURNS int
AS $$
DECLARE
arr1 int[] := array[1,2,3];
i int;
array_sum int := 0;
BEGIN
FOREACH i IN ARRAY arr1
LOOP
array_sum := i + array_sum;
RAISE NOTICE 'row = %', i;
END LOOP;
RETURN array_sum;
END;
$$ LANGUAGE plpgsql;
注:
RAISE NOTICE 报告消息，在执行会报告给客户端
循环-WHILE
...
WHILE 判断表达式 LOOP
语句
END LOOP
...
只要判断表达式为真，就会重复语句序列，每次进入到循环体之前都会检查判断表达式
eg: 创建一个函数，计算1-10数值之和
CREATE OR REPLACE FUNCTION sum_num()
RETURNS int
AS $$
DECLARE
num_sum int DEFAULT 0;
i int DEFAULT 1;
BEGIN
WHILE i<=10
LOOP
num_sum := num_sum +i;
i := i+1;
END LOOP;
RETURN num_sum;
END;
$$ LANGUAGE plpgsql;
PL/pgSQL-触发器函数
触发器又分为触发器(常规触发器，关联在一个表上只捕捉DML事件)和事件触发器(关联数据库，捕捉DDL事件)
触发器
一个触发器声明了当执行一种操作时，数据库自动执行一个特殊函数(触发器函数)。
触发器可以被附加到表(分区|不分区)、视图、外部表。
触发器函数必须在触发器创建之前被定义好。
触发器函数必须定义为一个没有参数的函数，返回类型为trigger。
触发器函数可以用于多个触发器
触发器的SQL命令
创建触发器(CREATE TRIGGER)
CREATE TRIGGER 触发器名
{BEFORE | AFTER | INSTEAD OF} {event [OR event]}
ON 表名
FOR EACH {ROW | STATEMENT}
EXECUTE {FUNCTION | PROCEDURE} 触发器函数名()
event :表示执行的操作，有 INSERT, UPDATE, DELETE, TRUNCATE，可以指定多个操作如： INSERT OR DELETE
CREATE TRIGGER : 创建一个触发器
触发器将被关联到指定的表、视图、外部表并且在表上执行特定操作时会执行触发器函数
BEFORE: 触发器指定为在执行该操作之前触发 (INSERT, UPDATE, DELETE 操作之前)
AFTER: 触发器指定为在执行该操作之后触发 (INSERT, UPDATE, DELETE 操作之后)
INSTEAD OF: 触发器在执行该操作时触发并取代该操作 (INSERT, UPDATE, DELETE 操作时)
FOR EACH ROW: (行级触发器) 触发器会对该操作修改的每一行都会调用一次。eg:每修改行一次就执行一次触发器函数
FOR EACH STATEMENT: (语句级触发器) 触发器会对给定的操作(语句)执行一次。eg:每执行一次操作(语句)就执行一次触发器函数
删除触发器(DROP TRIGGER)
DROP TRIGGER [IF EXISTS] 触发器名 ON 表名 [CASCADE | RESTRICT]
DROP TRIGGER : 删除一个触发器
IF EXISTS : 如果函数不存在则不抛出错误而是报告消息
CASCADE :自动删除依赖于该函数的对象(如：触发器，操作符)，然后删除所有依赖于该对象的对象
RESTRICT :如果有任何对象依赖于该函数则拒绝删除，默认为 RESTRICT
eg: 删除一个关联在emp表上的触发器emp_stamp
DROP TRIGGER emp_stamp ON emp;
触发器关联表
-表示无法关联到任何表
| 触发时机 | 操作事件 | 行级 | 语句级 |
|---|---|---|---|
| BEFORE | INSERT|UPDATE|DELETE | 表|外部表 | 表|视图|外部表 |
| BEFORE | TRUNCATE | - | 表 |
| AFTER | INSERT|UPDATE|DELETE | 表|外部表 | 表|视图|外部表 |
| AFTER | TRUNCATE | - | 表 |
| INSTEAD OF | INSERT|UPDATE|DELETE | 视图 | - |
| INSTEAD OF | TRUNCATE | - | - |
eg: 假设已经创建好一个客户表account和触发器函数check_account_update(),创建一个检查account表更新操作的触发器check_update
CREATE TRIGGER check_update
BEFORE UPDATE ON account
FOR EACH ROW
EXECUTE FUNCTION check_account_update();
触发器函数
PL/pgSQL可以被用来在数据更改或者数据库事件上定义触发器函数，除了过程性语言，也可以用C编写触发器函数但不能用纯SQL函数语言来编写。
触发器函数用CREATE FUNCTION命令创建，被声明为一个没有参数并且返回类型为trigger(数据更改的触发器，普通触发器)或者event_trigger(数据库事件触发器，事件触发器)
名为PG_something的特殊变量将被自动创建用来描述触发该调用的条件
触发器函数必须返回NULL或者一个与触发器关联表的结构相同的记录
数据改变的触发器(普通触发器)
当一个PL/pgSQL函数作为触发器函数被调用时，会自动创建一些特殊变量，如下表所示：
| 变量名 | 变量类型 | 说明 |
|---|---|---|
| NEW | record | 在行级触发器中INSERT|UPDATE操作时变量为新数据行。在语句级触发器以及DELETE操作时变量为null |
| OLD | record | 在行级触发器中UPDATE|DELETE操作时变量为旧数据行。在语句级触发器以及INSERT操作时变量为null |
| TG_NAME | name | 变量为触发器名 |
| TG_WHEN | text | 变量为触发器时机(BEFORE|AFTER|INSTEAD OF) |
| TG_LEVEL | text | 变量为触发器级别(ROW|STATEMENT) |
| TG_OP | text | 变量为触发触发器的操作(INSERT|UPDATE|DELETE|TRUNCATE) |
| TG_RELID | oid | 变量为触发器关联表的对象ID(oid) |
| TG_RELNAME | name | 变量为触发器关联的表名，已经废弃使用TG_TABLE_NAME替代 |
| TG_TABLE_NAME | name | 变量为触发器关联的表名 |
| TG_TABLE_SCHEMA | name | 变量为触发器关联的模式名 |
| TG_NARGS | integer | 变量为在CREATE TRIGGER语句中给触发器函数的参数数量 |
| TG_ARGV[] | text[] | 变量为在CREATE TRIGGER语句的参数。下标从0开始计数。非法下标(小于0或大于等于tg_nargs)返回空值 |
eg: 创建一个emp表，emp_stamp触发器函数，emp_stamp触发器，用于检查员工的姓名与薪水
CREATE TABLE emp(
empname text,
salary integer,
last_date timestamp,
last_user text
);
CREATE OR REPLACE FUNCTION emp_stamp()
RETURNS trigger
AS $$
BEGIN
--检查员工名与薪水是否为空
IF NEW.empname IS NULL THEN
RAISE EXCEPTION 'empname is null';
END IF;
IF NEW.salary IS NULL THEN
RAISE EXCEPTION '% salary is null', NEW.empname;
END IF;
--记录修改或添加的操作的用户与时间
NEW.last_date := current_timestamp;
NEW.last_user := current_user;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER emp_stamp
BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW
EXECUTE FUNCTION emp_stamp();
