PostgreSQL的相同触发器函数可以在INSERT上更新到不同的表中（使用相同的模式） [英] PostgreSQL same trigger function to UPDATE on INSERT into different tables (using the same schema)

问题描述

我有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?

参考： SQL触发函数，用于在插入时更新每日移动平均值

推荐答案

您可以使用相同的过程来执行并返回所有表的触发器，但是不能为所有表使用相同的触发器。

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上更新到不同的表中（使用相同的模式）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！