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

CREATE TYPE groupcount AS (name text, COUNT int);
CREATE TYPE groupsresult AS(cats int, dogs int, birds int);
-- your original
WITH unnestedTable AS (WITH resultTable AS (SELECT ARRAY [ ('Cats', 5)::GroupCount, ('Dogs', 2)::GroupCount ] resp)
                    SELECT unnest(resp)::GroupCount t
                    FROM resultTable)
SELECT (
        (SELECT (unnestedTable.t::GroupCount).count FROM unnestedTable WHERE (unnestedTable.t::GroupCount).name = 'Cats'),
        (SELECT (unnestedTable.t::GroupCount).count FROM unnestedTable WHERE (unnestedTable.t::GroupCount).name = 'Dogs'),
        (SELECT (unnestedTable.t::GroupCount).count FROM unnestedTable WHERE (unnestedTable.t::GroupCount).name = 'Birds')
)::GroupsResult;


row


(5,2,)


-- simpler equivalent
SELECT (min(COUNT) FILTER (WHERE name = 'Cats')
      , min(COUNT) FILTER (WHERE name = 'Dogs')
      , min(COUNT) FILTER (WHERE name = 'Birds'))::groupsresult
FROM   unnest('{"(Cats,5)","(Dogs,2)"}'::groupcount[]) u;


row


(5,2,)