I am new to PostgreSQL and have a simple task with a looping structure.
Trying to print 0 to 10. Here is my function:
CREATE OR REPLACE FUNCTION LOOPING()
RETURNS TABLE(asd text) AS
$BODY$declare
i integer;
Begin
i:=0;
WHILE i > 10 LOOP
select i;
i:=(i+1);
END LOOP;
end;$BODY$
LANGUAGE plpgsql IMMUTABLE
COST 100
ROWS 1000;
ALTER FUNCTION LOOPING()
OWNER TO postgres;
I have tried with while loop. If anybody can do this task with for loop it will be very helpful.
You have several hick-ups in there. One working way (of many):
CREATE OR REPLACE FUNCTION f_loop()
RETURNS TABLE(asd int) AS
$BODY$
BEGIN
asd := 0;
WHILE asd < 11 LOOP
RETURN NEXT;
asd := asd + 1;
END LOOP;
END
$BODY$ LANGUAGE plpgsql IMMUTABLE
Call:
SELECT * FROM f_loop();
CREATE OR REPLACE FUNCTION f_loop()
RETURNS TABLE(asd int) AS
$BODY$
BEGIN
FOR i IN 0..10 LOOP
asd := i;
RETURN NEXT;
END LOOP;
END
$BODY$ LANGUAGE plpgsql IMMUTABLE;
Or, for this simple case:
CREATE OR REPLACE FUNCTION f_loop()
RETURNS SETOF int AS
$BODY$
BEGIN
FOR i IN 0..10 LOOP
RETURN NEXT i;
END LOOP;
END
$BODY$ LANGUAGE plpgsql IMMUTABLE;
i is an integer. You can cast to text. RETURN NEXT i::text;.Your problem as far as I can see is that you have to return the value properly and RETURN NEXT in my experiments (on Pg 9.1) did not work as I expected it to.
I tested this and it was working:
create or replace function loop() returns table (i int)
language plpgsql as
$$
declare i_array int[];
begin
for i in 0 .. 10 loop
i_array := i_array || i;
end loop;
return query select unnest(i_array);
end;
$$;
RETURN NEXT i with RETURNS TABLE (...) is all. I included another examples to demonstrate both viable routes.