Matthew Wilson írta: > On Mon Aug 16 10:26:36 2010, Tom Lane wrote: > >> Matthew Wilson <m...@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 >> >> > > 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
