Store and Reuse Value Returned by Insert ... Returning

Store and reuse value returned by INSERT ... RETURNING

... that can be used to insert values into other tables?

You can even do that in a single SQL statement using a data-modifying CTE:

WITH ins1 AS (
   INSERT INTO tbl1(txt)
   VALUES ('foo')
   RETURNING tbl1_id
   )
INSERT INTO tbl2(tbl1_id)
SELECT * FROM ins1

Requires PostgreSQL 9.1 or later.

db<>fiddle here (Postgres 13)

_{Old sqlfiddle (Postgres 9.6)}

Reply to question update

You can also insert into multiple tables in a single query:

WITH ins1 AS (
   INSERT INTO tbl1(txt)
   VALUES ('bar')
   RETURNING tbl1_id
   )
 , ins2 AS (
   INSERT INTO tbl2(tbl1_id)
   SELECT tbl1_id FROM ins1
   )
INSERT INTO tbl3(tbl1_id)
SELECT tbl1_id FROM ins1;

How to reuse value of insert statment

best way for your matter is

DECLARE @worker_idtbl TABLE (worker_id INT);
INSERT INTO dbo.worker OUTPUT Inserted.worker_id INTO @worker_idtbl(worker_id) VALUES ('my_name');
INSERT INTO dbo.job VALUES(GetDate(), NULL, 8, (select top 1 worker_id from @worker_idtbl) );

Returning identity of INSERT for new INSERT with data outside of RETURNING clause

Your example fails because you're not providing the value for product_id, which is required.

You can directly specify one like this:

WITH inserted_customer AS (
    INSERT INTO customers (first_name, last_name) VALUES ('acacaca', 'Doe') RETURNING id
)
INSERT INTO sales (customer_id, product_id) 
SELECT inserted_customer.id, 2 FROM inserted_customer;

If you want to get the product ID from an existing product you can do it with a subquery (or a CTE if you're doing more complicated stuff).

WITH inserted_customer AS (
    INSERT INTO customers (first_name, last_name) VALUES ('acacaca', 'Doe') RETURNING id
)
INSERT INTO sales (customer_id, product_id) 
SELECT inserted_customer.id, (SELECT id FROM products ORDER BY id DESC LIMIT 1) FROM inserted_customer;

If you want to insert the customer and the product on the fly, you can do two CTEs:

WITH inserted_customer AS (
    INSERT INTO customers (first_name, last_name) VALUES ('acacaca', 'Doe') RETURNING id
),
inserted_product AS (
    INSERT INTO products (product) VALUES ('my product') RETURNING id
)   
INSERT INTO sales (customer_id, product_id) 
SELECT inserted_customer.id, inserted_product.id
FROM inserted_customer, inserted_product;

Hope this helps!

Store the value of output inserted._ID to local variable to reuse it in another query

IDENTITY COLUMN

If it is an identity column and you are only inserting a single row then you can use SCOPE_IDENTITY() function to get the last generated Identity value within the scope of the current session.

DECLARE @NewValue INT;
insert into V_Post 
values('Please return the id of THIS row :)')
SET @NewValue = SCOPE_IDENTITY()

IF NOT IDENTITY Column Or Multiple Identity values

If it is an identity column and you are inserting multiple rows and want to return all the newly inserted Identity values, or it is not an identity column but you need the last inserted value then you can make sure of OUTPUT command to get the newly inserted values.

DECLARE @tbl TABLE (Col1 DataType)
DECLARE @NewValue Datatype;

insert into V_Post 
output inserted._ID INTO @tbl
values('Please return the id of THIS row :)')

SELECT @NewValue = Col1 FROM @tbl

SQL Server - Return value after INSERT

No need for a separate SELECT...

INSERT INTO table (name)
OUTPUT Inserted.ID
VALUES('bob');

This works for non-IDENTITY columns (such as GUIDs) too

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

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

SQL: Reuse value returned by function?

Since the function body is procedural, use the plpgsql language as opposed to SQL:

CREATE FUNCTION messages_add(bigint, bigint, text) RETURNS void AS $$
BEGIN
    INSERT INTO chats
        SELECT pair($1, $2), $1, $2
        WHERE NOT EXISTS (SELECT 1 FROM chats WHERE id = pair($1, $2));
    INSERT INTO messages VALUES (pair($1, $2), $1, $3);
END
$$ LANGUAGE plpgsql;

Also, if the result to reuse is pair($1,$2) you may store it into a variable:

CREATE FUNCTION messages_add(bigint, bigint, text) RETURNS void AS $$
DECLARE
    pair bigint := pair($1, $2);
BEGIN
    INSERT INTO chats
        SELECT pair, $1, $2
        WHERE NOT EXISTS (SELECT 1 FROM chats WHERE id = pair);
    INSERT INTO messages VALUES (pair, $1, $3);
END
$$ LANGUAGE plpgsql;

Is it possible to reuse a SELECT expression into a RETURNING expression?

You can do:

with i as (
      INSERT INTO table_name
          VALUES ...
          RETURNING *
     )
SELECT a, b, fn(c), fn2(a, d), d
FROM table_name;

That is, you don't have to use the results from the CTE in the SELECT. This seems really weird. Why not just execute two separate statements?

Or, perhaps you intend this:

with i as (
      INSERT INTO table_name
          VALUES ...
          RETURNING *
     )
SELECT a, b, fn(c), fn2(a, d), d
FROM i;

Is using the RETURNING clause from an UPDATE as the query clause for an INSERT's query clause possible?

Right now, no.

There was a feature that almost made it into PostgreSQL 9.0 known as Writeable CTE's that does what you're thinking (although the syntax is different).

Currently, you could either do this via a trigger or as two separate statements.

---

Related Topics