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
