i have a set of tables that define an allowed set of
statistics, a cost for each statistic, an item that
statistics can be applied to, a item-stat table that
lists all the statistics that have been applied to
and item during a given time period, and an item-
cost table that holds the total cost of an item
during that period. the last table is used because
the number of items and statistics for each item is
sufficiently large that dynamically calculating
those numbers would suck performance way down.
to add some level of complexity there are multiple
cost scenarios which are defined in a different
table. each scenario can have its own collection of
cost/statistics. also, there can be multiple costs
within a scenario for a statistic.
currently, the statistics for a time period (one
calendar week to be exact) are handed over at one
time. i parse those and dump the raw stats into the
database. given that there are multiple (unknown
number of) cost scenarios, i do not think that i
can effectively perform the calcs during parsing.
so, my plan is to parse the stats and have a stored
procedure run the calcs to determine total cost. my
algorithm looks like...
foreach costScenario
{
foreach item
{
cost = 0
foreach statistic
{
foreach costReferencingStatistic
{
cost += calculation(cost.value, stat.value)
}
}
updateCalculateCostTable
}
}
what is the best way of implementing this algorithm
in PL/pgSQL?
should i be creating temporary tables to hold the
information i need and working off of that?
should i be creating views that i select out of instead
of doing a select inside of all the foreach statements?
is there a way that i can do the foreach using the FOR
row in SELECT construct while only issuing one select?
thanks!
rjsjr
