Generate Series of Rows with AFTER INSERT Trigger in PostgreSQL

postgresqlstored-procedurestrigger

Let's say that a customer places an order with certain quantity and I want to insert this information into table1 initialy. At the same time, I want to generate a series of rows in the table2 with a combination of the newly generated ID in table1. Has to be executed for every new order. Like:

Table 1 
columns - id ,qty
          A1   25
          A2   32

Generates:

Table 2 
column - sub_id 
          A1_1
          A1_2
          .
          .
          A1_25
          A2_1
          A2_2
          .
          .
          A2_32

Best Answer

You can achieve your goal easily with after insert trigger. First you need to create a trigger function which will collect information of newly added rows from table1 and will generate and insert a series of IDs into table2.

CREATE OR REPLACE FUNCTION after_insert_table1()
  RETURNS trigger AS
$$
BEGIN
         insert into table2 SELECT CONCAT(new.id,'_',generate_series(1, new.qty)) as sub_id;
    RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';

Then you need to create the trigger on table1 which will call the trigger function.

CREATE TRIGGER InsertIntoTable2 AFTER INSERT 
ON table1
FOR EACH ROW
execute procedure after_insert_table1()

This trigger will insert five rows into table2 with unique sub_id for satement insert into table1 values ('A1',5);

select * from table1

id qty
A1 5

select * from table2

sub_id
A1_1
A1_2
A1_3
A1_4
A1_5
Related Question