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

-- aux. function (Postgres 14 syntax, see: https://dba.stackexchange.com/a/300512/3684)
CREATE FUNCTION another_func(text, text)
  RETURNS text
  LANGUAGE sql
BEGIN ATOMIC
SELECT $1 || ': ' || $2;
END;

CREATE FUNCTION

-- Postgres 9.1 or later
CREATE FUNCTION f_loop_array_1dim(_arr text[])
  RETURNS SETOF text
  LANGUAGE plpgsql AS
$func$
DECLARE
   m text[];  -- matching array type!
BEGIN
   FOREACH m SLICE 1 IN ARRAY _arr
   LOOP
      RETURN NEXT another_func(m[1], m[2]);
   END LOOP;
END
$func$;

CREATE FUNCTION

-- call with array constructor
SELECT * FROM f_loop_array_1dim(array[['key1','val1'],['key2','val2']]);
-- call with array literal
SELECT * FROM f_loop_array_1dim('{{key1,val1},{key2,val2}}');

f_loop_array_1dim

key1: val1

key2: val2

SELECT 2

f_loop_array_1dim

key1: val1

key2: val2

SELECT 2

-- Postgres 9.0 or older
CREATE FUNCTION f_loop_array_1dim_pg90(_arr text[])
  RETURNS SETOF text
  LANGUAGE plpgsql AS
$func$
BEGIN
   FOR i IN array_lower(_arr, 1) .. array_upper(_arr, 1)
   LOOP
      RETURN NEXT another_func(_arr[i][1], _arr[i][2]);
   END LOOP;
END
$func$;

CREATE FUNCTION

-- call with array constructor
SELECT * FROM f_loop_array_1dim_pg90(array[['key1','val1'],['key2','val2']]);
-- call with array literal
SELECT * FROM f_loop_array_1dim_pg90('{{key1,val1},{key2,val2}}');

f_loop_array_1dim_pg90

key1: val1

key2: val2

SELECT 2

f_loop_array_1dim_pg90

key1: val1

key2: val2

SELECT 2