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

D-Shih · Accepted Answer · 2022-01-30 14:27:33Z

4

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

edited Jan 30, 2022 at 14:27

answered Jan 30, 2022 at 2:37

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Ish Thomas Over a year ago

Thanks! It looks like it works (although, I had to put column names in to double quotes, which is annoying). I wish there was an easier way to do it, but if it works, it works.

D-Shih Over a year ago

@IshThomas Ok I see I had edited my answer hope that can help you.

Ish Thomas Over a year ago

With "do end" option, I'm getting this error: SQL Error [42601]: ERROR: syntax error at or near "begin" Position: 24 Where: invalid type name "integer

Ish Thomas Over a year ago

I've tried first insert without into, it worked. I tried insert into GameScores but without g_id and it worked as well. So, I don't know how to declare a variable, or into doesn't work.

D-Shih Over a year ago

I guess there is another issue with your query because it work on sqlfiddle dbfiddle.uk/…

|

Collectives™ on Stack Overflow

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

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related