PostgreSQL same trigger function to UPDATE on INSERT into different tables (using the same schema)

Question

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.

Table Schema:

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)
);

Trigger function:

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;

Trigger setup on table:

CREATE TRIGGER trig_update_sma
AFTER INSERT ON symbol_daily_ohlc
FOR EACH ROW
EXECUTE PROCEDURE 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).

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?

Reference: SQL trigger function to UPDATE daily moving average upon INSERT

You shouldn't have tables with the same schema. If you are thinking of such a thing, you probably have an issue with your data model. All that data should go into a single table with a column that defines whatever other attribute you want the data to have. — Gordon Linoff
– Gordon Linoff, Commented Jan 12, 2019 at 12:57
The database is to store historical data of stock market i.e of different symbols (stocks). I think this is better approach to study the particular stock. — selvask
– selvask, Commented Jan 12, 2019 at 13:01
And yet, having a column called "stock_symbol" would have meant this issue wouldn't have been. You can still have a million "tables" if you want.. CREATE VIEW msft_history AS SELECT * FROM all_stock_history WHERE stock_symbol = 'MSFT';CREATE VIEW goog_history AS SELECT * FROM all_stock_history WHERE stock_symbol = 'GOOG' — Caius Jard
– Caius Jard, Commented Jan 12, 2019 at 13:56
Instead of an UPDATE assign the values you want to the respective columns of the new pseudo record. Then you won't have a table name in the trigger body and can use it for every table that has the columns you address (with compatible types of course). — sticky bit
– sticky bit, Commented Jan 12, 2019 at 14:07
@GordonLinoff I accepted your suggestion and I have changed my database accordingly. Thanks to you and Caius Jard. — selvask
– selvask, Commented Jan 13, 2019 at 9:24

Kaushik Nayak · Accepted Answer · 2019-01-12 15:32:17Z

1

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.

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 $$;

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;

Demo

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.

answered Jan 12, 2019 at 15:32

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

selvask Over a year ago

That's so nice of you to answer. Thank you. I would evaluate others suggestion too.

Caius Jard · Accepted Answer · 2019-01-12 14:12:42Z

0

I think you'll probably have to use SQL to generate SQL, then run the generated SQL. Something like:

select CONCAT('create or replace function update_sma8() RETURNS TRIGGER AS
  $$
  BEGIN
  UPDATE ', table_name, '  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 ', table_name, '
  )s where s.cdate = NEW.cdate  --The newly inserted cdate
  AND d.cdate = s.cdate;   
  RETURN NULL;
  END $$ language plpgsql;
') from information_schema.tables

But really you should consider Gordon's advice (and my followup) and put all your data back in one table:

SELECT CONCAT('INSERT INTO all_hist SELECT ''', table_name, ''', t.* FROM ', table_name) FROM information_schema.tables

This will generate a bunch of sqls that put all the table data into all_hist, a table that should have an identical schema to the other thousands, except for an additional column "symbol" or whatever..

You can use a similar trick to create yourself a bunch of views to recreate the thousands-table approach if you really want..

answered Jan 12, 2019 at 14:12

Caius Jard

75k6 gold badges61 silver badges95 bronze badges

1 Comment

selvask Over a year ago

Thank you for answering. I would evaluate your suggestion.

Collectives™ on Stack Overflow

PostgreSQL same trigger function to UPDATE on INSERT into different tables (using the same schema)

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related