如何在PostgreSQL中基于索引从数组列表随机选取数组？

在PostgreSQL存储过程里，需要为变量随机赋值，目标是从一组预设的数组中随机选取一个有效数组，但现有尝试遇到了格式转换或取值的问题，以下是测试代码、执行结果及问题分析，最后给出可行解决方案。

测试代码 #

DO 
$EG$ 
DECLARE
  try1 text := (SELECT (ARRAY['item10','item20','item30'])[1]);
  try2 text := (SELECT (ARRAY['item10','item20','item30'])[floor(random() * 3 + 1)]);
  try3 text := (SELECT (ARRAY['item10,item11','item20,item21','item30,item31'])[floor(random() * 3 + 1)]);
  try4 text := (SELECT (ARRAY['''item10'',''item11''','''item20'',''item21''','''item30'',''item31'''])[floor(random() * 3 + 1)]);
  try5 text := (SELECT (ARRAY[ARRAY['item10','item11'],ARRAY['item20','item21'],ARRAY['item30','item31']])[floor(random() * 3 + 1)]);
BEGIN
  -- 1.1 & 2.1 基础索引取值示例
  RAISE INFO 'try1.1 (use index).......: %', try1;
  RAISE INFO 'try2.1 (use random index): %', try2;
  -- 3.1 至 5.1 为尝试的数组取值方式
  RAISE INFO 'try3.1 (string_to_array).: %', string_to_array(try3,',');
  -- RAISE INFO 'try3.2 (cast to text[])..: %', try3::text[]; -- 执行失败
  RAISE INFO 'try4.1 (string_to_array).: %', string_to_array(try4,',');
  -- RAISE INFO 'try4.2 (cast to text[])..: %', try4::text[]; -- 执行失败
  RAISE INFO 'try5.1 (array of arrays).: %', try5;
END
$EG$;

执行结果 #

INFO: try1.1 (use index).......: item10
INFO: try2.1 (use random index): item20
INFO: try3.1 (string_to_array).: {item10,item11}
INFO: try4.1 (string_to_array).: {'item20','item21'}
INFO: try5.1 (array of arrays).:

问题分析 #

• try3.2（已注释）执行失败，报错：malformed array literal: "item10,item11"，原因是字符串未符合PostgreSQL数组的字面量格式（缺少大括号包裹）。
• try4.1的结果接近预期，但与表中现有数组格式{"item20","item21"}不符，存在多余的单引号。
• try4.2（已注释）执行失败，报错：malformed array literal: "'item20','item21'"，因为字符串中的单引号未正确转义，不符合数组字面量规范。
• try5.1尝试使用数组的数组，但返回NULL，原因是变量try5被声明为text类型，无法直接存储数组类型的值，导致隐式转换失败。

可行解决方案 #

方法1：使用数组类型变量直接存储

将变量类型声明为text[]，直接从数组的数组中随机选取元素，这是最直接的方式：

DO 
$EG$ 
DECLARE
  try6 text[] := (SELECT (ARRAY[ARRAY['item10','item11'],ARRAY['item20','item21'],ARRAY['item30','item31']])[floor(random() * 3 + 1)::int]);
BEGIN
  RAISE INFO 'try6.1 (array of arrays, correct type): %', try6;
END
$EG$;

执行后输出类似：INFO: try6.1 (array of arrays, correct type): {item20,item21}，完全匹配表中现有数组格式。

方法2：从合法数组字符串转换

如果必须使用字符串变量中转，可以直接选取符合PostgreSQL数组格式的字符串，再转换为数组：

DO 
$EG$ 
DECLARE
  try7 text := (SELECT ('{"item10","item11"}','{"item20","item21"}','{"item30","item31"}')[floor(random() * 3 + 1)::int]);
BEGIN
  RAISE INFO 'try7.1 (cast from valid array string): %', try7::text[];
END
$EG$;

选取的字符串本身是合法的数组字面量，可直接转换为text[]类型。

方法3：优化string_to_array的使用

针对try3的场景，string_to_array返回的结果本身就是标准text[]类型，直接使用即可匹配表中格式：

DO 
$EG$ 
DECLARE
  try8 text := (SELECT (ARRAY['item10,item11','item20,item21','item30,item31'])[floor(random() * 3 + 1)::int]);
  arr_result text[];
BEGIN
  arr_result := string_to_array(try8, ',');
  RAISE INFO 'try8.1 (string_to_array result): %', arr_result;
END
$EG$;

输出格式为{item20,item21}，与表中数组格式一致。

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