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
