Thread: looping through records...

I'm trying to create a function that loops through records returned from a
query. This is what I came up with but it doesn't work. Can anyone tell me
what I'm doing wrong?

CREATE FUNCTION test_loop(INT4, VARCHAR)
    RETURNS INT4
    AS 'DECLARE
        int_key ALIAS FOR $1;
        var_test VARCHAR;

    BEGIN
        var_test := $2;
        FOR row IN SELECT * FROM companies LOOP
            UPDATE companies SET name = var_test;
        END LOOP;
        RETURN int_key;
    END;'
LANGUAGE 'plpgsql';

Here's how I call the test loop and the error I get:

db=# SELECT test_loop(1, 'testing');
ERROR: parse error at or near ";"
db=#

Thanks

"Bob Whitehouse" <bwhitehouse@geeknest.com> writes:
> Here's how I call the test loop and the error I get:

> db=# SELECT test_loop(1, 'testing');
> ERROR: parse error at or near ";"

I'm not sure why this comes out as a parse error rather than something
more helpful, but the problem is you didn't declare the record variable
that's going to receive the rows.  Try adding the declaration "row RECORD;"

            regards, tom lane

Don't you need to declare "row"? like

    row record;

or

    row companies%ROWTYPE

But then again, that might not be the reason.

HTH,

Albert.


On Wed, Apr 18, 2001 at 04:09:47PM -0400, Bob Whitehouse wrote:
> I'm trying to create a function that loops through records returned from a
> query. This is what I came up with but it doesn't work. Can anyone tell me
> what I'm doing wrong?
>
> CREATE FUNCTION test_loop(INT4, VARCHAR)
>     RETURNS INT4
>     AS 'DECLARE
>         int_key ALIAS FOR $1;
>         var_test VARCHAR;
>
>     BEGIN
>         var_test := $2;
>         FOR row IN SELECT * FROM companies LOOP
>             UPDATE companies SET name = var_test;
>         END LOOP;
>         RETURN int_key;
>     END;'
> LANGUAGE 'plpgsql';
>
> Here's how I call the test loop and the error I get:
>
> db=# SELECT test_loop(1, 'testing');
> ERROR: parse error at or near ";"
> db=#
>
> Thanks
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
>