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

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
>

> is there a way that i can do the foreach using the FOR
> row in SELECT construct while only issuing one select?

I am not sure about what you mean here.
So I am giving a reply to what I think you mean.

the FOR IN can be used like this:
create table blah(
    field1        int,
    field2        int,
    field3        int
);

CREATE FUCNTION blah() RETURN null AS
'
DECLARE
    -- declare a variable oif type recrod to store results in
    mydata     record;
    --declare some variables
    -- %TYPE allows you to defer to typing of variables
    --until exectution time. the type will be whatever the type is for
    --the column of the specified table in this case it would be int
    -- and that is what all columns of the table are declared as
    var1    blah.field1%TYPE;
    var2    blah.field2%TYPE;
    var3    blah.field3%TYPE;
    var4    blah.field3%TYPE;
BEGIN

    -- perform query
    FOR mydata IN select field1, fields2,field3 from blah where
field3>100 LOOP
    --for each row retunred in the query this block will be executed
        var1 := mydata.field1;
        var2 := mydata.field2;
        var3 := mydata.field3;
        var4 := var1+var2+var3;
    END LOOP;
END;
'language 'plpgslq';

Which says exceute the query "select field1,field2,field3 form blah wehre
field3>100" and for each row returned (the rows get returned n a cursor
called "mydata" - a record is  a special type for returned reocrds its
strucutre varies depneidng ofn the data returned from query), assign
field1 to var1, field2 to var2, and field3 to var3 and sum them.
(I dont do anythign with the sum, but that shoudl I hope demonstrate how
the FOR IN LOOP construct can be used).

Thsi url uis the only reference material I ahve found for PL/pgSQL
http://www.postgresql.org/idocs/index.php?plpgsql-description.html

This url has instructions on how to enable PL/pgSQL in postgrers:
http://www.postgresql.org/idocs/index.php?xplang.html

It is not enabled by default.

You will have to do this before you can use any PL/pgSQL.

Hope this helps.
Jason