Op 6 Feb 2003 (14:25), schreef Nigel J. Andrews <nandrews@investsystems.co.uk>:
> On Thu, 6 Feb 2003, Jules Alberts wrote:
<snip>
> > 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.
Thanks for your reaction Nigel, but my problem remains the same whether
I use OIDs or sequence values. In a more abstract way the problem would
be:
"how do I determine which row was affected by my last INSERT
or UPDATE statement"
If you know the primary key value it's easy, you just do
INSERT INTO customer (id, name) VALUES ('1234', 'Paul');
SELECT * FORM customer WHERE id = '1234';
But the problem is that in my situation there is no way of knowing the
primary key value. Pgsql very politely echoes the OID of the affected
row. Languages like pl/pgsql and PHP AFAIK, don't. Maybe getting the
lastval() of the primary key sequence would be a solution, but what
happens if someone else accesses the sequence between my INSERT and
SELECT?
So no matter if OIDs are lost with a dump / restore, if they will be
dropped in the future or not, my problem remains...
TIA for any other tips!