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]

 



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.

<ducks>

Matt


-- 
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