Re: Loop through records

Emiliano Amilcarelli <amiemi@tin.it> schrieb:

> tatus: O
> Content-Length: 1264
> Lines: 47
>
> Hi all,
> I'm a PostgreSQL novice (obviously)...
> I' trying to write some test functions to iterate over recordsets...
>
> this is one:
>
> CREATE OR REPLACE FUNCTION "public"."new_allarma" (soglia name) RETURNS
> text AS
> $body$
> DECLARE
>     rec RECORD;
>     agent text;
> BEGIN
>
>      FOR rec in SELECT * from "ERRORS" where "MAX_ERRORS" > = soglia limit
> 3
>     LOOP
>         RAISE NOTICE '--> RECORD  --> ';
>     END LOOP;
>     RAISE NOTICE 'Complete';
>     RETURN 'OK';
> END;
> $body$
> LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
>
> It doesn't even print the string --> RECORD  --> as I supposed it to do...
>
> but raises the error:ERROR:  record "rec" is not assigned yet
> DETAIL:  The tuple structure of a not-yet-assigned record is indeterminate.
> CONTEXT:  PL/pgSQL function "new_allarma2" line 6 at for over select rows

My guess:
The problem is, you should create a string with your sql-query and
execute this string, because you have parameters in your query.

I will give you a example:

create or replace function foo_test(int) returns text as $$
declare rec record;
begin
    for rec in execute 'select * from foo where id = ' || $1 || ';' loop
        raise notice '--> RECORD  --> ';
    end loop;
    return 'ready';
end;
$$ language plpgsql;


test=> select foo_test(1);
HINWEIS:  --> RECORD  -->
 foo_test
----------
 ready
(1 Zeile)




HTH, Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°