Update each row of a table with the corresponding value

Question

I have two Postgres tables:

create table A(
   id_A  serial not null,
   column_A  varchar  null;
   ...);

create table B(
   id_B  serial  not null,
   id_A  int4  not null,
   name  varchar  null,
   keywords  varchar  null,
   ...);

An element of table A is associated to multiple elements of table B and an element of table B is associated to one element of table A.

The column keywords in table B is a concatenation of values of columns B.name and A.column_A:

B.keywords := B.name || A.column_A

How to update with a trigger the column B.keywords of each row in table B if the value of A.column_A is updated?

In other words, I want to do something like this (pseudo-code):

FOR EACH ROW current_row IN TABLE B
   UPDATE B SET keywords = (SELECT B.name || A.column_A
                            FROM B INNER JOIN A ON B.id_A = A.id_A
                            WHERE B.id_B = current_row.id_B)
   WHERE id_B = current_row.id_B;

Answer 1

Your trigger has to call a function when A is updated:

CREATE OR REPLACE FUNCTION update_b()
  RETURNS TRIGGER
AS $$
BEGIN
  UPDATE B
  SET keywords = name || NEW.column_A
  WHERE id_A = NEW.id_A;
  return NEW;
END
$$ LANGUAGE plpgsql;


CREATE TRIGGER update_b_trigger AFTER UPDATE OF column_A
ON A
FOR EACH ROW
EXECUTE PROCEDURE update_b();

It might also be useful to add a trigger BEFORE INSERT OR UPDATE on table B to set the keywords.