I did already, but this still does not help me write a simple while loop
that goes through all data and stops at the last row.
Joris Dobbelsteen wrote:
> See the postgresql documentation at:
> http://www.postgresql.org/docs/8.2/interactive/plpgsql-cursors.html
>
>
>> -----Original Message-----
>> From: pgadmin-support-owner(at)postgresql(dot)org
>> [mailto:pgadmin-support-owner(at)postgresql(dot)org] On Behalf Of
>> Anton Andreev
>> Sent: dinsdag 24 april 2007 13:45
>> To: pgadmin-support(at)postgresql(dot)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
>
>
>
>