Re: Loop through records - Mailing list pgsql-novice

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
>