See the postgresql documentation at: http://www.postgresql.org/docs/8.2/interactive/plpgsql-cursors.html
>-----Original Message----- >From: [EMAIL PROTECTED] >[mailto:[EMAIL PROTECTED] On Behalf Of >Anton Andreev >Sent: dinsdag 24 april 2007 13:45 >To: pgadmin-support@postgresql.org >Subject: [pgadmin-support] questions about cursors > >Hi, > >I am trying to use cursors and I am really frustrated already. >Do I need to install an extension? > >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; Please see chapter 37.8.3.1 "FETCH" (in the 8.2 documentation). Important for you is: "As with SELECT INTO, the special variable FOUND may be checked to see whether a row was obtained or not." >FETCH mycursor INTO iterator; > >--fetch next from mycursor --gives an error > WHILE (FOUND) LOOP -- compute FETCH mycursor INTO interator; END LOOP; -- instead of >WHILE (FETCH next from mycursor) LOOP > -- some computations here >END LOOP; I believe this should work. I've never really used cursors before, as I can usually do it with regular SQL statements (which are usually faster). I should also advise that you can create your own aggregate (see CREATE AGGREGATE in the documentation), which might, or might not, be easier for your purposes. >CLOSE mycursor; >END; > >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. When FOUND evaluates to false, you should have completed. (In case of errors, you will probably have an exception being thrown). >I will greatly appreciate any help, pgsql is my database of choice. Hope this helps... - Joris ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
