Thanks John. I've been seeing a lot of examples like this lately. Does the following approach have any advantages over traditional approaches?
WITH sel AS ( SELECT id FROM hometowns WHERE name = 'Portland' ), ins AS ( INSERT INTO hometowns(name) SELECT 'Portland' WHERE NOT EXISTS (SELECT 1 FROM sel) RETURNING id ) INSERT INTO users(name, hometown_id)
VALUES ('Robert', SELECT id FROM ins UNION ALL SELECT id FROM sel);
Oh, that is very clever. I've not see such a thing before. Thanks.
I've added it to my stable of "tricks". Which aren't really tricks, just really nice new methods to do something.
The main advantage that I can see is that it is a single SQL statement to send to the server. That makes it "self contained" so that it would be more difficult for someone to accidentally mess it up. On the other hand, CTEs are still a bit new (at least to me) and so the "why it works" might not be very obvious to other programmers who might need to maintain the application. To many this "lack of obviousness" is a detriment. To me, it means "update your knowledge". But then, I am sometimes a arrogant BOFH. Add that to my being an surly old curmudgeon, and you can end up with some bad advice when in a "corporate" environment. The minus, at present, is that it is "clever" and so may violate corporate coding standards due to "complexity". Or maybe I just work for a staid company.
--
While a transcendent vocabulary is laudable, one must be eternally careful so that the calculated objective of communication does not become ensconced in obscurity. In other words, eschew obfuscation.
111,111,111 x 111,111,111 = 12,345,678,987,654,321