pqxx return id of just inserted row

Question

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.

Hatpin

Answer 1

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;

Sontag

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! – Hatpin