Thread: Loop through records

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


Now i ask: how can I iterate through a recordset and print something,
suppose the entire record ( or better its fields) ?

Thanks in advance.

Best Regards,

Emil.

am  11.11.2005, um 16:43:29 +0100 mailte Emiliano Amilcarelli folgendes:
>
> 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

You can't run a sql with variables in this way. I show you a example:

,----[  example  ]
| CREATE OR REPLACE FUNCTION loop (val varchar) returns text as $$
| declare
|         rec     RECORD;
|         sql     varchar;
| begin
|         sql := 'select * from foo where val = \'' || $1 || '\';';
|         raise notice 'sql %',sql;
|         for rec in execute sql loop
|                 RAISE NOTICE '--> RECORD  --> ';
|         end loop;
|         RAISE NOTICE 'Complete';
|         RETURN 'OK';
| end;
| $$
| LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
`----


and a test:

test=# select * from foo;
 id | val
----+-----
  1 | a
  2 | b
  3 | c
(3 Zeilen)


test=# select * from loop('a');
HINWEIS:  sql select * from foo where val = 'a';
HINWEIS:  --> RECORD  -->
HINWEIS:  Complete
 loop
------
 OK
(1 Zeile)


The point is, you should create a string that contains your sql, and
this string can you execute.

Read the docu for more details:
http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN


HTH, Andreas
--
Andreas Kretschmer    (Kontakt: siehe Header)
Heynitz:  035242/47212,      D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===    Schollglas Unternehmensgruppe    ===