questions about cursors

See the postgresql documentation at:
http://www.postgresql.org/docs/8.2/interactive/plpgsql-cursors.html

-----Original Message-----
From: pgadmin-support-owner@postgresql.org
[mailto:pgadmin-support-owner@postgresql.org] 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

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:

Anton,

Wrong mailing list. You should send this type of query to
pgsql-general@postgresql.org in the future. The documentation is confusing,
though. Try This:
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;

while found Loop
-- some computations here
FETCH mycursor INTO iterator;
END LOOP;

CLOSE mycursor;
END;

Thank You
Sim Zacks
IT Manager
CompuLab
04-829-0145 - Office
04-832-5251 - Fax

________________________________________________________________________________

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;

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;

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.

I will greatly appreciate any help, pgsql is my database of choice.

Cheers,
Anton

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly