Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris
 
PerlMonks  

Re^6: SELECT LAST_INSERT_ID does not work

by erix (Prior)
on Jul 01, 2017 at 21:25 UTC ( [id://1194008]=note: print w/replies, xml ) Need Help??


in reply to Re^5: SELECT LAST_INSERT_ID does not work
in thread SELECT LAST_INSERT_ID does not work

Oracle's RETURNING clause is simply the best way to do it.

I think PostgreSQL's RETURNING clause is far, far better.

Oracle's way forces you to read the values into variables that you also have to define beforehand:

(Oracle12 docs:) "For each expression in the RETURNING list, you must specify a corresponding type-compatible PL/SQL variable or host variable in the INTO list."

Yuck, that's awful. That's really not handy.

It seems the objections mentioned in this old SO thread are still valid.

By the way, postgres has had INSERT ... RETURNING since v8.2 (2006). I rather suspect that Oracle's kludgy PL/SQL-based imitation was bolted on after that, and because of it. (Well, maybe 'kludgy' is too harsh. But the above disadvantages certainly make it imperfect compared with Postgres.)

INSERT - Oracle

INSERT - PostgreSQL

Replies are listed 'Best First'.
Re^7: SELECT LAST_INSERT_ID does not work
by afoken (Chancellor) on Jul 02, 2017 at 13:50 UTC
    By the way, postgres has had INSERT ... RETURNING since v8.2 (2006). I rather suspect that Oracle's kludgy PL/SQL-based imitation was bolted on after that, and because of it. (Well, maybe 'kludgy' is too harsh. But the above disadvantages certainly make it imperfect compared with Postgres.)

    Well, let's have a look.

    Oracle has documented everything and the kitchen sink for ages, and the answer to almost any Oracle-related question, including far more than you ever wanted to know about Larry Ellison's coffee mug, could simply be RTFM. After about 15 sec with google and some hops through Oracle's website, I found the 8.0.4 documentation CD (also available as a ZIP file), and from there, the INSERT statement documentation. The documentation has a "copyright 1997" statement, matching the information found here and here.

    Now guess what's in the INSERT statement documentation:

    The RETURNING Clause

    An INSERT statement with a RETURNING clause retrieves the rows inserted and stores them in PL/SQL variables or bind variables. Using a RETURNING clause in INSERT statements with a VALUES clause enables you to return column expressions, ROWIDs, and REFs and store them in output bind variables. You can also use INSERT with a RETURNING clause for views with single base tables.

    That was in 1997, and around that time, I had my first contact with Oracle.

    Now, according to Wikipeda, PostgreSQL was at that time somewhere between 6.0 and 6.2. I could not find PostgreSQL documentation from that time, but hopping across the versions starting at 7.1 shows no traces of RETURNING until 8.2. And 8.2 was released at the end of 2006, at least 9 years after Oracle had documented RETURNING.

    So no, sorry, Oracle did not copy that from PostgreSQL. It looks like PostgreSQL copied the idea from Oracle.

    Note: Not the implementation! PostgreSQL works like a simple SELECT, whereas Oracle requires explicit use of bind variables. (Update:) Both ways have their advantages: The PostgreSQL way of RETURNING mutates the INSERT into something that behaves like a SELECT with (big) side effects. This allows easy fetching of the return value, but breaks the assumtion found in many programs and libraries that only SELECT statements return data, and especially INSERT doesn't. Oracle's way keeps INSERT a strictly non-SELECT statement and uses only bind parameters to return data, in a way that would also work with PL/SQL code.

    And I wish I had read the fine manual a little bit more 15 years ago, that would have simplified my software quite a bit. On the other hand, my software had to support MS SQL, and that sucked (and still sucks) so much that it would not have made a big difference.


    Update:

    Oracle also has documentation for 7.3.4 for browsing and download. Browsing that shows that INSERT had no RETURNING in 7.3.4.

    Alexander

    Yet another update: Typos fixed, thanks to AnomalousMonk.

    --
    Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)
Re^7: SELECT LAST_INSERT_ID does not work
by chacham (Prior) on Jul 02, 2017 at 04:09 UTC

    I think Oracle's version is "correct" because it completely separates what is returned into variables. Those variables are defined by the caling environment like any variables are. I do not see that as kludgy at all. Perhaps it is "pure sql" because it requires the use of a caller.

    On the other hand, i have never used PostreSQL, i just consider it as being similar to Oracle in (most) practice.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://1194008]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others perusing the Monastery: (6)
As of 2024-03-28 08:18 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found