Re: Group By and wildcards...

Tom Lane wrote:
> Bruno Wolff III <bruno@wolff.to> writes:
>
>>  Jon Lapham <lapham@jandr.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@jandr.org>                Rio de Janeiro, Brasil
  Personal: http://www.jandr.org/
***-*--*----*-------*------------*--------------------*---------------