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

Question

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 ###?

score 2 · Answer 1 · 2020-05-23T19:05:14.590

2

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

edited May 23 '20 at 19:05

answered May 23 '20 at 18:07

Thank you, but ... I Issued five times INSERT INTO actors (first_name, last_name) VALUES ('Tom', 'Hanks'); id_act now is = 5 After that I issued INSERT INTO movies(act_id) values (lastval()); and assumed to see 5 as act_id, but it is always the highest ID of Movies, not the latest from Actors – StOMicha May 23 '20 at 18:34
the with .. version produces an SQL-Error: returning id; at or near ";" – StOMicha May 23 '20 at 18:37
@StOMicha: I had a wrong `;` inside the CTE. And you are right `lastval()` doesn't work because `movies` has a serial column as well. see my edit – May 23 '20 at 18:59
Thanks a lot, the new with... looks good is there any way, to get that ID into a variable for other usages? – StOMicha May 23 '20 at 19:29

score 1 · Answer 2 · answered May 23 '20 at 17:56

1

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)

answered May 23 '20 at 17:56

pifor

7,419
2
8
16

Thank you, but ... I issued the above CREATES, declare v_id_act int; INSERT INTO actors (first_name, last_name) VALUES ('Tom', 'Hanks') RETURNING id_act INTO v_id_act; and this results in a SQL-Error [42601]: ERROR: syntax error at or near "INTO" – StOMicha May 23 '20 at 18:14
I have displayed the output of statements execution with `psql`: you only need to run the `create or replace ... $$;`(11 lines) in psql *in one go*. I don't understand how you can have a syntax error. Which tool are you using to run the statements ? – pifor May 24 '20 at 06:46
I used dBeaver. I don't want the procedure; here [link] (https://www.postgresqltutorial.com/postgresql-insert/) I found a description, please refer to the last chapter "Get the last insert id": All I want now is to get that value "8" in my hands for further storage and usage. – StOMicha May 24 '20 at 07:20
What is your host programming language ? Maybe it's possible to retrieve using an host variable but I am not sure. If this is what you really want to do, ask a new question detailing this specific need and add the host language as question tag. – pifor May 24 '20 at 07:24

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

2 Answers2