Skip site navigation (1) Skip section navigation (2)

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

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: (view raw, whole thread or download thread mbox)
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

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!


> -----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

In response to

pgsql-novice by date

Next:From: postgresDate: 2001-09-04 04:40:35
Subject: Re: Perl DBI Drivers
Previous:From: Chuck WolberDate: 2001-09-04 03:54:49
Subject: Perl DBI Drivers

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group