Re: the best way? (maybe a PL/pgSQL question) - Mailing list pgsql-novice
From | Robert J. Sanford, Jr. |
---|---|
Subject | Re: the best way? (maybe a PL/pgSQL question) |
Date | |
Msg-id | HOEFIONAHHKFEFENBMNOMEGACCAA.rsanford@nolimitsystems.com Whole thread Raw |
In response to | the best way? (maybe a PL/pgSQL question) ("Robert J. Sanford, Jr." <rsanford@nolimitsystems.com>) |
List | pgsql-novice |
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@nolimitsystems.com] > Sent: Monday, September 03, 2001 9:04 PM > To: pgsql-novice@postgresql.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 >
pgsql-novice by date: