#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2000
    Location
    Zhitomir, Ukraine
    Posts
    2
    Rep Power
    0
    How can one write a function similar to mysql_insert_id for use with PostgreSQL?

    I need to move a site from MySQL to PostgreSQL. It uses a simple database interface library. I have modified most of the functions, but I have a problem with this one:
    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">code:</font><HR><pre>
    function SQLInsId() {
    return mysql_insert_id();
    }
    [/code]
    I'd like to create a context-independent function that would do exactly the same: return the last inserted AUTO_INCREMENT value (sequence in PostgreSQL). The problem is it is unknown what table the script works with when the function is called.

    I guess that someone must have already coped with this problem. Please tell me how to do this.
  2. #2
  3. .Net Developer
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2000
    Location
    London
    Posts
    987
    Rep Power
    15
    I think you can use pg_getlastoid() function in PostgreSQL to find out last insert id.

    ie, pg_getlastoid(result_id);





    ------------------
    SR -
    webshiju.com
    www.jobxyz.com-IT Career Portal
    ezipindia.com--WebStudio


    "The fear of the LORD is the beginning of knowledge..."
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2000
    Location
    Zhitomir, Ukraine
    Posts
    2
    Rep Power
    0
    Thanks a lot for your advice.

    But OID is not exactly what I need. I need to get the value of a "serial" field. It is used to link tables on insert.

    I have another idea about it. I think it's possible to use a single sequence for all serial fields in all tables (defining the default value "nextval(seq)" for these fields explicitly). Then it must be possible to get the last sequence value with a single "select":
    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">code:</font><HR><pre>
    CREATE SEQUENCE only_seq;

    CREATE TABLE table1
    (table1_id INT4 DEFAULT nextval('only_seq') PRIMARY KEY,
    table1_data INTEGER DEFAULT 0);

    CREATE TABLE table2
    (table2_id INT4 DEFAULT nextval('only_seq') PRIMARY KEY,
    table2_data INTEGER DEFAULT 0);

    -- ... here go some inserts or anything else ...

    -- Now, to get the latest sequence number:
    SELECT currval(only_seq);

    -- Looks quite easy!
    [/code]

    I haven't checked this code yet, but I think it should work. I'm not sure it is good to use a single sequence for all tables, but it seems to be better than using OID for linking or "selecting smth where OID=last_oid".

    If someone else is interested in this problem, please tell me what solution is better. Or, maybe, you can invent something completely different?


    [This message has been edited by valichek (edited August 15, 2000).]

IMN logo majestic logo threadwatch logo seochat tools logo