A common need for web apps is to insert something into a database table and then immediately find out which ID got assigned to your new thing so that you can refer to it later in the script (probably while inserting more data). A lot of folks start out doing something along the lines of:
INSERT INTO Addresses (address, city, state, postalcode) VALUES ('123 Main St.','Springfield','MA','01109'); SELECT max(id) as ID FROM Addresses;
After that, the folks on Microsoft SQL Server tend to discover the combination of SET NOCOUNT ON and @@IDENTITY:
SET NOCOUNT ON; INSERT INTO Addresses (address, city, state, postalcode) VALUES ('123 Main St.','Springfield','MA','01109'); SELECT id = @@IDENTITY; SET NOCOUNT OFF;
Now, at my new gig, we use PostgreSQL. Today, I finally found myself wondering if/how I could achieve the NOCOUNT/@@IDENTITY behavior. Turns out, it's super easy and almost even sexy (since version 8.2, I guess):
INSERT INTO Addresses (address, city, state, postalcode) VALUES ('123 Main St.','Springfield','MA','01109') RETURNING id;
That, my friends, is HOTT.
Sure, it's non-standard SQL, but what's the point of picking a particular RDMS if you don't use the magic that it offers?