Matthew Wilson írta:
> On Mon Aug 16 10:26:36 2010, Tom Lane wrote:
>
>> Matthew Wilson <matt@tplus1.com> writes:
>>
>>> All I can come up with so far is to use a view and then another view on
>>> top of that one:
>>>
>> Note that you don't actually need a view, as you can just write the
>> subselect in-line:
>>
>> select a, b, c,
>> case when c < 0 then 'no'
>> else 'yes'
>> end as d
>> from (select a, b, a - b as c from foo) as v1;
>>
>> This is the standard method for avoiding repeat calculations in SQL.
>>
>> One thing to keep in mind is that the planner will usually try to
>> "flatten" a nested sub-select (and whether it was written out manually
>> or pulled from a view does not matter here). This will result in the
>> sub-select's expressions getting inlined into the parent, so that the
>> calculations will actually get done more than once. If you're trying
>> to reduce execution time not just manual labor, you may want to put an
>> "offset 0" into the sub-select to create an optimization fence. But
>> test whether that really saves anything --- if there are bigger joins
>> or additional WHERE conditions involved, you can easily lose more than
>> you gain by preventing flattening.
>>
>> regards, tom lane
>>
>>
>
> Thanks so much for the help!
>
> I don't care if the code is rearranged so that c is replaced with an
> inline definition during compilation. I'm not concerned about
> efficiency here. I just don't want to have to redefine it manually over
> and over again, because I know that as I update how c is defined, I'll
> forget to update it everywhere.
>
> Maybe sql needs a preprocessing macro language like C.
>
Or maybe we can dust off my GENERATED column patch
I posted here in 2006. :-)
Best regards,
Zoltán Böszörményi