Is there a way to get a random element out of a cursor in PL/PgSQL?-CodePudding

I'm studying examples in PostgreSQL, and I can't find a way to get a single random element when dealing with a cursor.

I have seen many articles and documentation, but there seems to be no way to do it.The output would be a random number from the cursor, that is, a random element from the cursor.

CodePudding user response：

Don't use a cursor. Instead, use a query that returns a single random value and assign it to a variable:

num1 := (select num from client order by random() limit 1);

CodePudding user response：

A test case using a local table:

do $$
DECLARE 
    rec record;
    num1 integer;
    ct integer;
    cur1 CURSOR FOR
        SELECT p_item_no FROM plant1;
    fetch_ct integer;
BEGIN
    SELECT INTO ct count(*) from plant1;
    SELECT INTO fetch_ct (random() * ct)::integer;
    open cur1;
    fetch relative fetch_ct from cur1 into rec;
    num1 = rec.p_item_no;
    Raise Notice 'Num: %', num1;

close cur1;

end $$ 
Language 'plpgsql';

NOTICE:  Num: 2742
DO

--Running it again

NOTICE:  Num: 3083
DO
--And again
NOTICE:  Num: 4848
DO