Re: Simple Atomic Relationship Insert

This CTE approach doesn't appear to play well with multiple concurrent
transactions/connections.

On Tue, Jan 13, 2015 at 10:05 AM, John McKown <john(dot)archie(dot)mckown(at)gmail(dot)com>
wrote:

> 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
>