Can I use return value of INSERT RETURNING in another INSERT

Question

Is something like this possible   INSERT INTO Table2  val  VALUES   INSERT INTO Table1  name  VALUES   a title   RETURNING id      like using the return value as value to insert a row in a second table with a reference to the first table

User · Answer

You can use the lastval   function      Return value most recently obtained with nextval for any sequence   So something like this   INSERT INTO Table1  name  VALUES   a title    INSERT INTO Table2  val   VALUES  lastval       This will work fine as long as no one calls nextval   on any other sequence  in the current session  between your INSERTs   As Denis noted below and I warned about above  using lastval   can get you into trouble if another sequence is accessed using nextval   between your INSERTs  This could happen if there was an INSERT trigger on Table1 that manually called nextval   on a sequence or  more likely  did an INSERT on a table with a SERIAL or BIGSERIAL primary key  If you want to be really paranoid  a good thing  they really are you to get you after all   then you could use currval   but you d need to know the name of the relevant sequence   INSERT INTO Table1  name  VALUES   a title    INSERT INTO Table2  val   VALUES  currval  Table1 id seq   regclass      The automatically generated sequence is usually named t c seq where t is the table name and c is the column name but you can always find out by going into psql and saying     gt   d table name    and then looking at the default value for the column in question  for example   id   integer   not null default nextval  people id seq   regclass    FYI  lastval   is  more or less  the PostgreSQL version of MySQL s LAST INSERT ID  I only mention this because a lot of people are more familiar with MySQL than PostgreSQL so linking lastval   to something familiar might clarify things

User · Answer

DO    DECLARE tableId integer  BEGIN   INSERT INTO Table1  name  VALUES   a title   RETURNING id INTO tableId    INSERT INTO Table2  val  VALUES  tableId   END       Tested with psql  10 3  server 9 6 8

User · Answer

The best practice for this situation  Use RETURNING     INTO  INSERT INTO teams VALUES       RETURNING id INTO last id   Note this is for PLPGSQL

User · Answer

table ex id  default nextval  table id seq   regclass   camp1 varchar camp2 varchar  INSERT INTO table ex camp1 camp2  VALUES   xxx   123   RETURNING id

User · Answer

In line with the answer given by Denis de Bernardy    If you want id to be returned afterwards as well and want to insert more things into Table2   with rows as   INSERT INTO Table1  name  VALUES   a title   RETURNING id   INSERT INTO Table2  val  val2  val3  SELECT id   val2value    val3value  FROM rows RETURNING val

User · Answer

You can do so starting with Postgres 9 1   with rows as   INSERT INTO Table1  name  VALUES   a title   RETURNING id   INSERT INTO Table2  val  SELECT id FROM rows   In the meanwhile  if you re only interested in the id  you can do so with a trigger   create function t1 ins into t2     returns trigger as    begin   insert into table2  val  values  new id     return new  end     language plpgsql   create trigger t1 ins into t2   after insert on table1 for each row execute procedure t1 ins into t2

[postgresql] Can I use return value of INSERT...RETURNING in another INSERT?

Examples related to postgresql

Examples related to sql-returning