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
