I have a situation where I need to insert into multiple tables and the last table will depend on the table before it with some extra list of items. I am using pg with node and javascript.
Table 1:
insert into col1, col2 values (1, 2) return col1 as table1_id
Table 2:
insert into col1 col2 values ( table1_id, val1),
(table1_id, val2),
.
.
.
.
(table1_id, val_n)
I have seen here some who have done that but with just 1 row in the last table but I will have a list of items as val1....valn... to be merged with the id generated from the first insert in table1 and added to a list that I have. This is going to be a relationship table with a list of items that belong to a record in table1.
Any clues would be appreciated..
Thanks
I think you should create a 'dao' layer in postgres. An Postgres function with all parameters and return what you should have in frontend.
And this postgres function insert all row what you want.
Like this you can manage your problem:
CREATE OR REPLACE FUNCTION function_what_i_want(val1 integer, val2 integer, my_argument integer[])
RETURNS my_schema_and_table[] AS
$BODY$
DECLARE
result my_schema_and_table[];
BEGIN
insert into table1(col1,col2,col3) VALUES (nextval('seq1'),val1, val2);
insert into table2(c1,c2)
select currval('seq1'),m.c2
from unnest(my_argument) m(c2);
END;
I don't know your project. If you doesn't want search on this tables, perhaps is better store this lists in json.