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

2010/8/16 Boszormenyi Zoltan <zb(at)cybertec(dot)at>:
> Matthew Wilson írta:
>> On Mon Aug 16 10:26:36 2010, Tom Lane wrote:
>>
>>> Matthew Wilson <matt(at)tplus1(dot)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
>

You mean this?:
http://archives.postgresql.org/pgsql-hackers/2006-07/msg00543.php
And this?: http://archives.postgresql.org/pgsql-hackers/2006-08/msg00038.php
And this?: http://archives.postgresql.org/pgsql-patches/2007-04/msg00107.php

--
Thom Brown
Registered Linux user: #516935