Re: how to determine OID of the row I just inserted??? - Mailing list pgsql-general

On Thu, 6 Feb 2003, Jules Alberts wrote:

> Hello everybody,
>
> Here's a question I have asked some time ago and Google tells me I'm
> not the only one with this problem, but I haven't found a solution yet
> :-(. I have a setup like this:
>
>     customer {id serial, name varchar, address bigint}
>     person   {id serial, name varchar, address bigint}
>     address  {id serial, street varchar}
>
>     customer.address points to address.id
>     person.address points to address.id
>
> So, addresses are stored in a seperate table, customer.address and
> person.address should have a value that exists in address.id. When I
> add an address for customer X, I must do something like:
>
>     insert into address (street) values ('Penny Lane');
>     update customer set address = ??? where name = 'X';
>
> If I would do this in pgsql there would be no problem, because the OID
> is echoed when the update succeeds.

Whoa. You're on about different things. OID is most certainly not what you
want, it is not the value in your id column. What you want is to forget about
OIDs and find the value inserted into id for the row you just inserted.

The serial type is based on sequences so you can query the sequence associated
with it to find it's current value (after running the insert). You do that by
using: SELECT curval('my_serial_column_seq')

Now, I've never used serial, I have only created and used my own sequences for
this task and so I can not say how you know what the sequence underlying the
serial is called. It must be in the documentation somewhere and you'll probably
get a few replies from people who do know.



> But I use PHP or pl/pgsql (others
> have exactly the same problem with JDBC) and I know of no way to solve
> this. Something like lastval() IMHO is way too risky. I need something
> like a return value:
>
>     catchOID = returnQueryOID('insert into address (street)
>         values ('Penny Lane'));
>     update customer set address = 'select id from address where
>         oid = catchOID' where name = 'X';

Ah, I see, so it wasn't such a mistake as I thought. However, the basic idea is
the same; forget about oids and use the serial type [sequence] otherwise you
may as well get rid of it.

> Sorry for any syntax errors in my examples (it's half semi code), I
> hope you understand the problem. If somebody knows a good solution, IMO
> this would be something to put in a FAQ, because exactly the same
> question arises when you do an insert on a table where the primary key
> is generated automatically and you want to show the result after the
> row is updated (which is very common).
>
> TIA for any help, I really need a solution...
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

--
Nigel J. Andrews