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

What I do is to call currval(tablename_idcolumn_seq) after the insert, which will always work as long as you don't
exceedthe NAMEDATALEN for column/field name lengths.  If you do, they get truncated somthing like
reallylongtablena_reallylongfieldna_seq. I forget the exact method they use for truncating them, but if you needed to
youcould include its logic into a function to figure out the sequence name. 

That turns the below into

    insert into address (street) values ('Penny Lane');
    update customer
        set address =
         (select currval('address_addressid_seq') )
        where name = 'X';

Which would work even with duplicate streets.

Ian Harding
Programmer/Analyst II
Tacoma-Pierce County Health Department
iharding@tpchd.org
(253) 798-3549

"If any man can convince me and bring home to me that I do not think or act aright, gladly will I change; for I search
aftertruth, by which man never yet was harmed. But he is harmed who abideth on still in his deception and ignorance" 

              -- Marcus Aurelius

>>> Dennis Gearon <gearond@cvc.net> 02/06/03 08:33AM >>>
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.alberts@arbodienst-limburg.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@postgresql.org)
>




---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org