Re: Using oid as pkey

On Aug 20, 2007, at 17:51 , D. Dante Lorenso wrote:

> Michael Glaesemann wrote:
>> On Aug 20, 2007, at 16:58 , Ed L. wrote:
>> You'd have  to specify your table WITH OIDS anyway as they're no  
>> longer used by default for table rows, so there's really nothing  
>> to be gained by using oids.
>
> How exactly can you get rid of OIDs when using a language like PHP?

I've never used OIDs when programming in PHP (or any other language,  
IIRC)

>   The "magic" of SERIAL and BIGSERIAL is that they are supposed to  
> be like MySQL's AUTO INCREMENT feature and they create their own  
> SEQUENCE for you automatially to handle the serialization.

I don't know the exact history of sequences, but I believe they have  
more to do with Oracle and/or the SQL spec than MySQL. But I could be  
wrong here.

> Using a brain-dead sample table that looks like this:
>
> 	CREATE table some_table (
> 		col0 SERIAL,
> 		col1 VARCHAR,
> 		col2 VARCHAR
> 	);

You can with fair certainty predict the name of the sequence, you can  
look it up using the system tables, or use the pg_get_serial_sequence  
system information function.

> I want to do something like this:
>
> 	INSERT INTO some_table (col1, col2)
> 	VALUES ('val1', 'val2');

INSERT INTO some_table (col1, col2)
VALUES ('val1', 'val2')
RETURNING col0;

> In PHP with PDO, I've only been able to get this by first finding  
> the OID value from 'lastInsertId' and then using that OID to run  
> this select:
>
> 	SELECT $column AS last_inserted_id
> 	FROM $table
> 	WHERE oid = ?

If you're using an ORM, I'm surprised it doesn't already incorporate  
something like pg_get_serial_sequence already.

> How else could this be done without the round-trip back the db  
> server or knowing too much about the SERIAL internals that I  
> shouldn't really need to know?

I hope I've given you some options here.

Michael Glaesemann
grzm seespotcode net