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 2: Don't 'kill -9' the postmaster

Anton Andreev wrote:
> Hi,
>
> I am trying to use cursors and I am really frustrated already. Do I
> need to install an extension?

No, you just need to have a look at the docs.
http://www.postgresql.org/docs/8.2/s...ORDS-ITERATING

> 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$
> [...]

Try something like this:

CREATE OR REPLACE FUNCTION database_correction()
RETURNS double precision LANGUAGE plpgsql AS
$body$
DECLARE
fund INTEGER;
BEGIN
FOR fund IN SELECT DISTINCT(fund_id) FROM "NAV_values_bfb_history" LOOP
RAISE NOTICE $$ foo bar $$;
-- some computations here
END LOOP;
RETURN 42.0;
END;
$body$;

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

> I am trying to use cursors and I am really frustrated already. Do I
> need to install an extension?

No, it's all in the documentation:
http://www.postgresql.org/docs/curre...rol-structures
..html#PLPGSQL-RECORDS-ITERATING

> 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;

My suggestion:

$BODY$
DECLARE
a_row RECORD;
BEGIN
FOR a_row IN SELECT DISTINCT(fund_id) FROM "NAV_values_bfb_history"
LOOP
-- some computations here
-- access the value as "a_row.fund_id"
END LOOP;
END;
$BODY$

> 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.

You do not need that at all, the loop will be left if there are no more
results.

Yours,
Laurenz Albe

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq