Re: Loop through records

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(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
>