Re: [HACKERS] Getting OID in psql of recent insert
От | Tom Lane |
---|---|
Тема | Re: [HACKERS] Getting OID in psql of recent insert |
Дата | |
Msg-id | 762.942983114@sss.pgh.pa.us обсуждение исходный текст |
Ответ на | Getting OID in psql of recent insert (Bruce Momjian <pgman@candle.pha.pa.us>) |
Ответы |
Re: [HACKERS] Getting OID in psql of recent insert
(Bruce Momjian <pgman@candle.pha.pa.us>)
Re: [HACKERS] Getting OID in psql of recent insert (Peter Eisentraut <peter_e@gmx.net>) |
Список | pgsql-hackers |
Bruce Momjian <pgman@candle.pha.pa.us> writes: > In writing the book, I see the serious limitation that there is no way > in psql to access the most recently inserted oid. Without it, there > seems to be no way to use the oid value as a foreign key in another > table. > Should I add a function to return the most recently assigned oid to the > backend, or is there a better way? I'm not sure why, but a backend-side function seems like the wrong way to approach it. I guess I'm worried that the state would be too volatile on the backend side. (Example: if you use the hypothetical lastoid() function in an SQL query that causes triggers or rules to be invoked behind your back, those triggers/rules could do new inserts. Will lastoid() still return the "right" value by the time it gets executed?) It'd certainly be easy enough for psql to save off the OID anytime it gets an "INSERT nnn" command response. The missing link is to invent a way for a psql script to access that value and insert it into subsequent SQL commands. If you want to attack this, I'd suggest thinking a little larger than just the last-OID problem. I'd like to be able to save off both insertion OIDs and values extracted by SELECTs into named variables of some sort, and then insert those values into as many later commands as I want. Right now there's no way to do any such thing in a psql script; you have to move up a level of difficulty into ecpg or pgtcl or even C code if your application needs this. Plain psql scripts would become substantially more powerful if psql had a capability like this. OTOH: we shouldn't ask psql to do everything under the sun. I'd certainly think that it'd be unreasonable to try to do conditional evaluation or looping in psql scripts, for instance. Maybe the right answer is to teach people a little bit about using honest-to-goodness scripting languages when their applications reach this level of complexity. How much daylight is there between needing script variables and needing control flow, do you think? regards, tom lane PS: not relevant to your main point, but to your example: I think it's a real bad idea to teach people to use OIDs as foreign keys. That'll create all kinds of trouble when it comes time to dump/reload their database. Better to tell them to use SERIAL columns as keys. Not so incidentally, we have currval() already...
В списке pgsql-hackers по дате отправления: