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

参考网址

https://www.postgresql.org/docs/13/index.html

http://www.postgres.cn/docs/13/index.html

posted @   柯南。道尔  阅读(234)  评论(0)  编辑  收藏  举报
相关博文：
· SQL语言
· 在Linux环境下安装PostgreSQL
· PostgreSQL-触发器
· PL/SQL编程
· PostgreSQL学习笔记-5.基础知识：触发器、索引
阅读排行：
· Manus重磅发布：全球首款通用AI代理技术深度解析与实战指南
· 被坑几百块钱后，我竟然真的恢复了删除的微信聊天记录！
· 没有Manus邀请码？试试免邀请码的MGX或者开源的OpenManus吧
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
· 【自荐】一款简洁、开源的在线白板工具 Drawnix
点击右上角即可分享
微信分享提示