Postgres Pro
Downloads
Home   >   mailing lists

Thread: iterate over refcursor

iterate over refcursor

From
"FERREIRA William (COFRAMI)"
Date:

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

Re: iterate over refcursor

From
"Sim Zacks"
Date:
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
 

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

Re: iterate over refcursor

From
"Sim Zacks"
Date:
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@postgresql.org [mailto:pgsql-general-owner@postgresql.org]De la part de Sim Zacks
Envoyé : mardi 8 mars 2005 12:53
À : pgsql-general@postgresql.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.ferreira@airbus.com> wrote in message 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