8

i am using c++ 4.8 ( available 4.9) and pqxx driver ver. 4.0.1. postgresdb is latest stable.

My problem is all about complexity and resource balance:

I need to execute insert to database (and there is optionally pqxx::result) and id in that table id is based on nextval(table_seq_id)

Is is possible to get id of inserted row as a result? There is a workaround on this to ask db about currentvalue in sequence and just insert query with currentvalue+1 (or +n) but this will require to do "insert and ask" chain.

Db should be able to store more than 6K large requests /per.sec. so i would like to ask about id as infrequent as possible. Bulk insert is not an option.

esavier
  • 423
  • 4
  • 13

1 Answers1

8

As documented here, you can add a RETURNING clause to the INSERT query, to return values from the inserted row(s). They give an example similar to what you want, returning an ID:

INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets')
   RETURNING did;
Mike Seymour
  • 249,747
  • 28
  • 448
  • 644
  • first of all, thank you for quick response! This may be actually what i am looking for. I will put together some tests to check how pqxx will respond with that construct ( mainly complexity issues ). Again, thank you very much! – esavier Oct 23 '14 at 20:25