Using triggers in Postgres

Question

I have two tables A and B in Postgres DB.

Whenever I insert a new row or rows in table A I want to add those updates to table B. If table B is empty, all the news rows inserted in table A will be also added to table B. However, when new rows are inserted in table A and if there is already data in table B, I want to delete all those data from table B before performing insert of the new rows of table A into table B.

So basically, every time when new rows are inserted into table A, corresponding rows should also be added to table B but perform delete operation every time before inserting to table B so that there is no old rows in table B. Table B is intended to hold only newly added rows of table A.

Following is the Postgres script where I have used a trigger.

    CREATE TRIGGER test_one AFTER INSERT ON A
    FOR EACH ROW EXECUTE PROCEDURE addRows();

    CREATE OR REPLACE FUNCTION addRows() RETURNS TRIGGER AS $remove_table$
       BEGIN
          DELETE from B;
          INSERT INTO B(std_id, name) VALUES (new.col1, new.col2);
          RETURN NEW;
       END;
    $remove_table$ LANGUAGE plpgsql;

This trigger function does the job but not all the newly added rows in table A are preserved. I end up with only one row in table B that is the last row of newly inserted data in the table A. To reemphasize, if I insert 10 new rows into table A, table B is expected to have 10 rows added. However, only the 10th row is added into table B which is not the desired result.

Can anybody help me understand what's wrong?

Answer 1

Since Postgres 10, pseudo tables are supported through which the inserted set can be accessed in FOR EACH STATEMENT triggers.

Change your function to do an INSERT ... SELECT ... from such a pseudo table.

CREATE
 OR REPLACE FUNCTION addrows
                     ()
                     RETURNS TRIGGER
AS
$$
BEGIN
  DELETE FROM b;
  
  INSERT INTO b
              (std_id,
               name)
              SELECT col1,
                     col2
                     FROM inserted;
  RETURN NULL;
END;
$$
LANGUAGE plpgsql;

And when creating the trigger, make it a a FOR EACH STATEMENT trigger and add a reference for the pseudo table.

CREATE TRIGGER test_one
               AFTER INSERT ON a
               REFERENCING NEW TABLE AS inserted
               FOR EACH STATEMENT
               EXECUTE PROCEDURE addrows();

db<>fiddle

Answer 2

The problem with the trigger is that it fires for each row you insert, not each 'collection' of rows. With, for example, a batch of 10 inserts, the trigger will fire 10 times always removing all data in B, leaving you with 1 row - the last to be inserted.

Why create this additional table and duplicate data? Could you not store an insert date/time or an 'insert batch number' in table A and either query from this or create a view on it?