0

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

vdegenne
  • 12,272
  • 14
  • 80
  • 106
  • 1
    Gordon's answer is probably what you need, but I suggest you also [Read this post.](http://stackoverflow.com/questions/2944297/postgresql-function-for-last-inserted-id) – Zohar Peled May 10 '15 at 16:24

2 Answers2

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;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 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. – vdegenne May 10 '15 at 17:30
  • This example works for any kind of returning value, not just a sequence value. – Frank Heikens May 10 '15 at 18:06
  • @FrankHeikens . . . In addition, it doesn't suffer from race conditions, that can invalidate the other answer. – Gordon Linoff May 10 '15 at 19:25
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;
percy
  • 988
  • 9
  • 9
  • 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 ? – vdegenne May 10 '15 at 16:40
  • It is absolutely safe - CURRVAL returns last returned value from any sequence for current session. – Pavel Stehule May 10 '15 at 16:59