Re: referencing other INSERT VALUES columns inside the insert

On 17 November 2015 at 14:31, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
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