Thread: Problem adding columns
I have two tables, from one of which I wish to obtain the sum of several columns (return1,....return7) and insert the result into the other.
Here are the tables layout:
CREATE TABLE investments
(
investment_id serial NOT NULL,
client_id integer,
client_number integer,
investment_date date,
investment_amount integer,
return1 integer,
return2 integer,
return3 integer,
return4 integer,
return5 integer,
return6 integer,
return7 integer
)
CREATE TABLE totalreturns
(
client_name character varying(128),
initial_invest numeric(8),
total_reimburse numeric(8),
difference numeric(7)
)
And the function to perform the aforementioned calculation:
CREATE FUNCTION add_reimburse(integer,integer,integer,integer,integer,integer,integer) RETURNS integer AS $$
DECLARE
sumyields investments%rowtype;
yields integer;
client_count integer;
counter integer;
BEGIN
SELECT count(*) INTO client_count FROM investments;
FOR counter IN 1 TO client_count
LOOP
yields:= sumyields.return1+sumyields.return2+sumyields.return3+sumyields.return4+sumyields.return5+sumyields.return6+sumyields.return7 ;
INSERT INTO totalreturns(total_reimburse) VALUES(yields);
yields:=0;
END LOOP;
return counter;
END;
$$ language plpgsql;
The code is not resulting in any calculations inserted on the "recipient" table totalreturns.
Thank you in advance for any suggestions.
--
Regards
Oscar Alberto Chavarria
Mobile: +506 814-0247
Here are the tables layout:
CREATE TABLE investments
(
investment_id serial NOT NULL,
client_id integer,
client_number integer,
investment_date date,
investment_amount integer,
return1 integer,
return2 integer,
return3 integer,
return4 integer,
return5 integer,
return6 integer,
return7 integer
)
CREATE TABLE totalreturns
(
client_name character varying(128),
initial_invest numeric(8),
total_reimburse numeric(8),
difference numeric(7)
)
And the function to perform the aforementioned calculation:
CREATE FUNCTION add_reimburse(integer,integer,integer,integer,integer,integer,integer) RETURNS integer AS $$
DECLARE
sumyields investments%rowtype;
yields integer;
client_count integer;
counter integer;
BEGIN
SELECT count(*) INTO client_count FROM investments;
FOR counter IN 1 TO client_count
LOOP
yields:= sumyields.return1+sumyields.return2+sumyields.return3+sumyields.return4+sumyields.return5+sumyields.return6+sumyields.return7 ;
INSERT INTO totalreturns(total_reimburse) VALUES(yields);
yields:=0;
END LOOP;
return counter;
END;
$$ language plpgsql;
The code is not resulting in any calculations inserted on the "recipient" table totalreturns.
Thank you in advance for any suggestions.
--
Regards
Oscar Alberto Chavarria
Mobile: +506 814-0247
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