Duplicate INSERT to another PostgreSQL table

Question

I have checked similar threats but not match exactly that I am trying to do.

I have two identical tables:

t_data: we will have the data of the last two months. There will be a job for delete data older than two months.
t_data_historical: we will have all data.

I want to do: If an INSERT is done into t_data, I want to "clone" the same query to t_data_historical.

I have seen that can be done with triggers but there is something that I don't understand: in the trigger definition I need to know the columns and values and I don't want to care about the INSERT query (I trust of the client that to do the query), I only clone it.

trigger functions are exactly for that scenario - why not make use of them? — iLuvLogix
– iLuvLogix, Commented Feb 5, 2019 at 10:27
thanks for your quick answer. Yes, I guess that the solution are the triggers but I thought that I had to know the columns/values for insert. After read the Laurenz's answer seem that It is now needed (sorry but I am very new in postgresql). — CoDeC__
– CoDeC__, Commented Feb 5, 2019 at 10:44

Laurenz Albe · Accepted Answer · 2019-02-05 10:36:00Z

1

If you know that the history table will have the same definition, and the name is always constructed that way, you can

EXECUTE format('INSERT INTO %I.%I VALUES (($1).*)',
               TG_TABLE_SCHEMA,
               TG_TABLE_NAME || '_historical')
USING NEW;

in your trigger function.

That will work for all tables, and you don't need to know the actual columns.

answered Feb 5, 2019 at 10:36

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

4 Comments

CoDeC__ Over a year ago

Thank you so much. I will deploy and let you know the results.

CoDeC__ Over a year ago

Hi, I got the error: InternalError: control reached end of trigger procedure without RETURN. I have changed the USING command by RETURN and then I got an other error.

CoDeC__ Over a year ago

I created the trigger like:

CREATE OR REPLACE FUNCTION copy2historical() RETURNS trigger AS $BODY$ BEGIN  EXECUTE format('INSERT INTO %I.%I VALUES (($1).*)', TG_TABLE_SCHEMA, TG_TABLE_NAME || '_historical') USING NEW;  END; RETURN NULL; $BODY$   LANGUAGE plpgsql VOLATILE   COST 100;     CREATE TRIGGER copy2historical_tg AFTER INSERT ON t_data FOR EACH ROW EXECUTE PROCEDURE copy2historical();

Laurenz Albe Over a year ago

It should be RETURN NEW;, and it must come before the END;.

CoDeC__ · Accepted Answer · 2019-02-05 12:17:29Z

0

Laurenz, I created the trigger like:

    CREATE OR REPLACE FUNCTION copy2historical() RETURNS trigger AS
$BODY$
BEGIN

EXECUTE format('INSERT INTO %I.%I VALUES (($1).*)', TG_TABLE_SCHEMA, TG_TABLE_NAME || '_historical')
USING NEW;

END;
RETURN NULL;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;


CREATE TRIGGER copy2historical_tg
AFTER INSERT
ON t_data
FOR EACH ROW
EXECUTE PROCEDURE copy2historical();

thanks for your help.

answered Feb 5, 2019 at 12:17

CoDeC__

1134 silver badges10 bronze badges

Collectives™ on Stack Overflow

Duplicate INSERT to another PostgreSQL table

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related