How to split compound column like ROW, ARRAY?

Question

I have same problem with this stopped thread.

http://www.mail-archive.com/[email protected]/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.

Andomar · Accepted Answer · 2010-08-02 12:27:04Z

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
        )

answered Aug 2, 2010 at 12:27

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Brian Hooper Over a year ago

+1 @Andomar, I was groping towards this when I saw your answer, although having worked it out (seen your version) I'm obliged to admit that I like the subquery version better.

tinychen · Accepted Answer · 2010-08-03 01:36:58Z

1

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

answered Aug 3, 2010 at 1:36

tinychen

2,0992 gold badges13 silver badges8 bronze badges

Collectives™ on Stack Overflow

How to split compound column like ROW, ARRAY?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related