Re: "Subquery must return only one column" & query optimization

pgsql-sql-owner@postgresql.org wrote:
> "Philippe Lang" <philippe.lang@attiksystem.ch> writes:
>> I was trying to run this query this morning:
>
>> --------------------------
>> SELECT
>
>> r.*,
>
>> (
>>     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
>
> Since 8.0 or so you could write the sub-select as
>
>     SELECT ROW(rl.reminder_header, rl.reminder_footer) FROM ...
>
> We ought to make that happen automatically, but it's not real high on
> the to-do list.

Hi Tom,

Fine, the query is faster now:

------------------------
SELECT

r.*,

(SELECT
ROW(rl.reminder_header, rl.reminder_header)
FROM reminder_levels AS rlWHERE rl.lookup =(    SELECT MAX(reminder_level_lookup)     FROM reminders    WHERE
customer_id= r.customer_id) 
) AS rec

FROM reminders AS r
------------------------

... but the last column is now of type "record", and since this query
serves as a datasource for a MS Access report, it is not able to "split"
the record into the values that interest me: reminder_header and
reminder_footer!

Regards,

Philippe Lang