Re: How to refer to computed columns from other computed columns?

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.

<ducks>

Matt