CODEWITHSUNDEEP
postgresqlloopsfor-loopplpgsql
Aijaz Chauhan
Aijaz Chauhan

Reputation: 1659

Looping in PostgreSQL

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.

Upvotes: 1

Views: 1807

Answers (2)

Chris Travers
Chris Travers

Reputation: 26464

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;
$$;

Upvotes: 1

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658707

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

FOR 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;

Details in the excellent manual.

Upvotes: 2

Related Questions