From: | Dennis Gearon <gearond(at)cvc(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org, "Jules Alberts" <jules(dot)alberts(at)arbodienst-limburg(dot)nl> |
Subject: | Re: how to determine OID of the row I just inserted??? |
Date: | 2003-02-06 16:33:12 |
Message-ID: | USXUOJYVGBXUVTI64JUPWT2Z83A7OK.3e428e48@cal-lab |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
What someone told me was to do this:
insert into address (street) values ('Penny Lane');
update customer
set address =
(select id
from address
where street = 'Penny Lane')
where name = 'X';
Obviously, there can only be one id for 'Penny Lane'.
2/6/2003 6:08:17 AM, "Jules Alberts" <jules(dot)alberts(at)arbodienst-limburg(dot)nl> 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:
>
>
>If I would do this in pgsql there would be no problem, because the OID
>is echoed when the update succeeds. 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';
>
>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(at)postgresql(dot)org)
>
From | Date | Subject | |
---|---|---|---|
Next Message | will trillich | 2003-02-06 16:33:51 | Re: Question: unique on multiple columns |
Previous Message | Andrew Sullivan | 2003-02-06 16:23:11 | Re: password() function? |