On 17 November 2015 at 14:31, Adrian Klaver <[email protected]> wrote:
> On 11/17/2015 01:14 AM, Geoff Winkless wrote: > >> INSERT INTO test (c1, c2) VALUES (3, 7); UPDATE test SET c4=c1*c2 WHERE >> c1=3; UPDATE test SET c5=c4*c3 WHERE c1=3; >> > > Could the above not be shortened to?: > > INSERT INTO test (c1, c2) VALUES (3, 7); UPDATE test SET c4=c1*c2, > c5=c1*c2*c3 WHERE c1=3; > Well yes, but having to do a (potentially very) complicated parse just to get to that point is a bit of a wasted effort. > Also from your first post: > "To be clear, the SQL is generated dynamically based on data, ..." > > Would it not be easier to just calculate the values in whatever program is > generating the SQL and just supply the calculated values in the INSERT? > Easier how? At the moment I can just pass the derivations straight through to postgres and it does all the evaluation for me. If I do that in the code, I have to implement a complete parser and evaluation engine... so I'd say probably no, it's not :) > Lastly, and this is more about my curiosity then anything else, why > calculate the values at all? You have the original values c1 and c2 the > others can be derived at any time. I am just interested in what the benefit > is to calculate them on initial data entry? I've simplified to show an example. In reality the derivations are significantly more complex and represent business rules, configurable by a second-party admin. > Aargh, just realized I am not seeing where c3 comes from. It takes the column's default value, since it's not explicit in the first INSERT. Geoff
