PostgreSQL 函数
在线手册：中文 英文
PHP手册

pg_last_oid

(PHP 4 >= 4.2.0, PHP 5)

pg_last_oid — 返回上一个对象的 oid

说明

int pg_last_oid ( resource $result )

pg_last_oid() 在上一条通过 pg_query() 发送的命令是 SQL INSERT 的情况下用来取得分配给所插入记录的 oid。如果存在有效的 oid 则返回一个正整数，如果出错或者上一条通过 pg_query() 发送的命令不是 INSERT 或者该 INSERT 失败则返回 FALSE。

从 PostgreSQL 7.2 版开始 OID 字段成为可选项。如果一个表中没有定义 OID 字段，程序员必须用 pg_result_status() 函数来检查记录是否被成功插入。

Note:

本函数以前的名字为 pg_getlastoid()。

参见 pg_query() 和 pg_result_status()。

PostgreSQL 函数
在线手册：中文 英文
PHP手册
PHP手册 - N: 返回上一个对象的 oid

用户评论:

Volodymyr Volynets (27-Apr-2011 05:14)

Hi,

Version 9 supports the optional RETURNING clause causes INSERT to compute and return value(s) based on each row actually inserted. This is primarily useful for obtaining values that were supplied by defaults, such as a serial sequence number. However, any expression using the table's columns is allowed. The syntax of the RETURNING list is identical to that of the output list of SELECT.

If the UPDATE command contains a RETURNING clause, the result will be similar to that of a SELECT statement containing the columns and values defined in the RETURNING list, computed over the row(s) updated by the command.

Basically you can fetch all updated or inserted rows from the resource like if you executed SELECT statement.

jcvpalma at gmail dot com (16-Apr-2009 09:29)

Hi,

I solved this problem make a function that returns my last inserted id:

<?php
function pg_last_inserted_id($con, $table){
       
        #make the initial query
        $sql = "SELECT * FROM " . $table;
        #execute
        $ret = pg_query($con, $sql);
        #get the field name
        $campoId = pg_field_name($ret, 0);
       
        #change the query, using currval()
        $sql = "SELECT currval('".$table."_".$campoId."_seq')";
       
        #exec
        $retorno =pg_query($con, $sql);
       
        if(pg_num_rows($ret)>0){
            #array
            $s_dados = pg_fetch_all($retorno);
           
            #vars
            extract($s_dados[0],EXTR_OVERWRITE);
           
            return $currval;
           
        } else {
            #case error, returns false
            return false;
        }
?>

fun =)

malyszg at o2 dot pl (09-May-2008 10:59)

//function which return last row ID. It works like mysqli_insert_id() function
function pg_insert_id($pg,$query){// $pg - string connection, $query - sql command
$regExp = preg_match_all("/nextval\('([a-zA-Z0-9_]+)'\)/",$query,$array);
$sequence = $array[1][0];
$select = "SELECT currval('$sequence')";
$load = pg_query($pg,$select);
$id = pg_fetch_array($load,null,PGSQL_NUM);
return $id[0];
}

$connect = pg_connect("host dbname user password");
$insert = "Insert into klienci Values(nextval('autonumerowanie'),'Krzysztof','Nowak')";
$wykonaj = pg_query($connect,$insert);
$lastID = pg_insert_id($connect,$insert);
/*call pg_insert_id function with two arguments.
First argument is a string connection. Second argument is a SQL statement */
echo $lastID;

kevin at stormtide dot ca (06-Jun-2006 11:10)

I am afraid that the editor is misleading people here.

QUOTE "Editor's Note: If another record is inserted after the nextval is obtained and before you [sic: execute] the INSERT query this code will fail.  This should not be done on busy sites."

This is not correct. A sequence is a multi-session safe table-like structure in postgresql.

From the postgresql manual for nextval()

"Advance the sequence object to its next value and return that value. This is done atomically: even if multiple sessions execute nextval concurrently, each will safely receive a distinct sequence value. "

Since the default for a serial column during an insert is to call nextval(), it will also get a unique identifier.

Sequences are _not_ defined as being linearly ordered however and may contain holes and return values out of order (due to cache settings). They will, however, be unique 100% of the time.

The following code is CORRECT and thread safe WITHOUT a transaction on even the most loaded server.

QUOTE (with corrections) "

$res=pg_query("SELECT nextval('foo_key_seq') as key");
$row=pg_fetch_array($res, 0);
$key=$row['key'];
// now we have the serial value in $key, let's do the insert
pg_query("INSERT INTO foo (key, foo) VALUES ($key, 'blah blah')");

"

In this case the value retrieved for $key will never again be retrieved by the database under any circumstance. Period. Ever.

Hope that clears this up.

php at antimatters dot oc dot uk (31-May-2006 05:24)

Remember:  Although OID is somewhat unique (as others have mentioned), the OID isn't entirely unique.  Once it reaches the extent of the datatype it will go back to 0 and start again.

I suggest either using transactions or have another set of identifiers which you can use together to identify the inserted row... such as user_id and timestamp (which you've manually created, not using NOW)... If it's for something human based, a user would not be able to realistically click two submit buttons in the same second... if you do get that happening, then maybe you could assume it was spam.

Just a thought

Jonathan Bond-Caron (30-May-2005 04:21)

I'm sharing an elegant solution I found on the web (Vadim Passynkov):

CREATE RULE get_pkey_on_insert AS ON INSERT TO Customers DO SELECT currval('customers_customers_id_seq') AS id;

Every time you insert to the Customers table, postgreSQL will return a table with the id you just inserted. No need to worry about concurrency, the ressource is locked when the rule gets executed.

Note that in cases of multiple inserts:
INSERT INTO C1 ( ... ) ( SELECT * FROM C2);

we would return the id of the last inserted row.

For more info about PostgreSQL rules:
http://www.postgresql.org/docs/7.4/interactive/sql-createrule.html

paul at paulmcgarry dot com (16-Dec-2004 05:20)

I do not understand the editors notes in this section.
They seem to suggest that you can't safely use nextval to get an identifier from a sequence that will be unique accross all users/sessions. That is categorically false.

nextval will pull a number from a sequence and that number is guarunteed to be distinct accross all sessions.

To quote the Postgres Docs:
"nextval

Advance the sequence object to its next value and return that value. This is done atomically: even if multiple sessions execute nextval concurrently, each will safely receive a distinct sequence value."

wes at softweyr dot com (28-Oct-2004 07:02)

There seems to be some confusion about the PostgreSQL currval() function in these notes.  currval() isn't a general-purpose access to sequences, it has a very specific function.  From the PostgreSQL User's Guide (v 7.3.2) section 6.11:

currval - Return the value most recently obtained by nextval in the current session.  (An error is reported if nextval has never been called for this sequence in this session.)  Notice that because this is returning a session-local value, it gives a predictable answer even if other sessions are executing nextval meanwhile.

So wrapping a nextval (or other sequence insert) followed by currval in a transaction is not necessary.

php at developersdesk dot com (29-May-2004 01:46)

If you want to get the value of a sequence out of pgsql, similar to mysql_insert_id(), try this:

<?

pg_query( $connection, "BEGIN TRANSACTION" );
pg_query( $connection, $your_insert_query );
$result = pg_query( $connection, "SELECT CURRVAL('$seq_name') AS seq" );
$data = pg_fetch_assoc( $result );
pg_free_result( $result );
pg_query( $connection, "COMMIT TRANSACTION" );
echo "Insert sequence value: ", $data[ 'seq' ], "<BR>\n";

?>

Note the transaction that groups the INSERT query and the query that obtains the sequence value.  If you are already in a transaction, don't start another.  Also be aware that CURRVAL only works after an INSERT query, and always returns the value for the last INSERT for your connection, even if someone else has done an INSERT after you.

See:
http://www.postgresql.org/docs/7.3/interactive/functions-sequence.html

talk_biz at yahoo dot com (19-Apr-2004 11:28)

The sequence functions for autonumbering  can be read like an ordinary table.
ex. "select * from request_id_seq";
which returns all the current values for the sequence.  To get the last id number entered use
"select last_value from request_id_seq";
To have the query pull the values of the record with the last id, use a query with a subselect statement.
ex. "SELECT id, date_requested, time_requested, requestor_name, requestor_email, requestor_phone
FROM request where id = (select last_value from request_id_seq)";

luke at prgmr dot com (12-Apr-2004 09:04)

responding to a previous  editor's note-

No, the user is correct. when you do a 'select nextval' from a sequence, that increments the sequence.  If someone else does a 'select nextval' or inserts into the table with a 'null' value for the field defaulted to a nextval, they will get the next value-  thus if another record is inserted after the nextval and before the insert, that next record will still get a different value from the sequence.

Am I wrong?

[Editor's Note: If another record is inserted after the nextval is obtained and before you the INSERT query this code will fail.  This should not be done on busy sites.]

Especially now with an optional oid field, getting an implicit serial key is harder than ever. The solution is to get the serial key first, and then use that value in an insert:

<?
pg_query($conn, 'CREATE TABLE foo (key SERIAL, foo TEXT)');
$res=pg_query("SELECT nextval('foo_key_seq') as key");
$key=pg_fetch_array($res, 0);
$key=$key[key];
// now we have the serial value in $key, let's do the insert
pg_query("INSERT INTO foo (key, foo) VALUES ($key, 'blah blah')");
?>

Hope this helps...

gaagaagui at aiagrp dot net (26-Feb-2004 10:51)

note the following:
"The oid type is currently implemented as an unsigned four-byte integer. Therefore, it is not large enough to provide database-wide uniqueness in large databases, or even in large individual tables. So, using a user-created table's OID column as a primary key is discouraged. OIDs are best used only for references to system tables."
and
"OIDs are 32-bit quantities and are assigned from a single cluster-wide counter. In a large or long-lived database, it is possible for the counter to wrap around. Hence, it is bad practice to assume that OIDs are unique, unless you take steps to ensure that they are unique."
from: http://www.phphub.com/postgres_manual/index.php?p=datatype-oid.html

julian at e2-media dot co dot nz (26-Aug-2003 07:21)

The way I nuderstand it, each value is emitted by a sequence only ONCE. If you retrieve a number (say 12) from a sequence using nextval(), the sequence will advance and subsequent calls to nextval() will return further numbers (after 12) in the sequence.

This means that if you use nextval() to retrieve a value to use as a primary key, you can be guaranteed that no other calls to nextval() on that sequence will return the same value. No race conditions, no transactions required.

That's what sequences are *for* afaik :^)

a dot bardsley at lancs dot ac dot uk (15-May-2003 07:23)

As pointed out on a busy site some of the above methods might actually give you an incorrect answer as another record is inserted inbetween your insert  and the select. To combat this put it into a transaction and dont commit till you have done all the work

dtutar at yore dot com dot tr (26-Apr-2003 03:15)

This is very useful function :)

function sql_last_inserted_id($connection, $result, $table_name, $column_name) {
   $oid = pg_last_oid ( $result);
       $query_for_id = "SELECT $column_name FROM $table_name WHERE oid=$oid";
   $result_for_id = pg_query($connection,$query_for_id);
   if(pg_num_rows($result_for_id))
      $id=pg_fetch_array($result_for_id,0,PGSQL_ASSOC);
   return $id[$column_name];
}

Call after insert, simply ;)

webmaster at gamecrash dot net (03-Mar-2003 02:29)

You could use this to get the last insert id...

CREATE TABLE test (
  id serial,
  something int not null
);

This creates the sequence test_id_seq. Now do the following after inserting something into table test:

INSERT INTO test (something) VALUES (123);
SELECT currval('test_id_seq') AS lastinsertid;

lastinsertid should contain your last insert id.

bens at effortlessis dot com (09-Feb-2003 01:39)

[Editor's Note: If another record is inserted after the nextval is obtained and before you the INSERT query this code will fail.  This should not be done on busy sites.]

Especially now with an optional oid field, getting an implicit serial key is harder than ever. The solution is to get the serial key first, and then use that value in an insert:

<?
pg_query($conn, 'CREATE TABLE foo (key SERIAL, foo TEXT)');
$res=pg_query("SELECT nextval('foo_key_seq') as key");
$key=pg_fetch_array($res, 0);
$key=$key[key];
// now we have the serial value in $key, let's do the insert
pg_query("INSERT INTO foo (key, foo) VALUES ($key, 'blah blah')");
?>

Hope this helps...

juancri at tagnet dot org (30-Oct-2002 06:29)

Note that:

- OID is a unique id. It will not work if the table was created with "No oid".

- MySql's "mysql_insert_id" receives the conection handler as argument but PostgreSQL's "pg_last_oid" uses the result handler.