How can i use returning id in multiple requests (postgres)-CodePudding

For my task i need to add a main entry and then add some additional, using the id of the added record. Now i use request with "returning id":

with rows as (
insert into "Contact"(name, gender, city, birthdate)
    values
           ('Name', 1, 'City', '2000-02-03')
           returning id
            )
insert into "Education"(user_id, place, degree, endyear)
    select id , 'some_place', 'some_state', 1990 from rows

This way I can add one additional entry, but I need several. If I try to do the second insert query - postgre loses relation "rows"

with rows as (
insert into "Contact"(name, gender, city, birthdate)
    values
           ('Name', 1, 'City', '2000-02-03')
           returning id
            )
insert into "Education"(user_id, place, degree, endyear)
    select id , 'some_place', 'some_state', 1990 from rows
insert into "Status"(user_id, status)
    select id , 'val' from rows

ERROR:  relation "rows" does not exist
LINE 11:     select id , 'val' from rows
                                             ^
SQL state: 42P01
Character: 373

is there any way to fix this?

CodePudding user response：

I would suggest to put all statements in an anonymous code block. You could use the RETURNING into a variable and from there execute the following inserts, e.g.

DO $$
DECLARE returned_id int;
BEGIN
  INSERT INTO contact 
    (name, gender, city, birthdate) VALUES 
    ('Name', 1, 'City', '2000-02-03')
  RETURNING id INTO returned_id;
  
  INSERT INTO education 
    (user_id, place, degree, endyear) VALUES 
    (returned_id, 'some_place', 'some_state', '1990-01-01');
    
  INSERT INTO status 
    (user_id, status) VALUES 
    (returned_id, 'val');   
END;
$$;

Demo: db<>fiddle