pgsql触发器:当向一张表中插入或更新一条记录时，同时向另一张表也插入或更新一条记录
发布网友
发布时间：2022-04-08 09:26
共2个回答
懂视网
时间：2022-04-08 13:47
计算平均值
PostgreSQL计算平均值的sql类似如下：
with s as (select cast(load_15 as float) from asset_serverstatus where sid_id=10 order by id desc limit 10) select avg(load_15) from s
触发器函数
CREATE OR REPLACE FUNCTION fn_status_loadavg_insert() RETURNS trigger AS $BODY$ BEGIN update asset_serverstatus set load_avg=( with s as (select cast(load_15 as float) from asset_serverstatus where sid_id=new.sid_id order by id desc limit 10) select avg(load_15) from s ) where id=new.id; return new; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100;
用pgsql写了个触发器的函数，基本思路其实就是更新。
为表添加触发器
CREATE TRIGGER trg_status_loadavg_insert AFTER INSERT ON asset_serverstatus FOR EACH ROW EXECUTE PROCEDURE fn_status_loadavg_insert();
这样PostgreSQL数据库中根据某条件，取某字段的平均值，然后插入到另外一个字段的实现就完成了。
记录下。
热心网友
时间：2022-04-08 10:55
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "a_pkey" for table "a"
CREATE TABLE
digoal=# create table b (id int primary key, name text, time timestamp);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "b_pkey" for table "b"
CREATE TABLE
digoal=# create or replace function tg_a () returns trigger as $$
declare
begin
case TG_OP
when 'INSERT' then
insert into b(id,name,time) values (NEW.aid, NEW.aname, NEW.time);
when 'UPDATE' then
update b set id=NEW.aid, name=NEW.aname, time=NEW.time where id=OLD.aid;
when 'DELETE' then
delete from b where id=OLD.aid;
when 'TRUNCATE' then
truncate b;
else
return NULL;
end case;
return NULL;
end;
$$ language plpgsql;
digoal=# create trigger tg_a after INSERT OR DELETE OR UPDATE ON a for each row execute procere tg_a();
CREATE TRIGGER
digoal=# create trigger tg_a_truncate after truncate ON a for each statement execute procere tg_a();
CREATE TRIGGER
digoal=# insert into a select generate_series(1,10),'digoal',clock_timestamp();
INSERT 0 10
digoal=# select * from a;
aid | aname | time
-----+--------+----------------------------
1 | digoal | 2013-02-03 18:59:37.592479
2 | digoal | 2013-02-03 18:59:37.592667
3 | digoal | 2013-02-03 18:59:37.592674
4 | digoal | 2013-02-03 18:59:37.592677
5 | digoal | 2013-02-03 18:59:37.59268
6 | digoal | 2013-02-03 18:59:37.592683
7 | digoal | 2013-02-03 18:59:37.592686
8 | digoal | 2013-02-03 18:59:37.59269
9 | digoal | 2013-02-03 18:59:37.592693
10 | digoal | 2013-02-03 18:59:37.592696
(10 rows)
digoal=# select * from b;
id | name | time
----+--------+----------------------------
1 | digoal | 2013-02-03 18:59:37.592479
2 | digoal | 2013-02-03 18:59:37.592667
3 | digoal | 2013-02-03 18:59:37.592674
4 | digoal | 2013-02-03 18:59:37.592677
5 | digoal | 2013-02-03 18:59:37.59268
6 | digoal | 2013-02-03 18:59:37.592683
7 | digoal | 2013-02-03 18:59:37.592686
8 | digoal | 2013-02-03 18:59:37.59269
9 | digoal | 2013-02-03 18:59:37.592693
10 | digoal | 2013-02-03 18:59:37.592696
(10 rows)
digoal=# delete from a where aid=1;
DELETE 1
digoal=# select * from b;
id | name | time
----+--------+----------------------------
2 | digoal | 2013-02-03 18:59:37.592667
3 | digoal | 2013-02-03 18:59:37.592674
4 | digoal | 2013-02-03 18:59:37.592677
5 | digoal | 2013-02-03 18:59:37.59268
6 | digoal | 2013-02-03 18:59:37.592683
7 | digoal | 2013-02-03 18:59:37.592686
8 | digoal | 2013-02-03 18:59:37.59269
9 | digoal | 2013-02-03 18:59:37.592693
10 | digoal | 2013-02-03 18:59:37.592696
(9 rows)
digoal=# select * from a;
aid | aname | time
-----+--------+----------------------------
2 | digoal | 2013-02-03 18:59:37.592667
3 | digoal | 2013-02-03 18:59:37.592674
4 | digoal | 2013-02-03 18:59:37.592677
5 | digoal | 2013-02-03 18:59:37.59268
6 | digoal | 2013-02-03 18:59:37.592683
7 | digoal | 2013-02-03 18:59:37.592686
8 | digoal | 2013-02-03 18:59:37.59269
9 | digoal | 2013-02-03 18:59:37.592693
10 | digoal | 2013-02-03 18:59:37.592696
(9 rows)
digoal=# update a set aname='new' where aid=2;
UPDATE 1
digoal=# select * from a where aid=2;
aid | aname | time
-----+-------+----------------------------
2 | new | 2013-02-03 18:59:37.592667
(1 row)
digoal=# select * from b where id=2;
id | name | time
----+------+----------------------------
2 | new | 2013-02-03 18:59:37.592667
(1 row)
digoal=# truncate a;
TRUNCATE TABLE
digoal=# select * from a;
aid | aname | time
-----+-------+------
(0 rows)
digoal=# select * from b;
id | name | time
----+------+------
(0 rows)
参考资料：http://blog.163.com/digoal@126/
