Update all column in a table using a record

Question

I have two identical tables: table1 and table2. I need to replicate all updates from one table to another using an after trigger. But I don't want to list all columns names in the update statement(except the PK). Is it possible to do something like this?

CREATE FUNCTION replicate_changes()
RETURNS trigger
LANGUAGE 'plpgsql' AS
$BODY$
BEGIN

  UPDATE table2
  SET (*) = NEW.*;
  WHERE table2.id = NEW.id;
 


  RETURN NULL;

END;
$BODY$;

CREATE TRIGGER trigger_replicate_changes
    AFTER UPDATE
    ON table1
    FOR EACH ROW
    EXECUTE FUNCTION replicate_changes();

Answer 1

You cannot do this directly, but you can delete the existing row and insert/select the new row. (see demo).

create or replace function replicate_changes()
returns trigger
language 'plpgsql' as
$body$
begin
  delete from table2 where id = old.id; 
  insert into table2 
         select * from table1
          where id = new.id;  
  return null;
end;
$body$;

But be careful, this is a dangerous process. What happens when you insert or delete from table1. As it stand those actions will not be reflected in table2. And what happens when DML is issued directly against table2, or only 1 of the table definitions gets updated (ddl) bu not the other.