Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



2010/8/16 Boszormenyi Zoltan <zb@xxxxxxxxxxx>:
> Matthew Wilson írta:
>> On Mon Aug 16 10:26:36 2010, Tom Lane wrote:
>>
>>> Matthew Wilson <matt@xxxxxxxxxx> 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

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux