问题描述
我有100多个具有相同模式的表,并且有一个触发函数,只要将数据插入到该表中就更新一些列。
I have 100s of tables having the same schema and I have a trigger function to UPDATE some column whenever data is INSERTed into that table.
表模式:
CREATE TABLE symbol_daily_ohlc (
cdate date,
open numeric(8,2),
high numeric(8,2),
low numeric(8,2),
close numeric(8,2),
sma8 numeric(8,2)
);
触发函数:
create or replace function update_sma8() RETURNS TRIGGER AS
$$
BEGIN
UPDATE symbol_daily_ohlc d SET sma8 = s.simple_mov_avg
FROM
(
SELECT sec.cdate,AVG(sec.close)
OVER(ORDER BY sec.cdate ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) AS
simple_mov_avg FROM symbol_daily_ohlc sec
)s where s.cdate = NEW.cdate --The newly inserted cdate
AND d.cdate = s.cdate;
RETURN NULL;
END $$ language plpgsql;
桌上的触发器设置:
CREATE TRIGGER trig_update_sma
AFTER INSERT ON symbol_daily_ohlc
FOR EACH ROW
EXECUTE PROCEDURE update_sma8();
这对于给定的表,即symbol_daily_ohlc,效果很好。我想使用相同的触发函数,即update_sma8()与具有相同模式的任何表一起使用(我不想为不同的表重写相同的函数)。
This is working well for the given table i.e symbol_daily_ohlc. I would like to use the same trigger function i.e update_sma8() to be used with any table having the same schema (I don't want to rewrite the same function for different tables).
我尝试用TG_TABLE_NAME替换表名(即symbol_daily_ohlc),但这不起作用-引发错误。那么该怎么做呢?
I tried replacing the table name (i.e symbol_daily_ohlc) with TG_TABLE_NAME, but that didn't work - thrown errors. So how to do that?
参考:
推荐答案
您可以使用相同的过程来执行并返回所有表的触发器,但是不能为所有表使用相同的触发器。
You can have a same procedure that executes and returns a Trigger for all the tables, but you can't have a same Trigger for all your tables.
这是一个块,动态创建带有表名后缀的触发器(使用 EXECUTE格式
)
Here's a block that dynamically creates a Trigger with table name suffix ( using EXECUTE format
)
DO $$
declare
tabs RECORD;
BEGIN
for tabs IN
(select table_name,table_schema
from information_schema.tables where table_name
like 'symbol_daily_ohlc%'
-- and table_schema like '%'
) LOOP
EXECUTE format('CREATE TRIGGER check_update_%I
AFTER INSERT ON %I.%I
FOR EACH ROW
EXECUTE PROCEDURE update_sma8()',tabs.table_name,
tabs.table_schema
,tabs.table_name);
END LOOP;
END $$;
这是您的触发器,它从 TG_TABLE_NAME动态获取表名
And here's your Trigger, which takes the table name dynamically from TG_TABLE_NAME
create or replace function update_sma8() RETURNS TRIGGER AS
$$
BEGIN
EXECUTE format ('UPDATE %I d SET sma8 = s.simple_mov_avg
FROM
(
SELECT sec.cdate,AVG(sec.close)
OVER(ORDER BY sec.cdate ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) AS
simple_mov_avg FROM %I sec
)s where s.cdate = %L --The newly inserted cdate
AND d.cdate = s.cdate',TG_TABLE_NAME,TG_TABLE_NAME,NEW.cdate);
RETURN NULL;
END $$ language plpgsql;
正如其他人所建议的,拥有多个带有相同的结构。您应该考虑将它们合并到一个表中。
As others have suggested, it's not a good idea to have multiple tables with the same structure. You should consider combining them into one table.
这篇关于PostgreSQL的相同触发器函数可以在INSERT上更新到不同的表中(使用相同的模式)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!