PL/PgSQL中是否存在从游标获取随机元素的方法？

PostgreSQL 原生游标没有提供直接抽取随机元素的内置语法，游标本质是结果集的遍历指针，可根据游标类型选择对应实现方案，两种方案都能实现等概率随机抽取单条记录：

方案1：可滚动游标（默认游标类型）快速抽取 #

PostgreSQL 默认创建的游标均为支持位置跳转的可滚动游标，可直接通过绝对位置定位实现随机抓取，不需要遍历全量数据，执行效率更高：

• 先移动游标到结果集末尾，通过状态变量拿到结果集总记录数
• 将游标指针重置到结果集起始位置
• 生成1到总记录数区间内的随机整数作为目标位置
• 移动指针到随机位置，抓取单条记录即可

DO $$
DECLARE
  -- 替换为你自己的游标查询逻辑
  cur CURSOR FOR SELECT id, content FROM public.test_table;
  total_count INT;
  random_pos INT;
  res RECORD;
BEGIN
  -- 统计结果集总行数
  MOVE FORWARD ALL FROM cur;
  GET DIAGNOSTICS total_count = ROW_COUNT;
  -- 游标回到初始位置
  MOVE ABSOLUTE 0 FROM cur;
  -- 生成随机位置
  random_pos := floor(random() * total_count + 1)::INT;
  -- 抓取随机位置的单条记录
  FETCH ABSOLUTE random_pos FROM cur INTO res;
  
  -- 后续替换为你自己的记录处理逻辑
  RAISE NOTICE '随机抽取记录：id=%, content=%', res.id, res.content;
END $$;

注意：如果你创建游标时显式指定了NO SCROLL参数，游标不支持绝对位置跳转，无法使用该方案。

方案2：不可滚动游标兼容抽取 #

如果是仅支持向前遍历的NO SCROLL游标，可使用蓄水池抽样算法在遍历过程中完成随机抽取，不需要提前统计总行数，内存占用固定为单条记录大小：

• 初始化遍历计数器为0，结果存储变量为空
• 逐行遍历游标记录，每读取一条计数器自增1
• 每次生成1到当前计数器值的随机整数，若随机值为1则将当前记录覆盖写入结果变量
• 遍历完成后，结果变量存储的就是等概率抽取的随机记录

DO $$
DECLARE
  -- 替换为你自己的不可滚动游标定义
  cur NO SCROLL CURSOR FOR SELECT id, content FROM public.test_table;
  current_row RECORD;
  res RECORD;
  counter INT := 0;
  rand_num INT;
BEGIN
  OPEN cur;
  LOOP
    FETCH cur INTO current_row;
    EXIT WHEN NOT FOUND;
    counter := counter + 1;
    -- 单条记录蓄水池抽样逻辑
    rand_num := floor(random() * counter + 1)::INT;
    IF rand_num = 1 THEN
      res = current_row;
    END IF;
  END LOOP;
  CLOSE cur;

  -- 后续替换为你自己的记录处理逻辑
  RAISE NOTICE '随机抽取记录：id=%, content=%', res.id, res.content;
END $$;

若游标已经被部分读取，方案1统计总行数后会重置游标到起始位置，若需要保留原有遍历位置，可在统计行数前先记录当前指针偏移量，抽取完成后再移动回原位置即可。

内容的提问来源于stack exchange，提问作者user18792499