Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

-- simple function
CREATE OR REPLACE FUNCTION f_array_move_element_simple(_arr bigint[], _elem bigint, _pos int)
  RETURNS bigint[] AS
$func$
   SELECT a1[:_pos-1] || _elem || a1[_pos:]
   FROM array_remove(_arr, _elem) a1;
$func$  LANGUAGE sql IMMUTABLE;


-- proper function
CREATE OR REPLACE 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
      WHEN true THEN
         RETURN _arr;
      WHEN false THEN
         _arr := array_remove(_arr, _elem);
      ELSE                    --
         RAISE EXCEPTION 'Element % not contained in array %!', _elem, _arr;
   END CASE;

   RETURN _arr[:_pos-1] || _elem || _arr[_pos:];
END
$func$  LANGUAGE plpgsql;
SELECT elem, pos, f_array_move_element_simple('{1,2,3,4}'::bigint[], elem, pos)
FROM generate_series(bigint '1',bigint '4') elem
   , generate_series(1, 4) pos
ORDER  BY 1,2;


elem
pos
f_array_move_element_simple


1
1
{1,2,3,4}

1
2
{2,1,3,4}

1
3
{2,3,1,4}

1
4
{2,3,4,1}

2
1
{2,1,3,4}

2
2
{1,2,3,4}

2
3
{1,3,2,4}

2
4
{1,3,4,2}

3
1
{3,1,2,4}

3
2
{1,3,2,4}

3
3
{1,2,3,4}

3
4
{1,2,4,3}

4
1
{4,1,2,3}

4
2
{1,4,2,3}

4
3
{1,2,4,3}

4
4
{1,2,3,4}


SELECT elem, pos, f_array_move_element('{1,2,3,4}'::bigint[], elem, pos)
FROM generate_series(bigint '1',bigint '4') elem
   , generate_series(1, 4) pos
ORDER  BY 1,2;


elem
pos
f_array_move_element


1
1
{1,2,3,4}

1
2
{2,1,3,4}

1
3
{2,3,1,4}

1
4
{2,3,4,1}

2
1
{2,1,3,4}

2
2
{1,2,3,4}

2
3
{1,3,2,4}

2
4
{1,3,4,2}

3
1
{3,1,2,4}

3
2
{1,3,2,4}

3
3
{1,2,3,4}

3
4
{1,2,4,3}

4
1
{4,1,2,3}

4
2
{1,4,2,3}

4
3
{1,2,4,3}

4
4
{1,2,3,4}


SELECT f_array_move_element_simple('{1,2,3,4}'::bigint[], bigint '666', 3);


f_array_move_element_simple


{1,2,666,3,4}


SELECT f_array_move_element('{1,2,3,4}'::bigint[], bigint '666', 3);
ERROR:  Element 666 not contained in array {1,2,3,4}!
CONTEXT:  PL/pgSQL function f_array_move_element(anyarray,anyelement,integer) line 15 at RAISE


SELECT f_array_move_element_simple('{1,2,3,4}'::bigint[], bigint '2', 0);


f_array_move_element_simple


{2,1,3,4}


SELECT f_array_move_element('{1,2,3,4}'::bigint[], bigint '2', 0);
ERROR:  Target position 0 not allowed. Must be a positive integer.
CONTEXT:  PL/pgSQL function f_array_move_element(anyarray,anyelement,integer) line 4 at RAISE


SELECT f_array_move_element_simple('{1,2,3,4}'::bigint[], bigint '2', NULL);


f_array_move_element_simple


{2}


SELECT f_array_move_element('{1,2,3,4}'::bigint[], bigint '2', NULL);
ERROR:  Target position <NULL> not allowed. Must be a positive integer.
CONTEXT:  PL/pgSQL function f_array_move_element(anyarray,anyelement,integer) line 4 at RAISE


SELECT f_array_move_element_simple('{1,2,3,4}'::bigint[], bigint '2', 7);


f_array_move_element_simple


{1,3,4,2}


SELECT f_array_move_element('{1,2,3,4}'::bigint[], bigint '2', 7);
ERROR:  Target position 7 not allowed. Cannot be greater than length of array.
CONTEXT:  PL/pgSQL function f_array_move_element(anyarray,anyelement,integer) line 6 at RAISE

elem	pos	f_array_move_element_simple
1	1	{1,2,3,4}
1	2	{2,1,3,4}
1	3	{2,3,1,4}
1	4	{2,3,4,1}
2	1	{2,1,3,4}
2	2	{1,2,3,4}
2	3	{1,3,2,4}
2	4	{1,3,4,2}
3	1	{3,1,2,4}
3	2	{1,3,2,4}
3	3	{1,2,3,4}
3	4	{1,2,4,3}
4	1	{4,1,2,3}
4	2	{1,4,2,3}
4	3	{1,2,4,3}
4	4	{1,2,3,4}