Re: the best way? (maybe a PL/pgSQL question)

okay, i'm trying to implement this now and i think there
is a much easier way to do this but i'm not 100% sure.

i don't think that i need to write a complicated stored
procedure like i described below. what i think that i can
do is to run a big, honkin' select that will return rows
looking like:
ItemID, CostSystemID, Cost

and then insert those rows into the WeeklyCost table.

what i think the big, honkin' select would do is to
join the weeklystats and costtypes tables based on
the StatTypeID. the join would add a calculated column
(i'll refer to that as WeeklyCost from now on) that
has the cost calc as a new column. then the select would
sum up the WeeklyCost for each unique ItemID/CostSystemID
combo.

are my expectations reasonable? if so, is it reasonable
to create a view that does this for all weeks of the
schedule and when i want to update the costs i just do
a select from the view for the week i want? would that
have any issues of running all the calcs for all weeks
when i only want the most recent or would it only run
the calcs for the week i want?

even better, haven't i seen a discussion thread about a
SELECT INTO construct that would do it all in one step
for me?

many thanks!

rjsjr

> -----Original Message-----
> From: Robert J. Sanford, Jr. [mailto:rsanford(at)nolimitsystems(dot)com]
> Sent: Monday, September 03, 2001 9:04 PM
> To: pgsql-novice(at)postgresql(dot)org
> Subject: the best way? (maybe a PL/pgSQL question)
>
>
> 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
>