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

От:

Tom Lane <tgl@sss.pgh.pa.us>

Дата:

15 декабря 2008 г. в 09:19:34

"Philippe Lang"  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.
		regards, tom lane

"Subquery must return only one column" & query optimization

От:

"Philippe Lang" <philippe.lang@attiksystem.ch>

Дата:

15 декабря 2008 г. в 08:57:53

Hi,

I was trying to run this query this morning:

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

r.*,

(SELECT 
rl.reminder_header,rl.reminder_footer
FROM reminder_levels AS rlWHERE 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
SQL state: 42601
--------------------------

Is there a way to avoid writing:

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

r.*,

(SELECT 
rl.reminder_header
FROM reminder_levels AS rlWHERE 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 rlWHERE rl.lookup =(	SELECT MAX(reminder_level_lookup) 	FROM reminders	WHERE customer_id = r.customer_id)
) AS reminder_footer

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

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

--------------------------FROM reminder_levels AS rlWHERE rl.lookup =(	SELECT MAX(reminder_level_lookup) 	FROM reminders	WHERE customer_id = r.customer_id)
--------------------------

Thanks,

Philippe Lang

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

От:

"Philippe Lang" <philippe.lang@attiksystem.ch>

Дата:

15 декабря 2008 г. в 10:28:13

pgsql-sql-owner@postgresql.org wrote:
> "Philippe Lang"  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