Setting Postgresql variable from result of insert for future use

Question

I have a table with a UUID key that is generated on insert. I need to use that key in multiple future inserts and want to store it in a variable.

    CREATE TABLE table1 (UUID uuid PRIMARY KEY DEFAULT gen_random_uuid(), blah integer);
    CREATE TABLE table2 (UUID uuid PRIMARY KEY DEFAULT gen_random_uuid(), UUID table1Uuid);
    INSERT INTO TABLE1 (blah) values (1234);
    INSERT INTO TABLE1 (blah) values (6789);
.....
    INSERT INTO TABLE2 (table1Uuid theUuidMadeFromInsert1234);

I think I can make sure all the future inserts into TABLE2 are in the same session (maybe the same script) as those for TABLE1. Would like to do something like

uuid1234 = INSERT INTO TABLE1 (blah) values (1234);
uuid6789 = INSERT INTO TABLE1 (blah) values (6789);
.....
INSERT INTO TABLE2 (table1Uuid uuid1234);

but I can't get any syntax to work. I tried

create or replace function insertTable1 (junk integer)
    RETURNS UUID
    LANGUAGE plpgsql AS
$func$
declare 
myUuid UUID;
BEGIN
    insert into table1 (blah) values (junk) returning uuid into myUuid;
    return myUuid;
END
$func$;

then doing something like set my.var.uuid = select insertTable1(1234) and insert into table2 (table1Uuid my.var.uuid) with various uses of current_setting I have read many posts on SO, but can't find one that allows the value of the variable to outlast the function and be used

GMB · Accepted Answer · 2020-11-09 17:39:03Z

2

Is this what you want?

with t1 as (
    insert into table1 (blah) values (1234), (6789)
    returning *
)
insert into table2 (table1Uuid)
select uuid from t1 where blah = 1234;

The CTE inserts several rows in table1, then the outer query inserts in table2 the uuid that was generated for blah 1234.

Note that if you insert 1234 more than once in the CTE, the outer query will create as many rows in table2.

Alernatively, you can isolate the first insert in another CTE:

with 
    t1 as (insert into table1 (blah) values (1234) returning *),
    t2 as (insert into table1 (blah) values (456), (789))
insert into table2 (table1Uuid) select uuid from t1

edited Nov 9, 2020 at 17:39

answered Nov 9, 2020 at 17:23

GMB

224k25 gold badges103 silver badges151 bronze badges

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

2 Comments

Gary Over a year ago

I can't guarantee the blah value will be unique so I need the uuid specific to that particular insert (there are other columns I didn't put in the sample code).

Gary Over a year ago

Does this mean that all the inserts to table2 would have to be in 1 statement? I'll likely have about 20 inserts into table1 and then a hundred into table2. Seems like 1 statement with would become unweildy.

Collectives™ on Stack Overflow

Setting Postgresql variable from result of insert for future use

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related