How to split compound column like ROW, ARRAY?

Answer 1

To select multiple columns from a subquery, you'd normally use a join. Now I'm not sure exactly how your tables relate, but something like this would be a good start:

SELECT  r.id
,       rl.reminder_header
,       rl.reminder_footer
FROM    reminders r
JOIN    reminder_levels AS rl
ON      rl.customer_id = r.customer_id
WHERE   rl.lookup =
        (
        SELECT  MAX(reminder_level_lookup) 
        FROM    reminders r2
        WHERE   r2.customer_id = r.customer_id
        )

Answer 2

You can do like this:

with query as ( 
SELECT 
r.id,
(
        SELECT 
        r1::reminder_levels
        FROM reminder_levels AS rl
        WHERE rl.lookup =
        (
                SELECT MAX(reminder_level_lookup) 
                FROM reminders
                WHERE customer_id = r.customer_id
        )
) AS rec
FROM reminders AS r)
select id,
       (rec::reminder_levels).reminder_header,
       (rec::reminder_levels).reminder_footer
from query