How to get an INSERT's returned value in a SQL function and use it in that same function?

Question

here's an example to complete the title, this is the inner of my function (i am using postgresql but i guess this is pure SQL language)

$$
  INSERT INTO foo VALUES (DEFAULT, $1) RETURNING id;
  INSERT INTO link_foo_to_bar VALUES (1, <?>);
$$ language SQL;

two things to notice here, I RETURNING the id of the first insert, how to catch it as to use the returned id as the second argument of the second insert marked as < ? > ?

Gordon's answer is probably what you need, but I suggest you also Read this post. — Zohar Peled
– Zohar Peled, Commented May 10, 2015 at 16:24

Gordon Linoff · Accepted Answer · 2015-05-10 16:15:53Z

5

You can use a CTE to capture the rows inserted from the first insert:

with firstinsert as (
      INSERT INTO foo VALUES (DEFAULT, $1) RETURNING id
     )
INSERT INTO link_foo_to_bar 
    select 1, id
    from firstinsert;

answered May 10, 2015 at 16:15

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

3 Comments

vdegenne Over a year ago

I valid the other answer as it is more neat and logic as the id i'm using is a sequence. but thanks for your answer I didn't know this way of writing constants.

Frank Heikens Over a year ago

This example works for any kind of returning value, not just a sequence value.

Gordon Linoff Over a year ago

@FrankHeikens . . . In addition, it doesn't suffer from race conditions, that can invalidate the other answer.

percy · Accepted Answer · 2015-05-10 16:30:33Z

1

If the first column is a serial/primary key, I would use CURRVAL function:

$$
  INSERT INTO foo VALUES (DEFAULT, $1);
  INSERT INTO link_foo_to_bar VALUES (1, CURRVAL('foo_id_seq'));
$$ language SQL;

answered May 10, 2015 at 16:30

percy

1,12811 silver badges9 bronze badges

2 Comments

vdegenne Over a year ago

neat. i may ask a novice question here but are the INSERT statements synchronous ? what if the first insert has a big load of data to insert, is the CURRVAL going to be updated properly as the process switch on the second INSERT ?

Pavel Stehule Over a year ago

It is absolutely safe - CURRVAL returns last returned value from any sequence for current session.

Collectives™ on Stack Overflow

How to get an INSERT's returned value in a SQL function and use it in that same function?

2 Answers 2

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related