multiple identical calc and function in single query

From: Sim Zacks <sim(at)compulab(dot)co(dot)il>
To: pgsql-general(at)postgresql(dot)org
Subject: multiple identical calc and function in single query
Date: 2009-10-29 10:05:54
Message-ID: 4AE96902.3000700@compulab.co.il
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have a query which includes the same calculation and function call
multiple times.

Is the calculation and function call evaluated one time or as many times
as it is in the query?

Example:

326/getdisplayconversionmultiplebypn(pnid) is in this query 6 times. How
many times is it evaluated?

select pnid, (326/getdisplayconversionmultiplebypn(pnid)) + case when
coalesce(mpq,0)=0 then 0 else
mpq-((326/getdisplayconversionmultiplebypn(pnid)) % mpq)end as qty,
case when not translatempq then
(326/getdisplayconversionmultiplebypn(pnid)) + case when
coalesce(mpq,0)=0 then 0 else
mpq-((326/getdisplayconversionmultiplebypn(pnid)) % mpq)end
else ((326/getdisplayconversionmultiplebypn(pnid)) + case when
coalesce(mpq,0)=0 then 0 else
mpq-((326/getdisplayconversionmultiplebypn(pnid)) % mpq)end)/mpq end as
mpqqty
from manufacturerpartpn a
inner join leadstatemfgparts b on a.partid=b.partid and
a.manufacturerid=b.manufacturerid
where compatibilitygradeid in (50,100,200) and lifecycleid not
in (-400,-500)
and a.parentid is null and a.PartID=1108 and leadstateid = any
('{1,3}')
and leadstateid <> case when lfbcount>0 then 4 else -1 end
and leadstateid <> case when lpbcount>0 then 2 else -1 end

Responses

Browse pgsql-general by date

  From Date Subject
Next Message William Temperley 2009-10-29 10:20:19 Re: Emal reg expression
Previous Message Alban Hertroys 2009-10-29 08:48:20 Re: Slow running query with views...how to increase efficiency? with index?