Re: iterate over refcursor

iterate over refcursorWill,
In the future, please respond to the list and not to me personally.

your refcursor is essentially a list of records. the variable list depends on your cursor.
your cursor contains records of select * from ...
You want to use a loop because you want to process fields from each record in the result set.
Lets say your resultset included partID int, Partname varchar, PartType varchar
and you wanted to access all 3 of them.
You could also declare a rowtype and select into that and then reference each field via the dot operator.

You would do a
declare
v_partid int;
v_partname varchar;
v_parttype varchar;
begin
fetch childcursor into v_partid,v_partname,v_parttype;
while Found LOOP
--Do stuff;
--Do More Stuff;
--Finish Doing Stuff;
fetch childcursor into v_partid,v_partname,v_parttype;
END LOOP;

You do not declare found, it is internal.
You also might want to try reading the Manual with reference to both cursors and loops. You need to read both of them to gain a usable understanding.

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

what's the type of the variable variablelist ?
end does i need to declare Found ?

i try your code and i got this error :

ERROR: syntax error at or near "variablelist" at character 484
-----Message d'origine-----
De : pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org]De la part de Sim Zacks
Envoyé : mardi 8 mars 2005 12:53
À : pgsql-general(at)postgresql(dot)org
Objet : Re: [GENERAL] iterate over refcursor

try fetch as in:

fetch childcursor into variablelist;
while Found LOOP
--Do stuff;
--Do More Stuff;
--Finish Doing Stuff;
fetch childcursor into variablelist;
END LOOP;

found is set to false if there is nothing to fetch

""FERREIRA William (COFRAMI)"" <william(dot)ferreira(at)airbus(dot)com> wrote in message news:1904E3EB39448246A7ECB76DF34A70B00143B46A(at)TOCOMEXC03(dot)(dot)(dot)
hi
I got 2 functions write in pl/pgsql.
In the first function, i create a cursor and i need to use it into an other function
so the parameter is a refcursor.
the code :
1st function :
DECLARE
childCursor CURSOR FOR select * from ...
BEGIN
SORT_CHILDREN(childCursor);
END;
2nd function :
CREATE OR REPLACE FUNCTION SORT_CHILDREN(refCursor) RETURNS int4[] AS
$$
DECLARE
childCursor ALIAS FOR $1;
childRecord adoc.xdb_child%ROWTYPE;
BEGIN
FOR childRecord IN childCursor LOOP
...
END LOOP;
RETURN ...;
END;
$$ LANGUAGE plpgsql;
But it doesn't work.....
My problem is : how to iterate over a refcursor in a function ?
thanks in advance
Will