Re: Loop through records

Emiliano Amilcarelli <amiemi(at)tin(dot)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°