postgresql - PL/pgSQL:如何使用 IF NEW。<> 旧的。
问题描述
我对 PL/pgSQL 编程很陌生。我需要审核记录表中更新的列
桌子
create table sample_table(name varchar(15),city varchar(15),age int,mail varchar(20) primary key);
审计表
create table sample_table__audits_dynamicols(mail varchar(20), columnchanged varchar(10), oldvalue varchar(10), changed_on timestamp(6) NOT NULL)
触发功能
CREATE FUNCTION public.log_sample_table_allchanges() RETURNS trigger AS $BODY$DECLARE
_colname text;
_tablename varchar(15) := 'sample_table';
_schema varchar(15) := 'public';
_changed_on time := now();
BEGIN
FOR _colname IN SELECT column_name FROM information_schema.Columns WHERE table_schema = _schema AND table_name = _tablename LOOP
IF NEW._colname <> OLD._colname THEN
INSERT INTO sample_table__audits_dynamicols(mail,columnchanged, oldvalue ,changed_on)
VALUES(OLD.mail,_colname,OLD.:_colname,_changed_on);
END IF;
END LOOP;
RETURN NEW;
END$BODY$
LANGUAGE plpgsql VOLATILE NOT LEAKPROOF;
扳机
create TRIGGER log_sample_table_allchanges
BEFORE UPDATE
ON SAMPLE_TABLE
FOR EACH ROW
EXECUTE PROCEDURE log_sample_table_allchanges();
要求:每当更改列值时,我都想将其记录为
(邮件、列名、列值、日期)
例如:
insert into sample_table (name, mail, city, age) values('kanta','mk@foo.com','hyd',23);
insert into sample_table (name, mail, city, age) values('kmk','mk@gmail.com','hyd',23);
所以当我更新如下
update sample_table set age=24 where mail='mk@foo.com';
update sample_table set city='bza' where mail='mk@gmail.com'
我希望审计表记录如下
(mk@foo.com,age,23, timestamp)
(mk@gmail.com, city, hyd, timestamp)
现在我在触发器函数中面临列比较的问题。请帮我纠正我的触发功能以满足我的要求。
解决方案
您可以使用EXECUTE
动态获取列的值并进行比较。
CREATE OR REPLACE FUNCTION public.log_sample_table_allchanges() RETURNS trigger AS
$BODY$
DECLARE
_colname text;
_tablename varchar(15) := 'sample_table';
_schema varchar(15) := 'public';
_changed_on timestamp := now();
_old_val text;
_new_val text;
BEGIN
FOR _colname IN SELECT column_name FROM information_schema.Columns WHERE table_schema = _schema AND table_name = _tablename
LOOP
EXECUTE 'SELECT $1.' || _colname || ', $2.' || _colname
USING OLD,NEW
INTO _old_val, _new_val; --get the old and new values for the column.
IF _new_val <> _old_val THEN
INSERT INTO sample_table__audits_dynamicols(mail,columnchanged, oldvalue ,changed_on)
VALUES(OLD.mail,_colname,_old_val,_changed_on);
END IF;
END LOOP;
RETURN NEW;
END$BODY$
LANGUAGE plpgsql VOLATILE NOT LEAKPROOF;
我不确定您为什么在审核表中定义mail
为 a ,如果同一邮件被更新两次PRIMARY KEY
,则会导致违规。unique constraint
推荐阅读
- bash - 将除第一行和最后一行之外的所有内容读入bash中的字符串
- java - 如何转换列表
到地图 > 使用 java8? - sh - “mpirun”调用的 Bash 脚本调用另一个 MPI 可执行文件并崩溃,退出代码为 13
- javascript - 如何将文本放在 d3 图表中的链接中心
- sql - 按类别、存储和属性分组,并根据属性名称的字母顺序分配顺序
- azure-application-insights - 为 WinForms 应用程序选择 App Center 或 Application Insights
- firebase - 按文档id查询集合
- iolanguage - REPL 与脚本中的克隆行为不同
- python - 使用 python 2.7.12 在 Ubuntu 16.04 服务器上进行视频流式传输期间发生错误
- javascript - 显示来自本地 JSON 文件的数据