On 4 July 2014 00:07, Tom Lane <t...@sss.pgh.pa.us> wrote: > TBH, I thought that RETURNING PRIMARY KEY was already fairly iffy > in that the application would have little idea what it was getting back. > IF EXISTS would make it so spongy as to be useless, IMO. >
IF EXISTS is pretty pointless - while the behaviour of getGeneratedKeys() isn't defined for cases where there aren't any, it's only meaningful if the caller has previously asked for the keys to be returned, and someone asking to do that where it doesn't make sense can get an error as far as I'm concerned. No-one does this in practice. Looking at the feature as a more general SQL construct, ISTM that if someone requests RETURNING PRIMARY KEY where there isn't one, an error is appropriate. And for the IF EXISTS case, when on earth will someone request a primary key even if they're not sure one exists? > It sounds to me like designing this for JDBC's getGeneratedKeys method > is a mistake. There isn't going to be any way that the driver can support > that without having looked at the table's metadata for itself, and if > it's going to do that then it doesn't need a crutch that only partially > solves the problem anyhow. > Sure it can - it append RETURNING PRIMARY KEY and hand back a ResultSet from whatever was returned. It's CURRENTLY doing that, but it's appending RETURNING * and leaving it up to the caller of getGeneratedKeys() to work out which columns the caller is interested in. Turns out that's actually ok - most Java-based ORMs have more than enough metadata about the tables they manage to know which columns are the primary key. It's the driver that doesn't have that information, which is where RETURNING PRIMARY KEY can help by not forcing the driver to request that every single column is returned. The only downside that I see is cases where someone requests the keys to be returned but already has a RETURNING clause in their insert statement - what if the requested columns don't include the primary key? IMO it's probably enough to document that if the caller wants to issue a RETURNING clause then they better make sure it contains the columns they're interested in. This is already way outside anything that standard ORMs will do (they don't know about RETURNING) so anyone doing this is hand-crafting anyway. Cheers Tom
