Postgresql function and trigger to copy content from one column to another?-CodePudding

I need to implement a function and a trigger to copy the contents of COLUMN A to COLUMN B( last one completely NULL) both in the same table. This function must be executed by the trigger whenever a new record is entered. I have tried to do the following in the function but it does not seem to work

UPDATE table 
SET column1 = column2;

RETURN column1;

On the other hand in the trigger I have:

CREATE TRIGGER name
AFTER UPDATE OR INSERT ON table
FOR EACH ROW EXECUTE PROCEDURE function();

Does anyone see the error?

CodePudding user response：

You need a before update or insert trigger for that.

create or replace function the_trigger_f() returns trigger language plpgsql as
$$
begin
 new.column_b := new.column_a;
 return new;
end;
$$;

CREATE TRIGGER trigger_name
BEFORE UPDATE OR INSERT ON the_table
FOR EACH ROW EXECUTE PROCEDURE the_trigger_f();

CodePudding user response：

maybe you need generated column? Documentation

create table test_table (column1 text, column2 text  
       GENERATED ALWAYS AS (column1) stored);

and results

insert into test_table(column1) values('Megatest'),('Ubertest');

column1  | column2
---------- ----------
 Megatest | Megatest
 Ubertest | Ubertest

update

update test_table set column1='11111111111111' where column1='Megatest';

select * from test_table;
    column1     |    column2
---------------- ----------------
 Ubertest       | Ubertest
 11111111111111 | 11111111111111