Pardon the edit, but I'll delete the previous quotations, since
the issues of syntax and functionality are resolved. Thank you, Tom.
The next problem is simplicity, or elegance, if you like.
I would like the code to read like this:
CREATE OR REPLACE FUNCTION share_cost( cost NUMERIC ) RETURNS VOID AS $$
-- Spread the amount COST across a number of records WHERE reference = 'SHARE'
-- increment the AMOUNT field by the amount of a nearly-equal share, so that
-- the sum of the shares exactly equals COST.
DECLARE
shares INTEGER;
error_term NUMERIC;
BEGIN
SELECT SUM(1) from temp WHERE reference = 'SHARE' into shares;
error_term := 0;
UPDATE temp SET amount = amount + calc_share( cost, shares, error_term )
WHERE reference = 'SHARE';
END;
$$ LANGUAGE PLPGSQL;
This example has the advantage of not requiring a primary key on
my temporary table, since the UPDATE statement ensures that each
record is processed in turn, with no ambiguity as to which record
is being updated.
However, the "calc_share" function has one INOUT parameter
"error_term" and an OUT parameter "result". From what I gather
so far, PL/pgSQL does not allow a function with OUT or INOUT
parameters to return a scalar result value. Based on that
understanding, my code looks like:
CREATE OR REPLACE FUNCTION share_cost( cost NUMERIC ) RETURNS VOID AS $$
-- Spread the amount COST across a number of records WHERE reference = 'SHARE'
-- increment the AMOUNT field by the amount of a nearly-equal share, so that
-- the sum of the shares exactly equals COST.
DECLARE
shares INTEGER;
error_term NUMERIC;
one_share NUMERIC;
share_record RECORD;
BEGIN
SELECT SUM(1) from temp WHERE reference = 'SHARE' into shares;
error_term := 0;
FOR share_record IN SELECT * FROM temp WHERE reference = 'SHARE' LOOP
SELECT error_term, result FROM calc_share( cost, shares, error_term ) INTO error_term, one_share;
UPDATE temp SET amount = amount + one_share
WHERE temp.acct_id = share_record.acct_id;
END LOOP;
END;
$$ LANGUAGE PLPGSQL;
So the simple UPDATE statement in the first example becomes a
somewhat clunky FOR loop in the second example, and the second
example also requires a primary key on acct_id to ensure that the
UPDATE and the FOR loop reference the same record.
Is that as good as this can get, or is there a simpler way, more
along the lines of the first version?
Thanks again for the education.
Jim
