在过程触发器内部动态插入记录 [英] Insert record dynamically inside of Procedural Trigger
问题描述
我们希望将我们的数据库转换为我没有经验的Postgres(9.3.5),并且我正在尝试启动并运行我们的审计表。我知道每个表都需要自己的触发器,但是所有触发器都可以调用一个函数。
We are looking to convert our database over to Postgres (9.3.5), which I have no experience with, and I am trying to get our audit tables up and running. I understand that each table will need its own trigger, but all triggers can call a single function.
表上的触发器正在传递需要
The trigger on the table is passing a list of the columns that need to be audited since some of our columns are not tracked.
以下是我关注的一些帖子:
-https://stackoverflow.com/a/7915100/229897
- http://www.postgresql.org/docs/9.3/static/plpgsql-statements.html
- http://www.postgresql.org/docs /9.4/static/plpgsql-trigger.html
Here are some of the posts I followed:
- https://stackoverflow.com/a/7915100/229897
- http://www.postgresql.org/docs/9.3/static/plpgsql-statements.html
- http://www.postgresql.org/docs/9.4/static/plpgsql-trigger.html
当我运行此程序时,我收到错误消息:ERROR: $ 1或附近的语法错误
When I run this I get the error: ERROR: syntax error at or near "$1"
DROP TABLE IF EXISTS people;
DROP TABLE IF EXISTS a_people;
CREATE TABLE IF NOT EXISTS people (
record_id SERIAL PRIMARY KEY NOT NULL,
first_name VARCHAR NOT NULL,
last_name VARCHAR NOT NULL,
last_updated_on TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS a_people (
record_id SERIAL PRIMARY KEY NOT NULL,
a_record_id INT,
first_name VARCHAR NULL,
last_name VARCHAR NULL,
last_updated_on TIMESTAMP
);
/******************************************************/
--the function
CREATE OR REPLACE FUNCTION audit_func()
RETURNS TRIGGER AS
$BODY$
DECLARE
audit TEXT := TG_TABLE_SCHEMA || '.a_' || TG_TABLE_NAME;
cols TEXT := TG_ARGV[0];
BEGIN
EXECUTE format('INSERT INTO %1$s(a_%2$s) SELECT %2$s FROM ($1)', audit, cols) USING OLD;
NEW.last_updated_on = CURRENT_TIMESTAMP;
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;
/******************************************************/
--the trigger calling the function to update inbound records
CREATE TRIGGER build_user_full_name_trg
BEFORE UPDATE
ON people
FOR EACH ROW WHEN (OLD.* IS DISTINCT FROM NEW.*)
EXECUTE PROCEDURE audit_func('record_id,first_name,last_name');
/******************************************************/
INSERT INTO people (first_name, last_name) VALUES ('George','Lincoln');
UPDATE people SET last_name = 'Washington' WHERE first_name = 'George';
SELECT * FROM people;
我欢迎您的帮助(和耐心等待)!
I welcome your assistance (and patience)!
推荐答案
此子选择应该起作用:
EXECUTE format('INSERT INTO %1$s(a_%2$s) SELECT %2$s FROM (select ($1).*) XX', audit, cols) USING OLD;
这篇关于在过程触发器内部动态插入记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!