Postgres 13

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

CREATE TYPE number_with_time AS(
  _num float
, _ts  timestamp
);

-- sql
CREATE OR REPLACE FUNCTION myfunc_sql(_arr_txt text)
  RETURNS integer       -- example
  LANGUAGE sql AS
$func$
   SELECT sum(_num)::int
   FROM   unnest (_arr_txt::number_with_time[]) x
   WHERE  _ts > '2014-04-19 20:00:00';
$func$;

-- plpgsql
CREATE OR REPLACE FUNCTION myfunc_plpgsql(_arr_txt text)
  RETURNS integer       -- example
  LANGUAGE plpgsql AS
$func$
DECLARE
   _arr number_with_time[] := _arr_txt::number_with_time[];
BEGIN
   RETURN (
   SELECT sum(_num)::int
   FROM   unnest (_arr) x
   WHERE  _ts > '2014-04-19 20:00:00'
   );
END
$func$;
-- elementary composite type
SELECT '(1,"2014-04-20 20:00")'::number_with_time::text;


text


(1,"2014-04-20 20:00:00")


-- array of number_with_time (JDBC is limited, so I cast to text for display):
SELECT ARRAY['(1,"2014-04-20 20:00")','(2,"2014-04-21 21:00")']       ::number_with_time[]::text AS arr_of_composite
UNION ALL
SELECT '{"(1,\"2014-04-20 20:00:00\")","(2,\"2014-04-21 21:00:00\")"}'::text::number_with_time[]::text
UNION ALL
SELECT '{"(1,2014-04-20 20:00:00)","(2,2014-04-21 21:00:00)"}'        ::text::number_with_time[]::text
UNION ALL
SELECT '{"(1,2014-04-20 20:00:00)","(2,2014-04-21 21:00:00)"}'        ::number_with_time[]::text;


arr_of_composite


{"(1,"2014-04-20 20:00:00")","(2,"2014-04-21 21:00:00")"}

{"(1,"2014-04-20 20:00:00")","(2,"2014-04-21 21:00:00")"}

{"(1,"2014-04-20 20:00:00")","(2,"2014-04-21 21:00:00")"}

{"(1,"2014-04-20 20:00:00")","(2,"2014-04-21 21:00:00")"}


-- demo calls
SELECT myfunc_sql    ('{"(1,2014-04-20 20:00:00)","(2,2014-04-21 21:00:00)"}')
     , myfunc_plpgsql('{"(1,2014-04-20 20:00:00)","(2,2014-04-21 21:00:00)"}');


myfunc_sql
myfunc_plpgsql


3
3