Insert multiple rows across 3 tables in 1 query using the returned identifier

Question

I record sports data in three tables:

• matches (id, start_time)
• match_teams (id, match_id, team_id, score)
• match_players (id, match_id, team_id, player_id)

Each match consists of several teams, each team consists of several players (actually a list of lists). With team_id and player_id as foreign keys in the table of teams and players, respectively.

Using the structure above, I need to first insert into the match table and use the returned identifier to go to match_teams and match_players.

Following this question , I use the following CTE to accomplish this when I insert one match:

WITH a AS (INSERT INTO matches (start_time) 
VALUES ('"0001-01-01T00:00:00+00:00"') 
RETURNING id), 
b AS (INSERT INTO match_teams (match_id, team_id, score) 
VALUES 
((SELECT id FROM a), 5, 1), 
((SELECT id FROM a), 6, 2)) 
INSERT INTO match_players (match_id, team_id, player_id) 
VALUES 
((SELECT id FROM a), 5, 3), 
((SELECT id FROM a), 5, 4),
((SELECT id FROM a), 6, 5)
((SELECT id FROM a), 6, 6); 

. , /.

WITH a AS (INSERT INTO matches (start_time) 
VALUES 
('"0001-01-01T00:00:00+00:00"'),     -- 1st match
('"0001-01-01T00:00:00+00:00"')      -- 2nd match
RETURNING id), 
b AS (INSERT INTO match_teams (match_id, team_id, score) 
VALUES 
((SELECT id FROM a OFFSET 0 LIMIT 1), 5, 1),     -- 1st match
((SELECT id FROM a OFFSET 0 LIMIT 1), 6, 2),     -- 1st match
((SELECT id FROM a OFFSET 1 LIMIT 1), 5, 2),     -- 2nd match
((SELECT id FROM a OFFSET 1 LIMIT 1), 6, 1))     -- 2nd match
INSERT INTO match_players (match_id, team_id, player_id) 
VALUES 
((SELECT id FROM a OFFSET 0 LIMIT 1), 5, 3),     -- 1st match
((SELECT id FROM a OFFSET 0 LIMIT 1), 6, 4),     -- 1st match
((SELECT id FROM a OFFSET 1 LIMIT 1), 5, 5),     -- 2nd match
((SELECT id FROM a OFFSET 1 LIMIT 1), 6, 6);     -- 2nd match

, . ?

, . , , . " ?"

Answer 1

, ?

. , .

row_number() id teams players teams:

with teams (rn, team_ids, scores) as (
    values 
        (1, array[5, 6], array[1, 2]),  -- match #1 in this query
        (2, array[5, 6], array[2, 1])   -- match #2 in this query
    ),
players (rn, team_ids, player_ids) as (
    values 
        (1, array[5, 5, 6, 6], array[3, 4, 5, 6]),
        (2, array[5, 5, 6, 6], array[3, 4, 5, 6])
    ),
ins_matches as (
    insert into matches (start_time) 
    values 
        ('"0001-01-01t00:00:00+00:00"'),
        ('"0001-01-01t00:00:00+00:00"')
    returning id
    ),
matches as (
    select id, row_number() over (order by id) rn
    from ins_matches        -- rn - number of match in this query
    ),
ins_teams as (
    insert into match_teams (match_id, team_id, score) 
    select id, unnest(team_ids), unnest(scores)
    from matches
    join teams using(rn)
    ) 
insert into match_players (match_id, team_id, player_id) 
select id, unnest(team_ids), unnest(player_ids)
from matches
join players using(rn);

Answer 2

route_sources routes

• route_sources route_source_id PK
• routes route_source_id FK

.

-- GOT ID TO ROUTE_SOURCES FROM SEQUENCE
int_route_source_id = nextval('traffic.route_sources_route_source_id_seq'::regclass);

-- CREATE NEW RECORD FOR ROUTE_SOURCES
INSERT INTO traffic.Route_Sources 
    (route_source_id, sql, ini_avl_id, ini_link_id)
VALUES
    (int_route_source_id, strSQL, A.avl_id, A.link_id, ini_offset); 

-- CREATE THE ROUTE
INSERT INTO traffic.Routes 
    (route_source_id, seq_id, node_id, link_id, cost)               
SELECT int_route_source_id, seq, id1 AS node, id2 AS edge, cost::numeric(11,4)
    FROM pgr_trsp;