CODEWITHSUNDEEP
sqlpostgresql
unacowa
unacowa

Reputation: 91

How to split compound column like ROW, ARRAY?

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.

Upvotes: 0

Views: 511

Answers (2)

tinychen
tinychen

Reputation: 2099

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

Upvotes: 1

Andomar
Andomar

Reputation: 238296

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
        )

Upvotes: 1

Related Questions