Re: Simple Atomic Relationship Insert

On Tue, Jan 13, 2015 at 11:45 AM, Robert DiFalco <robert(dot)difalco(at)gmail(dot)com>
wrote:

> 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

Maranatha! <><
John McKown