Re: Group By and wildcards...

Tom Lane wrote:
> Bruno Wolff III <bruno(at)wolff(dot)to> writes:
>
>> Jon Lapham <lapham(at)jandr(dot)org> 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 <some join conditions linking a,b,c,d>

followed by

SELECT *
FROM a, b, c, foo
WHERE <some join conditions linking a,b,c>
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 <lapham(at)jandr(dot)org> Rio de Janeiro, Brasil
Personal: http://www.jandr.org/
***-*--*----*-------*------------*--------------------*---------------