SQL function, PLpgSQL and Trigge

image.png

基本结构：
SQL:
create or update function functionName(type of parameter1, type of parameter2,...) returns type
as language sql;
中间是sql语句,里面需要的参数由function提供
e.g.
create or update function hotelsIn(text) returns text
AS
$1这种老干部写法表示了第一个参数，这些参数显然是匿名的
PLpgsql:
create or update function functionName(parameter1, parameter2,...) returns type
as language plpgsql;
的功能更加强大, plpgsql的语法是：Declare Begin End;
e.g.

create or update function factorial(integer) returns numeric
AS

PLpgSQL的语法对于任何一个有语言基础的程序员来说都是简单的，但是似乎网上的blog并不是很多，official document 也不算特别人性化，正在复习的我也准备总结一下。
基础操作
赋值 a:=1
判断相等 a=1
操作数：Numeric, Boolean, String, Time Related
Numeric: Numeric(precision, scale), Real, Integer
String: char(n), varchar(n), text
Boolean: boolean, True, False
Time Related: Date, Time, TimeStamp, Interval
Special: Null
Null和任何操作数的如果发生实际运算，运算结果都为Null
e.g.
True and Null = Null
False and Null = False
True or Null = True
False or Null = False
(结合and or 的执行规则)
实际上就是SQL里面的那一套
操作符：<, >, <=, >=, <>, =， :=
String concatenate: str1 || str2
String Case: lower(str)
Extract subString: subString(str, start, count)
Arithmetic Operations: + - * / abs ceil floor power sqrt sin
Aggregations: count sum max min avg

2.选择分支
Switch Case
Case
WHEN condition1 THEN operation set1
WHEN condition2 THEN operation set2
......
ELSE operation setn
END;
不写else默认return Null，所有exception默认return Null
2.If
IF condition1 THEN operation set1
ELIF condition2 THEN operation set2
......
ELSE operation setn
END IF;
3.循环
1.While
While condition LOOP
statements
END LOOP;
2.FOR
For start_index..end_index LOOP
...
END LOOP;
e.g.
suppose count := 0
FOR 0..9 LOOP
count := count + 1;
END LOOP;
The result is: 10 (end_index included)
3.FOREACH
for item in select musician from Member where musicGroup = old.id LOOP END LOOP;

Trigger 以及与trigger联用的function
Trigger 在很多DBMS里都有，这里还是具体指postgresql的trigger.
Trigger 往往结合function一同起效，在数据库里解决了很多需要监听并且同步执行的问题。
基本结构：
CREATE TRIGGER triggerName before/after event [for each row] execute procedure functionName;
create function functionName() returns trigger AS
language plpgsql;
其中event包括 insert, delete, update
trigger作为返回值，实际返回的是new 或 old;
new 是 更改后的tuple， old是更改前的tuple.
所以显而易见的是： insert 无 old, delete 无new

before: 在event执行之前，如果更改了new的值，那么存入的值就会改变；如果new的值不合法，那么就会抛异常
after :after event执行之后，所有的constraints都已经check过了
after 的 返回数据无所谓
而对于before, return Null会 rollback 并且 abort已进行的操作

e.g.
CREATE TRIGGER cheskState BEFORE insert or update on Person for each row execute procedure checkState();

CREATE FUNCTION shckState() RETURNS trigger
AS $$
BEGIN
new.state = upper(trim(new.state));
if new.state !~ '[A-Z][A-Z]' then
raise Exception 'Code must be two alpha characters';
end if;

if(Null = select * from states where code = new.state;) then
raise exception 'Invalid Code %', new/state;
end if;
return new;
END;
language plpgsql;
一般都是return new，不然改变的event不是白操作了...

不是很经常描述这种一大段的知识点，如果有疑惑请提出来，我会修改。
具体的没提到的一些细节问题请查看官方文档，这篇文章希望能快速的让一些读者过一遍trigger和function.