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

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