Thread: Function problem

I would appreciate and thank in advance any help to understand the error message I receive when I try to run this function. The function is initially accepted with no errors of syntax.

CREATE OR REPLACE FUNCTION returns_todate(integer)

RETURNS numeric AS

$BODY$

DECLARE

yield_rows investments%rowtype;

counter integer;

sum_returns numeric;

BEGIN

FOR yield_rows IN SELECT * FROM investments

LOOP

sum_returns:=investments.return1+investments.return2+investments.return3+investments.return4+investments.return5+investments.return6+investments.return7 ;

INSERT INTO investments(returns_to_date) VALUES(sum_returns);

END LOOP;

return counter;

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE;

SELECT returns_todate(5);

ERROR: missing FROM-clause entry for table "investments"

_____________________________

Regards
Oscar Chavarria
Mobile:          +506 814-0247

On Mon, 9 Apr 2007, Oscar Chavarria wrote:

> I would appreciate and thank in advance any help to understand the error
> message I receive when I try to run this function. The function is initially
> accepted with no errors of syntax.
>
>
> CREATE OR REPLACE FUNCTION returns_todate(integer)
>
> RETURNS numeric AS
>
> $BODY$
>
> DECLARE
>
> yield_rows investments%rowtype;
>
> counter integer;
>
> sum_returns numeric;
>
> BEGIN
>
> FOR yield_rows IN SELECT * FROM investments
>
> LOOP
>
> sum_returns:=
> investments.return1+investments.return2+investments.return3+
> investments.return4+investments.return5+investments.return6+
> investments.return7
> ;

My guess is that these should probably be yield_rows.<whatever> since
yield_rows is the variable name rather than using the table name.

"Oscar Chavarria" <cyberbuzzard@gmail.com> writes:
> DECLARE
> yield_rows investments%rowtype;
> ...
> FOR yield_rows IN SELECT * FROM investments
> LOOP
> sum_returns:=
>
investments.return1+investments.return2+investments.return3+investments.return4+investments.return5+investments.return6+investments.return7;

I think you want "yield_rows" not "investments" here, no?

> *ERROR: missing FROM-clause entry for table "investments"*

Also, you should have gotten a more specific error message than that.
Wasn't there a "context" line telling you exactly which line of the
function contained the problem?

            regards, tom lane