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
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
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
> -----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)
> 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?
In response to
pgsql-novice by date
|Next:||From: postgres||Date: 2001-09-04 04:40:35|
|Subject: Re: Perl DBI Drivers|
|Previous:||From: Chuck Wolber||Date: 2001-09-04 03:54:49|
|Subject: Perl DBI Drivers|