Saving results from RETURNING in separate variables

Question

Problem

Say I have the following insert statement:

insert into table_1 (...) values (...), (...), (...);

Assuming I know those 3 rows will be correctly inserted (no clause is violated), how can I fetch the id field of each inserted row into a separate variabile in order for me to use it in the rest of the .sql script?

What I tried

I've read about returning which would presumably translate into:

insert into table_1 (...) values (...), (...), (...) returning id;

I've also read about with (Common Table Expressions) which could "save" the result of returning into a table for later use in the following insert statements;

but it appears that I would then have to get the ids as select id from temp_table associating some kind of selector to distinguish between each 3 rows.

Question

Is there a way to get the ids of each inserted row to later use them, separately, in a different insert statement?

Maybe if you array_agg that returning ID and assign it to array variable, then sorting order will be the same as inserts and you will be able to just do my_arr[1], my_arr[2] to get ID of specific rows. — Łukasz Kamiński
– Łukasz Kamiński, Commented Nov 22, 2017 at 14:43
You are correct, but you can make your insert inside CTE and do SELECT array_agg(returned_id) INTO my_ids FROM ins_cte. From what little test I did it seems to preserve order. — Łukasz Kamiński
– Łukasz Kamiński, Commented Nov 23, 2017 at 10:03

Gordon Linoff · Accepted Answer · 2017-11-22 14:06:47Z

1

You could use three CTEs:

with i1 as (
      insert into table_1 (...) values (...)returning id
     ),
     i2 as (
      insert into table_1 (...) values (...) returning id
     ),
     i3 as (
      insert into table_1 (...) values (...) returning id
     )
insert . . .;

This doesn't put the values into three variables. It does put them in three separate CTEs so each can be referred to individually.

answered Nov 22, 2017 at 14:06

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

sh03 Over a year ago

And I have to reference the id as select id from i1 limit 1?

Gordon Linoff Over a year ago

@Shoe . . . That would be one approach.

Collectives™ on Stack Overflow

Saving results from RETURNING in separate variables

Problem

What I tried

Question

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Problem

What I tried

Question

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related