arrays - 将 PostgreSQL bigint 数组唯一值移动到另一个索引

Question

如何将数组 bigint 值从一个索引移动到另一个索引？例如，我有一个 bigint unique 值的数组 ARRAY[1, 2, 3, 4] 并且想将值 1 移动到索引 3，因此最终结果将是数组[2, 3, 1, 4]

假设:

1. 数组中由值标识的元素。

2. 保证元素的唯一性。

3. 任何元素都可以移动到任何地方。

4. 空值不涉及任何一方。

5. 如果不是，则该值包含在数组中，我们有 2 个选项。首先是什么都不做，其次是通过异常机制处理这个异常。只是因为一些BUG才会出现的极端情况

6. 数组是一维的。

Answer 1

一般假设:

• 数组元素是UNIQUE NOT NULL。
• 数组是具有标准下标的一维数组 (1..N)。看:
  • Normalize array subscripts for 1-dimensional array so they start with 1

简单的解决方案

CREATE FUNCTION f_array_move_element_simple(_arr bigint[], _elem bigint, _pos int)
  RETURNS bigint[] LANGUAGE sql IMMUTABLE AS
'SELECT a1[:_pos-1] || _elem || a1[_pos:] FROM array_remove(_arr, _elem) a1'

一切都很好，只要:

• 给定的元素实际包含在数组中。
• 给定的位置在 1 和数组的长度之间。

正确的解决方案

CREATE FUNCTION f_array_move_element(_arr ANYARRAY, _elem ANYELEMENT, _pos int)
  RETURNS ANYARRAY AS
$func$
BEGIN
   IF _pos IS NULL OR _pos < 1 THEN
      RAISE EXCEPTION 'Target position % not allowed. Must be a positive integer.', _pos;
   ELSIF _pos > array_length(_arr, 1) THEN
      RAISE EXCEPTION 'Target position % not allowed. Cannot be greater than length of array.', _pos;
   END IF;

   CASE array_position(_arr, _elem) = _pos  -- already in position, return org
      WHEN true THEN
         RETURN _arr;
      WHEN false THEN                       -- remove element
         _arr := array_remove(_arr, _elem);
      ELSE                                  -- element not found
         RAISE EXCEPTION 'Element % not contained in array %!', _elem, _arr;
   END CASE;

   RETURN _arr[:_pos-1] || _elem || _arr[_pos:];
END
$func$  LANGUAGE plpgsql IMMUTABLE;

如果违反了简单函数的任何附加假设，则会引发异常。

“正确的”函数使用 polymorphic types并适用于任何数据类型，而不仅仅是 bigint - 只要数组和元素类型匹配。

db<> fiddle here