Re: Loop through records

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