Postgres 14

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?.

-- Postgres 9.1 or later
CREATE FUNCTION f_loop_array_elem(_arr int[])
  RETURNS SETOF int
  LANGUAGE plpgsql AS
$func$
DECLARE
   _elem int;                       -- matching element type!
BEGIN
   FOREACH _elem IN ARRAY _arr
   LOOP
      RETURN NEXT _elem;
   END LOOP;
END
$func$;

CREATE FUNCTION


SELECT f_loop_array_elem('{1,2,3}');  -- call with array literal


f_loop_array_elem


1

2

3



SELECT 3


-- Postgres 9.0 or older
CREATE FUNCTION f_loop_array_elem_pg90(_arr int[])
  RETURNS SETOF int
  LANGUAGE plpgsql AS
$func$
BEGIN
   FOR i IN 1 .. array_upper(_arr, 1)  -- "i" is the index
   LOOP
      RETURN NEXT _arr[i];             -- single quotes
   END LOOP;
END
$func$;

CREATE FUNCTION


SELECT f_loop_array_elem_pg90(array[1,2,3]);  -- call with array constructor


f_loop_array_elem_pg90


1

2

3



SELECT 3