Re: Group By and wildcards...

Tom Lane wrote:
> Bruno Wolff III  writes:
> 
>>  Jon Lapham  wrote:
>>
>>>When using queries with aggregate functions, is there any way to not 
>>>have to have to explicitly write all the columns names after the GROUP 
>>>BY ?  I would like to use a wildcard "*".
> 
> 
>>Don't those tables have primary keys? Grouping by the primay key of each
>>table will produce the same result set as grouping by all of the columns.

Bruno, this is true, but I want all the columns to appear in the output.

> Unfortunately, PG will still make him GROUP BY everything he wants to
> use as a non-aggregated output column.  This behavior is per SQL92
> spec.  SQL99 added some verbiage to the effect that you only need to
> GROUP BY columns that the rest are functionally dependent on (this
> covers primary keys and some other cases); but we haven't got round
> to implementing that extension.

Ugh.

Since I do not want to have to re-write all my aggregate function 
containing queries upon modifications to the table definitions (and I do 
not want to write multi-thousand character long SELECT statements), 
maybe it is easier to use a temp table intermediary?

SELECT a.id AS aid, SUM(d.blah) AS sum_blah
INTO TEMPORARY TABLE foo
FROM a, b, c, d
WHERE 

followed by

SELECT *
FROM a, b, c, foo
WHERE 
   AND foo.aid=a.id

Ugly... ugly... any other ideas on how to do this?  My table definitions 
LITERALLY have hundreds of columns, and I need access to them all.

-- 
-**-*-*---*-*---*-*---*-----*-*-----*---*-*---*-----*-----*-*-----*---
  Jon Lapham                  Rio de Janeiro, Brasil
  Personal: http://www.jandr.org/
***-*--*----*-------*------------*--------------------*---------------