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

Search Discussions

  • Robert J. Sanford, Jr. at Sep 4, 2001 at 3:59 am
    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.
    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
  • Jason Tan at Sep 4, 2001 at 4:40 am

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedSep 4, '01 at 2:04a
activeSep 4, '01 at 4:40a
posts3
users2
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase