valgog@gmail.com writes:
> select 'key.' || md5( s.i::text ) as key,
> 'value.' || ( select string_agg( md5( (s.i)::text), '' )
> from generate_series(1, 20) as g(j)
> ) as value
> from generate_series(1, 100) as s(i);
> fails with:
> ERROR: column "s.i" must appear in the GROUP BY clause or be used in an
> aggregate function
> LINE 2: select 'key.' || md5( s.i::text ) as key,
That's per SQL standard, AFAICS. The string_agg() call is an aggregate of
the outer query, *not* the sub-select, because the lowest-level variable
in the aggregate's argument is of the outer query. See SQL:2011
6.9 <set function specification> syntax rule 6:
6) The aggregation query of a <set function specification> SFS is determined as follows. Case:
a) If SFS has no aggregated column reference, then the aggregation query of SFS is the innermost <query
specification>that contains SFS.
b) Otherwise, the innermost qualifying query of the aggregated column references of SFS is the aggregation query
ofSFS.
So the outer query has an aggregate, and that means that any variables
that aren't within the aggregate call have to be grouped columns.
regards, tom lane
