From: | "Robert J(dot) Sanford, Jr(dot)" <rsanford(at)nolimitsystems(dot)com> |
---|---|
To: | <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: the best way? (maybe a PL/pgSQL question) |
Date: | 2001-09-04 03:58:14 |
Message-ID: | HOEFIONAHHKFEFENBMNOMEGACCAA.rsanford@nolimitsystems.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(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
>
From | Date | Subject | |
---|---|---|---|
Next Message | postgres | 2001-09-04 04:40:35 | Re: Perl DBI Drivers |
Previous Message | Chuck Wolber | 2001-09-04 03:54:49 | Perl DBI Drivers |