Postgres create universal function on all table

Question

I have a few db tables. I want write universtal postgres function on copy rows to history tables I have tables:

table1
table1_h

table2
table2_h

I wrote function (with help stackoverflow)

CREATE OR REPLACE FUNCTION copy_history_f() RETURNS TRIGGER AS
$BODY$
  DECLARE
    tablename_h text:= TG_TABLE_NAME || '_h';
  BEGIN
     EXECUTE 'INSERT INTO  ' || quote_ident(TG_TABLE_SCHEMA) || '.' || quote_ident(tablename_h) || ' VALUES (' || OLD.* ||')';
    RETURN NULL;
  END; 
$BODY$
LANGUAGE plpgsql VOLATILE;

And functions was create, but after update is error.

ERROR:  syntax error at or near ","
ROW 1: ...RT INTO  table1_h VALUES ((12,,,0,,"Anto...

I know where is error in this insert but I don't know how I repair that. Structure tables table1 and table1_h are identical but table1_h has one more column (id_h)

Can you help me, how I have create psql function?

Thnak you.

Abelisto · Accepted Answer · 2017-06-26 14:42:19Z

drop table if exists t;
drop table if exists t_h;
drop function if exists ftg();

create table t(i serial, x numeric);
insert into t(x) values(1.1),(2.2);

create table t_h(i int, x numeric);

create function ftg() returns trigger language plpgsql as $ftg$
declare
  tablename_h text:= TG_TABLE_NAME || '_h';
begin
  execute format($q$ insert into %I.%I select $1.*; $q$, TG_TABLE_SCHEMA, tablename_h) using old;
  return null;
end $ftg$;

create trigger tg_t after delete on t for each row execute procedure ftg();

delete from t where i = 1;
select * from t_h;

dbfiddle

Update It solves your problem, but I think that you want to have a bit more info in your history tables. It will be more complex a bit:

drop table if exists t;
drop table if exists t_h;
drop function if exists ftg();

create table t(i serial, x numeric);
insert into t(x) values(1.1),(2.2);

create table t_h(
  hi serial,      -- just ID
  hd timestamp,   -- timestamp
  hu text,        -- user who made changes
  ha text,        -- action
  i int, x numeric
);

create function ftg() returns trigger language plpgsql as $ftg$
declare
  tablename_h text:= TG_TABLE_NAME || '_h';
begin
  execute format(
    $q$
      insert into %I.%I
        select
          nextval(%L || '_hi_seq'),
          clock_timestamp(),
          current_user,
          %L,
          $1.*
    $q$, TG_TABLE_SCHEMA, tablename_h, tablename_h, TG_OP) using old;
  return null;
end $ftg$;

create trigger tg_t after delete or update on t for each row execute procedure ftg();

update t set x = x * 2;
update t set x = x * 2 where i = 2;
delete from t where i = 1;

select * from t_h;

dbfiddle

Gurmokh · Accepted Answer · 2017-06-26 11:59:37Z

1

I assume you are inserting the 'old' values from table1 into table1_h.

The additional column is your problem. When you using an insert without naming columns you must use a matching number and type for the insert.

You must use column referencing.

eg.

Insert into table1_h(column1, column2, column3) 
values (a,b,c)

Consider a default value for the additional column in table table1_h.

answered Jun 26, 2017 at 11:59

Gurmokh

2,09127 silver badges28 bronze badges

1 Comment

ondrusu Over a year ago

Yes, thank you for your answer. Abelisto writes universal insert to table_h. It's working.

Collectives™ on Stack Overflow

Postgres create universal function on all table

2 Answers 2

1 Comment

1 Comment

Your Answer

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

Related