Get last insert id of Postgresql

Question

As per question:

PHP Postgres: Get Last Insert ID

they have stated that the best way to retrieve the last insert id is:

INSERT INTO ....
RETURNING id;

I want to create a wrapper class for Postgresql so that when i run, for example:

$id = $db_wrapper->insert("INSERT...");

that it will always return the last id inserted into variable $id. I do not know the auto_inc column name for queries that will be passed to the insert() function and I would prefer not to have to add "RETURNING id" to the end of all my insert queries - any ideas on how to achieve this?

Answer 1

INSERT with RETURNING

INSERT INTO employee (lastname,firstname) VALUES ('Jain', 'Manish') RETURNING id;

I Prefer this because of thousands of traffic at a time on server might get wrong last curval or LASTVAL(); Althogh it says this both functions are concurrent and work with individual session but i think the

RETURNING id

is better and secure.

Pros: This is very basic and realistic way to get last inserted id, no problem found till the direct entry of 1050 Entry at a time. ;) bit load testing.

Cons: Almost none, you might need to modify the way you call your INSERT statement (in the worst case, perhaps your API or DB layer does not expect an INSERT to return a value); it's not standard SQL (who cares);

Answer 2

PostgreSQL will (by default) create a sequence called 'user_id_seq'. for example if your table name is user. then it is user_id_seq

You can then do something like:

$strTable = "user";
$last_insert_id = $objPDO->lastInsertId("$strTable_id_seq");

See for multiuser-safe methods http://www.postgresql.org/docs/9.0/interactive/functions-sequence.html

See other ways mysql_insert_id alternative for postgresql

EDIT: as per comment of Eli

//if your table name in model is **user**
$strTable = "user";
$model = new User(); 

// do your stuff
$model->save();

$last_insert_id = $model->lastInsertId("$strTable_id_seq");

OR

If your model is called Model, and has a property called id (aka, the PK of the table), then you can acces this way:

//...
$model = new Model();
// do your stuff....
$model->save();

$the_id = $model->id;