How to assign returning value from Postgresql insert to a variable?

Question

I'm trying to insert one record into Postgresql, get inserted id, and use it in following 4 other inserts. I can't get it to work. This is what I have so far:

do $$
declare
    gameId integer
begin
    INSERT INTO public."Games" ("SeasonId", "StartTime", "GameStatusId", "AwayTeamId", "HomeTeamId", "IsVenueNeutral", "ModifiedOn", "Comment") 
    values(15, '2021-05-20 22:30:00.000', 1, 11, 12, false, '2022-01-29 20:20:00.000', 'Test')
    returning "GameId" into gameId ;

    INSERT INTO public."GameScores" ("GameId", "Period", "AwayScore", "HomeScore")
    VALUES(gameId , 1, 10, 10),
    VALUES(gameId , 2, 10, 10),
    VALUES(gameId , 3, 10, 10),
    VALUES(gameId , 4, 10, 10);
end $$;

I'm not even sure if I need to wrap it in do and end. All I want to is to generate a script to insert a bunch of data in one go. In some cases I need to reuse id of previously inserted record.

Thanks for help

Answer 1

I would use to use another variable g_id instead of gameId because there is another GameId column from your table, then the insert into may be like this.

do $$
declare
    g_id integer;
begin
    INSERT INTO public."Games" ("SeasonId", "StartTime", "GameStatusId", "AwayTeamId", "HomeTeamId", "IsVenueNeutral", "ModifiedOn", "Comment") 
    values(15, '2021-05-20 22:30:00.000', 1, 11, 12, false, '2022-01-29 20:20:00.000', 'Test')
    returning "GameId" into g_id ;

    INSERT INTO public."GameScores" ("GameId", "Period", "AwayScore", "HomeScore")
    VALUES(g_id , 1, 10, 10),
    (g_id , 2, 10, 10),
    (g_id , 3, 10, 10),
    (g_id , 4, 10, 10);
end $$;

sqlfiddle

Another way can try to use CTE to do that, then get the GameId from cte then do another insert into

WITH rows AS (
    INSERT INTO public."Games" ("SeasonId", "StartTime", "GameStatusId", "AwayTeamId", "HomeTeamId", "IsVenueNeutral", "ModifiedOn", "Comment") 
    values(15, '2021-05-20 22:30:00.000', 1, 11, 12, false, '2022-01-29 20:20:00.000', 'Test')
    returning "GameId"
)
INSERT INTO public."GameScores" ("GameId", "Period", "AwayScore", "HomeScore")
SELECT GameId,Period, AwayScore,HomeScore
FROM rows r CROSS JOIN 
LATERAL (VALUES
(1, 10, 10),
(2, 10, 10),
(3, 10, 10),
(4, 10, 10)
) s(Period, AwayScore,HomeScore);

sqlfiddle