Обсуждение: MOVE cursor in plpgsql?

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;

> 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.

Wouldn't it be easier to list the parts in a random order (simply ORDER
BY RANDOM()) and then use modulo by number of actors (but there's no
ROWNUM so a loop is needed anyway). Something like

i := 0;

FOR x IN SELECT .... parts ... LOOP
    actor := mod(i,number_of_actors);
    i := i + 1;
END LOOP;

This should be fair enough and does not need moving the cursors in
various areas.

t.v.

> > Is there a way to move a cursor in plpgsql in the same way as in
> > regular sql?  ...
>
> Wouldn't it be easier to list the parts in a random order (simply ORDER
> BY RANDOM()) and then use modulo by number of actors (but there's no
> ROWNUM so a loop is needed anyway). Something like...

I think you are right Thomas about the modulo, and that might be even
better stylistically (more obvious to the reader) -- thanks!

I still wonder if MOVing cursor in plpgsql is impossible, possible, or
available soon?

Thanks again,
W