On Thu, Nov 08, 2001 at 09:47:18AM -0600, Thomas T. Veldhouse wrote:
> MySQL is easier with PHP, for one reason, it is much easier to get the
> value of an autoincrement key for a row you just inserted.
> PostgreSQL is rather lacking in this respect.
He's refering to the mysql_insert_id() function, the reference for which
is found here:
http://www.php.net/manual/en/function.mysql-insert-id.php
A nice function indeed. The PostgreSQL PHP library has a similar
function, but it applies to ALL object, regardless whether the table has
a SERIAL (or autoincrement) field or not. It's called pg_getlastoid()
and is referenced here:
http://www.php.net/manual/en/function.pg-getlastoid.php
OID's are tricky with PostgreSQL. They apply to ALL database objects,
including indexes, sequences (used in SERIAL columns), tables, rows,
etc. The reason why they're tricky is that if you use them for
referential integrity (reference an OID from one table as a key for
another), you have to be careful with how you dump/restore your
databases. There is an option in the PostgreSQL utilities to preserve
old OID's from a previous dump, but it has to be specified manually
(AFAIK).
Now, if you don't want to deal with the OID dump/restore problems with
regards to referential integrity, you can create a SERIAL field in your
tables that you wish to provide unique ID's for records. A SERIAL is a
builtin TRIGGER to create a SEQUENCE specific to that column.
CREATE TABLE test1 (
record_id SERIAL,
value VARCHAR(255)
)
Is equivalent to saying
CREATE SEQUENCE test1_record_id_seq;
CREATE TABLE test1 (
record_id INTEGER DEFAULT nextval('test1_record_id_seq'),
value VARCHAR(255)
)
The nice thing about this is that if you want to preserve the sequence,
but you want to drop and recreate the table, you can do so by simply
DROPping the table and recreating it using the second declaration listed
above. The sequence lives on until you actually drop it manually:
DROP SEQUENCE test1_record_id_seq;
So, if you opt to use SEQUENCEs, you need to use the return value of
pg_getlastoid() in your select statement to query for the sequence
field (influenced from user submissions on pgp.net page):
// Insert record
$result = pg_exec( $conn, "INSERT INTO table1 (value) VALUES ('blah')");
if (!$result) {
error("Unable to insert\n.");
} else {
// Success, let's display it.
$oid = pg_getlastoid($result);
if ($oid<0) error ("Can't find OID of last insert. PANIC!");
$result = pg_exec( $conn,
"SELECT record_id,value FROM table1 WHERE oid=$oid;");
if (!$result) error ("Um... This sucks");
// Get first row
$arr = pg_fetch_array( $result, 0 );
echo "Record_id: " . $arr["record_id"];
echo "Value: " . $arr["value"];
}
So, is mysql_insert_id() easier? Depends upon your needs. ;-) With
PostgreSQL, you still get a unique reference to the last row inserted,
even if there is no SEQUENCE field in the table. Therein lies the
distinction. With MySQL, you must have an autoincrement field in the
table in order to use mysql_insert_id() function. With PostgreSQL, it's
automatic regardless. Frankly, I find the PostgreSQL implementation
more powerful and flexible.
References:
http://techdocs.postgresql.org
http://www.ca.postgresql.org/users-lounge/docs/#7.1
http://www.ca.postgresql.org/users-lounge/docs/7.1/reference/sql-createsequence.html
http://www.php.net/manual/en/function.pg-exec.php
--
Chad Walstrom <chewie at wookimus.net> | a.k.a. ^chewie
http://www.wookimus.net/ | s.k.a. gunnarr
Key fingerprint = B4AB D627 9CBD 687E 7A31 1950 0CC7 0B18 206C 5AFD