Re: iterate over refcursor

["Sim Zacks" <sim@xxxxxxxxxxxxxx>]

Title: iterate over refcursor

Will,

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@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx]De la part de Sim Zacks
Envoyé : mardi 8 mars 2005 12:53
À : pgsql-general@xxxxxxxxxxxxxx
Objet : Re: 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.ferreira@xxxxxxxxxx> wrote in message 1904E3EB39448246A7ECB76DF34A70B00143B46A@TOCOMEXC03">news:1904E3EB39448246A7ECB76DF34A70B00143B46A@TOCOMEXC03...
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