MOVE cursor in plpgsql?

From: "Webb Sprague" <webb(dot)sprague(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: MOVE cursor in plpgsql?
Date: 2007-02-10 18:43:11
Message-ID: b11ea23c0702101043r6fe9cf68s984308ccdd842677@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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;

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Anton Melser 2007-02-10 19:15:31 Re: drop table if exists mytable;
Previous Message Tom Lane 2007-02-10 17:34:20 Re: Adding TEXT columns tanks performance?