For every inserted row, generate series of rows in another table with AFTER INSERT trigger

Question

Let's say that a customer places an order with certain quantity and I want to insert this information into table1 initially. 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

• user1822: You already asked that question: dba.stackexchange.com/questions/286548

Answer 1

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

• Manoj Selvam: Thanks mate . I tried in the same way as yours but i made few errors your code helped me to understand better
• Kazi Mohammad Ali Nur Romel: You are most welcome. Thanks for accepting the answer. Best wishes.

Answer 2

IN postgres you can use a CTE and generate_series to build you desrired result

CREATE TABLE table1("id" VARCHAR(3),"qty" int);
INSERT INTO table1 VALUES('A1', 25),('A2', 32)

CREATE TABLE table2 AS
WITH sub_isbuild AS (
 SELECT CONCAT("id",'_',generate_series(1, "qty")) as sub_id
 FROM table1
)
 SELECT sub_id FROM sub_isbuild;

57 rows affected

SELECT * FROM table2

| sub_id |
| :----- |
| A1_1 |
| A1_2 |
| A1_3 |
| A1_4 |
| A1_5 |
| A1_6 |
| A1_7 |
| A1_8 |
| A1_9 |
| A1_10 |
| A1_11 |
| A1_12 |
| A1_13 |
| A1_14 |
| A1_15 |
| A1_16 |
| A1_17 |
| A1_18 |
| A1_19 |
| A1_20 |
| A1_21 |
| A1_22 |
| A1_23 |
| A1_24 |
| A1_25 |
| A2_1 |
| A2_2 |
| A2_3 |
| A2_4 |
| A2_5 |
| A2_6 |
| A2_7 |
| A2_8 |
| A2_9 |
| A2_10 |
| A2_11 |
| A2_12 |
| A2_13 |
| A2_14 |
| A2_15 |
| A2_16 |
| A2_17 |
| A2_18 |
| A2_19 |
| A2_20 |
| A2_21 |
| A2_22 |
| A2_23 |
| A2_24 |
| A2_25 |
| A2_26 |
| A2_27 |
| A2_28 |
| A2_29 |
| A2_30 |
| A2_31 |
| A2_32 |

db<>fiddle here

• Manoj Selvam: Is this possible to achieve via stored procedure and triggers ?
• nbk: of course that is normal sql in postgres, you must refine it, if you don't want duplicates. and if you don_'t want to create a new table, you can use it in nsert INTO. this is only to show that it works and how in a simple manner

Answer 3

This is likely best handled in application code since it's a procedural goal you're trying to accomplish, not really a relational one, since the approach likely requires iteration.

That being said, you possibly can accomplish it in a relational way via Dynamic SQL if you had a numbers table you could select the TOP N number of rows from prepended with your id. The Dynamic SQL comes into play as a way to build the query with N = your qty field.

E.g. the query that your Dynamic SQL could generate would look like this (with id and qty filled in):

SELECT CONCAT('A1', '_', Number) AS sub_id
FROM NumbersTable
ORDER BY Number
LIMIT 25

• Erwin Brandstetter: TOP 25 is valid in SQL Server, but not in Postgres (or standard SQL). And we don't really need the crutch of a "numbers table" in Postgres, either.
• J.D.: @ErwinBrandstetter Thanks, mind fart, fixed my example query.
• Erwin Brandstetter: I am also puzzled as to how dynamic SQL (or even ecpg!) would come into play. The + operator does concatenation in MS products, but not in Postgres (or standard SQL). We do not need an explicit cast, certainly not to varchar(10) with an arbitrary character limit.
• J.D.: @ErwinBrandstetter Thanks, the + was leftover from my brain fart, I've updated my answer with the PostgreSQL equivalent. The character limit is not arbitrary, the range of an INT in PostgreSQL is -2147483648 to [phone]. I did forget about negative numbers though and have fixed my character limit to 11, even though assumptively in OP's case, he won't have negative quantities, but anything is possible I suppose. Clearly I'm not an expert on PostgreSQL :) so I don't know the exact Dynamic SQL implementation here, rather it's just an idea, but I believe it could be possible.
• J.D.: Also I re-updated my answer after re-reading that you said a cast isn't even needed. I decided to simplify it in that case with the CONCAT() function anyway. Thanks again for the feedback.

Answer 4

Consider two separate columns in "Table 2", like:

CREATE TABLE tbl1 (
 t1_id text PRIMARY KEY
, qty int NOT NULL CHECK (qty BETWEEN 1 AND 1000) -- CHECK to prevent abuse
);
CREATE TABLE tbl2 (
 t1_id text NOT NULL REFERENCES tbl1 -- optional FK
, sub_id int NOT NULL -- separate column !
, PRIMARY KEY(t1_id, sub_id) -- optional PK
);
);

Either way, a data-modifying CTE in combination with generate_series() does the trick:

WITH ins1 AS (
 INSERT INTO tbl1(t1_id, qty)
 VALUES ('A1', 3) -- input row once
 RETURNING * -- !
 )
INSERT INTO tbl2(t1_id, sub_id)
SELECT i.t1_id, sub_id
FROM ins1 i, generate_series(1, i.qty) sub_id;

fiddle

You can still concatenate if you insist (for your original table layout):

WITH ins1 AS (
 INSERT ...
 )
INSERT INTO tbl2(sub_id)
SELECT i.t1_id || sub_id
FROM ins1 i, generate_series(1, i.qty) sub_id;

But I wouldn't.

OR add a trigger

Trigger function:

CREATE OR REPLACE FUNCTION tbl1_insaft()
 RETURNS trigger
 LANGUAGE plpgsql AS
$func$
BEGIN
 INSERT INTO tbl2(t1_id, sub_id)
 SELECT NEW.t1_id, sub_id
 FROM generate_series(1, NEW.qty) sub_id;
 RETURN NULL; -- AFTER trigger can return NULL
END
$func$;

Trigger:

CREATE TRIGGER insaft
AFTER INSERT ON tbl1
FOR EACH ROW EXECUTE PROCEDURE tbl1_insaft();

Then just:

INSERT INTO tbl1(t1_id, qty) VALUES ('A2', 4);

fiddle

Related:

• Delete rows from a table depending upon rows deleted from another table