> I am trying to use cursors and I am really frustrated already. Do I > need to install an extension? No, it's all in the documentation: http://www.postgresql.org/docs/current/static/plpgsql-control-structures .html#PLPGSQL-RECORDS-ITERATING > 1. Problem number one is that what ever I use in front of the fetch > command it is not being accepted, it gives a syntax error. If I use a > number ,"all" or "forward" it gives an error again?????????? I want to > do something like the code below: > > CREATE OR REPLACE FUNCTION database_correction() > RETURNS double precision AS > $BODY$ > DECLARE > mycursor CURSOR FOR select distinct(fund_id) from > "NAV_values_bfb_history"; > iterator integer; > > BEGIN > open mycursor; > > FETCH mycursor INTO iterator; > > --fetch next from mycursor --gives an error > > WHILE (FETCH next from mycursor) LOOP > -- some computations here > END LOOP; > > CLOSE mycursor; > END; My suggestion: $BODY$ DECLARE a_row RECORD; BEGIN FOR a_row IN SELECT DISTINCT(fund_id) FROM "NAV_values_bfb_history" LOOP -- some computations here -- access the value as "a_row.fund_id" END LOOP; END; $BODY$ > 2. What is the right way to check that the cursor has ended. In > sqlserver there is a variable "@@fetch_status". I have to make here some > comparison in the while clause, but I am not sure what it should be. I > could not find a single example for cursor in a loop. You do not need that at all, the loop will be left if there are no more results. Yours, Laurenz Albe
