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