How to split compound column like ROW, ARRAY?

I have same problem with this stopped thread.

http://www.mail-archive.com/pgsql-sql@postgresql.org/msg28070.html

QUERY-1

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

Postgresql replied that:

ERROR: subquery must return only one column

QUERY-2

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

id  |  reminder_header  |  reminder_footer
----+-------------------+--------------------
1   |  hogehoge         |  fugafuga

... which works, but runs twice the same subselect block.

This makes performance kill.

(but, this result table is what I want.)

QUERY-3

SELECT 
r.id,
(
        SELECT 
        ROW(rl.reminder_header, rl.reminder_header)
        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

id  |  rec
----+----------------------
1   |  (hogehoge, fugafuga)

... which works, but column 'rec' is compound.

How to split this 'rec' to reminder_header and reminder_footer, like Query-2.

There is some procedure or tequnique? or other solution?

Thanks.

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
        )

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