FAQ

[PostgreSQL] Re: [pgadmin-support] questions about cursors

Anton Andreev
Apr 24, 2007 at 12:58 pm
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@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


reply

Search Discussions

1 response

  • Sim Zacks at Apr 26, 2007 at 12:43 pm
    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

Related Discussions

Discussion Navigation
viewthread | post

2 users in discussion

Anton Andreev: 1 post Sim Zacks: 1 post