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
Important for you is: "As with SELECT INTO, the special variable FOUND
may be checked to see whether a row was obtained or not."
-- compute
FETCH mycursor INTO interator;
END LOOP;
-- instead of
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.
errors, you will probably have an exception being thrown).
- Joris
http://www.postgresql.org/docs/8.2/interactive/plpgsql-cursors.html
-----Original Message-----
From: 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).From: 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;
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--fetch next from mycursor --gives an error
-- 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-- some computations here
END LOOP;
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 ofEND;
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.
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
