How to get last ID after TSQLQuery.Append() in PostgreSQL

[vladvons]

Create simple table with auto increment 'id' field using 'SERIAL' keyword for PostgeSQL

CREATE TABLE users (
    id    SERIAL PRIMARY KEY,
    name  TEXT
);

Code: [Select]

// this ID not exists. Just open SQLQuery (Dont know easiest way)
SQLQuery.SQL.Text := 'SELECT * FROM users WHERE id = -1';
SQLQuery.Active := false;
SQLQuery.Active := true;

SQLQuery.Append();
SQLQuery.FieldByName('Name').AsString := 'John';

// save changes
if (SQLQuery.Transaction.Active) then
begin
  SQLQuery.ApplyUpdates();
  TSQLTransaction(SQLQuery.Transaction).Commit();
end;

// Now i want to know lasdt ID
SELECT currval(pg_get_serial_sequence('users', 'id'))
ERROR:  currval of sequence "users_id_seq" is not yet defined in this session

SELECT last_value FROM users_id_seq;
OK

// i dont want to use 'RETURNING'
INSERT INTO users (name) VALUES ('John') RETURNING id;


is  there possibility to get last ID with native FPC functions?

perhaps my SQLQuery.Append() conception is wrong, so please give me an advice with example

[taazz]

how about executing <SELECT currval(pg_get_serial_sequence('users', 'id'))> before  calling TSQLTransaction.commit? Be warned I do not know if it will work just a thought.

[ludob]

how about executing <SELECT currval(pg_get_serial_sequence('users', 'id'))> before  calling TSQLTransaction.commit? Be warned I do not know if it will work just a thought.

It won't work if there are multiple simultaneous transactions. Sequences in PG are not part of a transaction.

SQLQuery.Append() is just an in memory append. Nothing is done to the DB before ApplyUpdates. So anything before that will not help in getting the last ID.

A possible solution is to call  SELECT nextval(users_id_seq) and use the resulting value to set your id with:

Code: [Select]

SQLQuery.Append();
SQLQuery.FieldByName('ID').AsInteger := NewID;
SQLQuery.FieldByName('Name').AsString := 'John';


[vladvons]

thanks ludob, works!