1. Need to ignore inactive BOM, i.e only M_Product_BOM.IsActive='Y'
2. Need to include basic check for infinite BOM, i.e M_Product_BOM.M_Product_ID != M_Product_BOM.M_ProductBOM_ID
3. For PostgreSQL, function should be declare as Stable instead of Volatile.
Adding some documentation about commit http://bitbucket.org/idempiere/idempiere/commits/df38654
POSTGRESQL has three types of functions:
volatile (the default)
stable (the return for same parameter is the same within a trx)
immutable (the return is always the same)
so, changing the postgresql functions to stable speed up queries because the return value is cached
deterministic (similar to postgresql immutable)
NOTE that oracle doesn't have a notation for the stable - so this cannot be exploited directly within the function.
However you can workaround it using subquery caching as explained here:
so, in the info window, you can change the info column definition from:
other candidates for change to stable:
candidates for change to immutable:
will keep this ticket open to do the suggested changes on these functions