Postgresql 函数、触发器写法

1、数据库环境

 

 

-- Table: 学生分数表

CREATE TABLE stu_score
(
  stuno serial NOT NULL,    --学生编号
  major character varying(16),   --专业课程
  score integer     --分数
)
WITH (
  OIDS=FALSE
);
ALTER TABLE stu_score OWNER TO postgres;




-- Table: 专业状态表，存储哪个专业有多少学生报名

CREATE TABLE major_stats
(
  major character varying(16),    --专业课程
  total_score integer,      --总分
  total_students integer   --学生总数
)
WITH (
  OIDS=FALSE
);
ALTER TABLE major_stats OWNER TO postgres;

 

2、函数、存储过程

 

 

create or replace function fun_stu_major()
returns trigger as 
	$BODY$
	DECLARE
	rec record;
	BEGIN
DELETE FROM major_stats;--将统计表里面的旧数据清空
FOR rec IN (SELECT major,sum(score) as total_score,count(*) as total_students 
FROM stu_score GROUP BY major) LOOP
INSERT INTO major_stats VALUES(rec.major,rec.total_score,rec.total_students);
END LOOP;
return NEW;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE

 

3、正式创建触发器trigger

 

 

create trigger tri_stu_major 
AFTER insert or update or delete
on stu_score 
for each row
execute procedure fun_stu_major()