I have a problem with the "pg_getlastoid()" function in php. I will
illustrate it with a example:

Create a table with psql:

create table test_serial ( id serial, name varchar );

Insert a record into the tabel with php:

$db = pg_pconnect ("host=10.0.0.2 port=5432 dbname=sockaart
user=postgres password=postgres");
if (!$db)
{
fatal_error("no connection.");
}

$result = pg_Exec ($db, "insert into test_serial (naam) values
'twan')");
if (!$result)
{
fatal_error("no insert: ".pg_errormessage ($db));
}

print pg_getlastoid($result)."<br>";

The line "print pg_getlastoid($result)."<br>";" should print the
number of the last inserted id, but it doesn't.

It prints the following number 33589 which increases every time when
you reload the page with 1.

What is going on here?

Best regards,
- Twan

    You're confusing postgresql's get last oid with mysql and other databases get last inserted id.

    An OID is an object id that is a unique identifier withing a database across all tables. I.e. no two records in a database in any of it's sub tables will have the same OID.

    You can see OIDs by doing a select like so:

    select *,oid from tablename;

    And you'll see the OIDs for each row. These are not the same as serial datatypes, i.e. the ones made when you do a:

    create table tablename (field1 text, id serial);

    OIDs are maintained by the dbms engine not the user. Note that if you create records in two tables, back and forth, the OIDs assigned will go up by one for each table alternately like so:

    table 1 oid:---table 2 oid:
    3452-----------3453
    3454-----------3455
    3456-----------3457

    and so on. But the same OID will never show up anywhere else in your database.

    So, to get your last inserted ID you have to "roll your own" last getlastinsertid function like so:

    function pg_getlastid($conn,$result,$table){
    $OID = pg_getlastoid($result);
    $idres = pg_exec($conn,"select id from $table where OID=$OID");
    $id = pg_result($idres,0,"id");
    return $id;
    }

    There may be some problems with variables not bein global enough here, I haven't tested this code at all, it's just from the hip.

      Thanks for the helpfull reply, i indeed thought that the SERIAL ID was returned by the function "pg_getlastoid()", but that was a little wrong 🙂 Now everything is working correctly!

        Write a Reply...