On Sun, Feb 11, 2007 at 09:16:36PM -0600, Oscar Alberto Chavarria Marin wrote:
> CREATE FUNCTION
> add_reimburse(integer,integer,integer,integer,integer,integer,integer)
Why does the function take all these parameters? It doesn't do anything
with them.
> SELECT count(*) INTO client_count FROM investments;
> FOR counter IN 1 TO client_count
> LOOP
Is this the actual code? CREATE FUNCTION fails with a syntax error
in 8.0 and later, which presumably you're using since the function
has dollar quotes. Perhaps you meant to do this (although a simpler
solution exists; see later):
FOR sumyields IN SELECT * FROM investments LOOP
> yields:=
>
sumyields.return1+sumyields.return2+sumyields.return3+sumyields.return4+sumyields.return5+sumyields.return6+sumyields.return7
> ;
The table definition you showed doesn't have NOT NULL constraints
on these columns -- are any of the values NULL? If so then their
sum will be NULL. You might need to use COALESCE:
yields := COALESCE(sumyields.return1, 0) +
COALESCE(sumyields.return2, 0) +
...
> INSERT INTO totalreturns(total_reimburse) VALUES(yields);
> yields:=0;
There's no need to set yields to 0 at the end of the loop when
you're going to be setting it at the beginning of the next loop.
> END LOOP;
> return counter;
> END;
> $$ language plpgsql;
Are you aware of INSERT ... SELECT? It looks like you're trying to
do this:
INSERT INTO totalreturns (total_reimburse)
SELECT COALESCE(return1, 0) + COALESCE(return2, 0) + COALESCE(return3, 0) +
COALESCE(return4, 0) + COALESCE(return5, 0) + COALESCE(return6, 0) +
COALESCE(return7, 0)
FROM investments;
In PL/pgSQL you can get the number of affected rows with GET DIAGNOSTICS:
GET DIAGNOSTICS counter := ROW_COUNT;
--
Michael Fuhr
