How to use the RETURNING clause to insert into two tables?

user_11033295 0
Viewed 1056

I don't understand how to use RETURNING, e.g. how to get the actual value "in my hands" for next steps. Assume, I have two tables:

create table "actors" (
  id_act serial not null primary key,
  first_name text not null,
  last_name text not null
);
create table movies (
  id_mov serial not null primary key,
  act_id integer not null
);

Now I add an actor:

INSERT INTO actors (first_name, last_name) VALUES ('Tom', 'Hanks');

and immediately after that, I'd like to use that new actor's-ID to insert a movie:

INSERT INTO movies(###);

How can I use RETURNING to use it instead of my placeholder ###?

sql-returning postgresql sql
2 Answers

You don't need PL/pgSQL to use RETURNING, this can be used in plain SQL as well.

But for the problem you have, you need neither, because you can use currval() from the sequence backing actors.id to get the last generated id:

INSERT INTO actors (first_name, last_name) VALUES ('Tom', 'Hanks');
INSERT INTO movies(act_id) values 
(currval(pg_get_serial_sequence('actors', 'act_id')));

If you really want to use RETURNING, you can use a data modifying CTE

with new_actor as (
  INSERT INTO actors (first_name, last_name) VALUES ('Tom', 'Hanks')
  returning id_act
)
insert into movies (act_id)
select id_Act
from new_actor;

Online example

user_3303150

You need to use the RETURNING clause in the INSERT statement to store the returned value in another variable.

Example:

create or replace procedure insert_rows()
language plpgsql
as
$$
declare
 v_id_act int;
begin
INSERT INTO actors (first_name, last_name) VALUES ('Tom', 'Hanks') RETURNING id_act INTO v_id_act;
INSERT INTO movies(act_id) values (v_id_act);
end;
$$;
CREATE PROCEDURE

call insert_rows();
CALL

select * from actors;
 id_act | first_name | last_name 
--------+------------+-----------
      1 | Tom        | Hanks
(1 row)

select * from movies;
 id_mov | act_id 
--------+--------
      1 |      1
(1 row)
user_107268500
Related