PostgreSQL函数及触发器的基本使用

函数基础语法

CREATE [OR REPLACE] FUNCTION function_name (arguments)   
RETURNS return_datatype AS $variable_name$  
  DECLARE  
    declaration;  
    [...]  
  BEGIN  
    < function_body >  
    [...]  
    RETURN { variable_name | value }  
  END; LANGUAGE plpgsql;

function_name:指定函数的名称。

arguments: 函数参数

[OR REPLACE]:是可选的，它允许修改/替换现有函数。

DECLARE:定义参数(参数名写在前面 类型写在后面)。

BEGIN~END: 在中间写方法主体。

RETURN:指定要从函数返回的数据类型(它可以是基础，复合或域类型，或者也可以引用表列的类型)。

LANGUAGE:它指定实现该函数的语言的名称。 可以是SQL，PL/pgSQL，C， Python等。

写一个简单自定义函数

-----求和函数
CREATE FUNCTION add(integer, integer) RETURNS integer   
AS 'select $1 + $2;'    
LANGUAGE SQL  
IMMUTABLE    
RETURNS NULL ON NULL INPUT;

qianbase=# select add(1,2);
 add
-----
   3
(1 row)


----自动+1的函数
CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS $$        
	BEGIN                
		RETURN i + 1;        
	END;
$$ LANGUAGE plqbsql;

qianbase=# select increment(10);
 increment
-----------
        11
(1 row)


-----返回值为多个输出参数
CREATE FUNCTION dup(in int, out f1 int, out f2 text)    
	AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$    
LANGUAGE SQL;


qianbase=# select * from dup(42);
 f1 |     f2
----+------------
 42 | 42 is text
(1 row)


-----计算1-100的和（循环语句的使用）
create or replace function sum_1_100()
returns integer as
$$
declare
	sum integer := 0;
begin
	for i in 1..100 loop
		sum := sum + i;
	end loop;
	return sum;
	RAISE NOTiCE 'sum 1..100 =：%',sum;
end
$$
language 'plqbsql';


qianbase=# select sum_1_100();                                                                                            sum_1_100
-----------
      5050
(1 row)

触发器的使用

语法

CREATE  TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name
ON table_name
[
 -- 触发器逻辑....
];

----创建一张表company
CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);
----创建一张audit表，每当company插入一条数据audit就更新一下
CREATE TABLE AUDIT(
   EMP_ID INT NOT NULL,
   ENTRY_DATE TEXT NOT NULL
);

---创建函数函数
CREATE OR REPLACE FUNCTION auditlogfunc() RETURNS TRIGGER AS $example_table$
   BEGIN
      INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, current_timestamp);
      RETURN NEW;
   END;
$example_table$ LANGUAGE plqbsql;

----创建触发器
CREATE TRIGGER example_trigger AFTER INSERT ON COMPANY FOR EACH ROW EXECUTE PROCEDURE auditlogfunc();

----在company表中插入数据
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 );

----此时audit表中也插入了数据
qianbase=# select * from audit;
 emp_id |          entry_date
--------+-------------------------------
      1 | 2022-08-11 03:39:30.196963-04
(1 row)

参考文档：PostgreSQL官方手册

PostgreSQL的存储过程及基本使用_NMAZMMF的博客-CSDN博客_postgresql 存储过程