Hi all,
Is there a way to move a cursor in plpgsql in the same way as in
regular sql? The function below would like to move the cursor back to
the start each time the cursor runs out of rows, creating pairs of
integers that are randomly put together.
The "motivation" for this is to randomly assign parts of a
shakespearian play (N=25) to not enough actors (N=6), giving each
actor several parts. (To be truly fair, I would have to weight by
number of lines, but that is for version 2... ) If there is a more
graceful way to solve the problem, I am interested, but I would like
to know about the MOVE issue in any case.
CREATE TYPE int_pair as (i int, j int);
CREATE OR REPLACE FUNCTION ASSIGN_RAND (tab1_nm text, tab2_nm text)
RETURNS SETOF int_pair AS $PROC$
DECLARE
curs1 REFCURSOR;
tab1_id int;
tab2_id int;
id_pair int_pair;
BEGIN
OPEN curs1 FOR EXECUTE 'select id from (select id, random() as r
from ' || quote_ident($2) || ' order by r) x';
FOR tab1_id IN EXECUTE 'select id from (select id, random() as r
from ' || quote_ident($1) || ' order by r) x' LOOP
id_pair.i := tab1_id;
fetch curs1 into id_pair.j;
IF NOT FOUND THEN
MOVE FIRST IN curs1; -- XXX gives error!
fetch curs1 into id_pair.j;
END IF;
RETURN NEXT id_pair;
END LOOP;
END;
$PROC$ language plpgsql;
