Skip site navigation (1) Skip section navigation (2)

Re: questions about cursors

From: "Joris Dobbelsteen" <Joris(at)familiedobbelsteen(dot)nl>
To: "Anton Andreev" <fn30762(at)fmi(dot)uni-sofia(dot)bg>,<pgadmin-support(at)postgresql(dot)org>
Subject: Re: questions about cursors
Date: 2007-04-24 12:49:14
Message-ID: 73427AD314CC364C8DF0FFF9C4D693FF037AC8@nehemiah.joris2k.local (view raw or flat)
Thread:
Lists: pgadmin-supportpgsql-general
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


In response to

Responses

pgadmin-support by date

Next:From: Anton AndreevDate: 2007-04-24 12:58:25
Subject: Re: [pgadmin-support] questions about cursors
Previous:From: Anton AndreevDate: 2007-04-24 11:45:10
Subject: questions about cursors

pgsql-general by date

Next:From: Jeff LanzarottaDate: 2007-04-24 12:53:02
Subject: How does server handle clients that disconnect ungracefully?
Previous:From: Wilkinson, JimDate: 2007-04-24 12:45:13
Subject: Re: hi

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group