help -- cursor inside a function

jprem wrote:

hello ,
i have a procedure as below.

-------------------------------------------------------------------------------------------------------------

create function retrec(integer) returns integer
as
'declare
uid alias for $1;
begin
declare retrec_cur cursor for select * from jd_user_master where
um_user_id>uid;
if found then
fetch 1 from retrec_cur;
move next from retrec_cur;
return 1;
end if;
end;'
language 'plpgsql';
-------------------------------------------------------------------------------------------------------------------------

PL/pgSQL cannot deal with cursors.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

hello ,
i have a procedure as below.

--------------------------------------------------------------------------

-----------------------------------

create function retrec(integer) returns integer
as
'declare
uid alias for $1;
begin
declare retrec_cur cursor for select * from jd_user_master where

um_user_id>uid;
if found then
fetch 1 from retrec_cur;
move next from retrec_cur;
return 1;
end if;
end;'
language 'plpgsql';
--------------------------------------------------------------------------

-----------------------------------------------

this gets created , but while running it i get the error below,
--------------------------------------------------------------------------

-----------------------------------------

NOTICE: plpgsql: ERROR during compile of retrec near line 5
ERROR: parse error at or near "cursor"
--------------------------------------------------------------------------

------------------------------------------------

why this is so ? can anyone help me out ? thanx in advance.

You may use query loops, such as:

DECLARE
nextrow RECORD;
BEGIN
FOR nextrow IN SELECT * FROM <table> WHERE <condition> ORDER BY <field>
LOOP
...
END LOOP;
END;

See postgresql HTML documentation for further info.

Alex Bolenok.