Обсуждение: 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.

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°

I managed to get results printed out from the function, but now i cannot
If..THEN working inside the same function.
To better show what strange behaviour i'm getting i explain my problem
from the beginning

Two simple queries correctly showing results:

First :  SELECT * from "ERRORI_REMOTI_PER_GIORNO_E_ORA" where
"PROVE_FALLITE" >= 30;

IMISDNMilanoRozzano | ERRORE REMOTO | 30/10/05 02      |            42
  ........(snip)....
(5 righe)

Second :   SELECT * from "ERRORI_REMOTI_PER_GIORNO_E_ORA" where
"PROVE_FALLITE" >= 90;

(0 righe)

The following function works fine, displaying fine results:

CREATE OR REPLACE FUNCTION "public"."allarma_accesso" (soglia integer)
RETURNS text AS
$body$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN SELECT * from "ERRORI_REMOTI_PER_GIORNO_E_ORA" where
"PROVE_FALLITE" >=soglia::INT
    LOOP
    RAISE NOTICE '--> Ag % Err % Data % N° Err %', rec."AGENT",
rec."ERRORE",rec."DATA_ORA_ACCESSO",rec."PROVE_FALLITE";
    END LOOP;
    RETURN 'Operazione Completata';

END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

select allarma_accesso('30');
NOTICE:  --> Ag IMISDNMilanoRozzano Err ERRORE REMOTO Data 30/10/05 02
N° Err 42
NOTICE:  --> Ag IMRTGTorinoArdigo Err ERRORE REMOTO Data 30/10/05 02 N°
Err 36
NOTICE:  --> Ag IMRTGFirenzeBruni Err ERRORE REMOTO Data 30/10/05 02 N°
Err 34
NOTICE:  --> Ag IMRTGMilanoRozzano Err ERRORE REMOTO Data 30/10/05 02 N°
Err 34
NOTICE:  --> Ag IMRTGRomaOriolo Err ERRORE LOCALE Data 30/10/05 02 N° Err 31
    allarma_accesso
-----------------------
 Operazione Completata
(1 riga)

 select allarma_accesso('90');
    allarma_accesso
-----------------------
 Operazione Completata
(1 riga)

If I try to use IF..THEN inside the function I get strange ( to me)
results:
CREATE OR REPLACE FUNCTION "public"."allarma_accesso" (soglia integer)
RETURNS text AS
$body$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN SELECT * from "ERRORI_REMOTI_PER_GIORNO_E_ORA" where
"PROVE_FALLITE" >=soglia::INT
    LOOP
    IF FOUND THEN
    RAISE NOTICE '--> Ag % Err % Data % N° Err %', rec."AGENT",
rec."ERRORE",rec."DATA_ORA_ACCESSO",rec."PROVE_FALLITE";
    ELSE
    RAISE NOTICE 'No Records found';
    END IF;
    END LOOP;
    RETURN 'Operazione Completata';

END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

*
  *select allarma_accesso('30');
NOTICE:  No Records found
NOTICE:  No Records found
NOTICE:  No Records found
NOTICE:  No Records found
NOTICE:  No Records found
    allarma_accesso
-----------------------
 Operazione Completata
(1 riga)

 select allarma_accesso('90');
    allarma_accesso
-----------------------
 Operazione Completata
(1 riga)


Where is the wrong thing i'm doing?

Thks....
Emil



Andreas Kretschmer ha scritto:
> 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
>

Emiliano Amilcarelli <amiemi@tin.it> writes:
> I managed to get results printed out from the function,

What was the problem exactly?  I couldn't see a reason for that error
message --- the only thing I could find wrong in your function was
spelling ">=" with a space in the middle.  After fixing that and making
a simple ERRORS table, it worked fine.

> but now i cannot If..THEN working inside the same function.

It looks like you're expecting FOUND to be set inside the loop, which it
is not.  Seems rather pointless --- if you're inside the loop body at
all, then it found a record, no?  A FOR-loop sets FOUND *when it exits*,
to tell code after the loop whether any iterations were performed or
not.

            regards, tom lane