Results 1 to 6 of 6
  1. #1
    Join Date
    May 2003
    Posts
    1

    Angry Unanswered: Last Insert ID???

    I'm writing a few DAOs in PHP with multiple database support for a script I will be releasing soon and I noticed that PostgreSQL doesn't have an easy method for fetching the last insertion ID. Although I have an idea as a work around ( not sure if PostgreSQL would support something like this ):

    Code:
    function get_insert_id ($table, $id)
    {
    
    	$result =  pg_fetch_array ($this->query ("SELECT * FROM {$table} ORDER BY {$id} DESC LIMIT 1"));
    
    	return $result[$id]; 
    
    }
    I think something like this would work, but the problem is that you have to specify the name of the table and the name of the id field ( if there is any ).

    I have created a DB super class in PHP to set a standard API and so far I have MySQL support. I MySQL all I have to use is the following function:

    Code:
        function get_insert_id () 
        { 
    
            return mysql_insert_id ($this->DBlink); 
    
        }
    You don't need to specify anything with the above. I was wondering if there is a simple way to achieve what I want while still keeping a standard.

    Thx!

  2. #2
    Join Date
    Jan 2003
    Location
    Midwest
    Posts
    138
    you could just do...
    SELECT MAX(id) AS 'maxId' FROM tableName

  3. #3
    Join Date
    May 2002
    Location
    Florida, USA
    Posts
    49
    Spend some more time understanding PostgreSQL sequences and how to manipulate them. There should be no reason to perform an extra aggregate query on the whole table as Memnoch1207 suggests. In fact, using the aggregate query method is dangerous, because a high volume of inserts could give wrong results here.

    And, the first method suggested in PHP above is really inefficient.

    Here is the simple way I like to do things for INSERT operations:

    1. start a transaction
    2. SELECT nextval(sequence_name); (this advances the sequence by one value, and returns that value to you)
    3. INSERT, using the value from step #2 explicitly, rather than just allowing the DEFAULT to handle it
    4. commit transaction

    Now, you have performed a single atomit operation, ending up with the value of your last inserted id. In PHP, it can look like $id = pg_fetch_result(pg_query("SELECT nextval('name_seq')"),0,0); Then you just use the $id value in your insert, and later for whatever else you need.

    This way, there is no chance of a high volume of INSERTS corrupting your results. In fact, you don't even need a transaction if you do things this way, because so what if someone selects another value in the sequence while you are doing your insert. You might get sequence value 4576 and before you are done with your insert, the next value 4577 has already been advanced in the sequence. It might mean that row with id 4577 is inserted before 4576, but the integrity of the table will still be preserved.

    Another thing I would like to point out:

    Often developers want this insert id because they are performaning a group of related queries or inserts. Look into the idea of putting groups of queries into PostgreSQL stored procedures. This can reduce the need for these problems in the first place.

  4. #4
    Join Date
    Jan 2003
    Location
    Midwest
    Posts
    138
    excellent point rycamor...I forgot about the SELECT nextval() which actually holds the value of the sequence...so another use can insert using that number.

  5. #5
    Join Date
    Jun 2003
    Location
    Tampa Bay, Florida, USA
    Posts
    2
    I love examples, myself, here's one:

    Suppose you have table "person", like this:

    CREATE TABLE person
    (
    person_id SERIAL NOT NULL PRIMARY KEY,
    first_name varchar(50),
    last_name varchar(50),
    age int4,
    email varchar(50)
    )


    Assuming you have plpgsql installed as a language for postgresql, here's an example of a stored procedure for a simple insert that returns the ID of the inserted row:


    CREATE OR REPLACE FUNCTION person_add
    (
    varchar(50),
    varchar(50),
    int,
    varchar(50)
    ) RETURNS int AS
    '
    BEGIN
    --
    -- Insert the new row, including the Primary Key (auto-generated)
    --
    INSERT INTO person
    SELECT nextval( ''person_person_id_seq'' ), $1, $2, $3, $4;
    --
    -- Now select back the new ID to the caller
    --
    RETURN currval( ''person_person_id_seq'' );
    END;
    '
    LANGUAGE 'plpgsql'




    Hope this helps!

  6. #6
    Join Date
    Jun 2003
    Location
    Tampa Bay, Florida, USA
    Posts
    2

    Talking

    Oops, forgot to tell you, here's how you call it in PostGreSQL v7.3.x:

    select * from person_add( 'Joe', 'User', 32, 'joe.user@domain.org' )

    This works from the psql command-line utility and even returns a colum for easy retrival in PHP.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •