Re: Set returning aggregate?

Hello

2013/12/8 Bborie Park <dustymugs(at)gmail(dot)com>

> I'm wondering if an aggregate function can return a set of records?
>

No, final function cannot returns set. It is disallowed.

Theoretically, it should be possible - it is explicitly prohibited. But if
it will be allowed, you can get same problems like using SRF function in
target list.

postgres=# select generate_series(1,2),generate_series(1,2);
generate_series │ generate_series
─────────────────┼─────────────────
1 │ 1
2 │ 2
(2 rows)

Time: 49.332 ms
postgres=# select generate_series(1,2),generate_series(1,3);
generate_series │ generate_series
─────────────────┼─────────────────
1 │ 1
2 │ 2
1 │ 3
2 │ 1
1 │ 2
2 │ 3
(6 rows)

Time: 0.445 ms

It will be hard defined a expected behaviour when somebody use more these
aggregates in same query and returns different number of rows.

Regards

Pavel

>
> Say I have a table with a column of type raster (PostGIS). I want to get
> the number of times the pixel values 1, 3 and 4 occur in that raster
> column. I am hoping to build an aggregrate function that returns the
> following...
>
> value | count
> --------+--------
> 1 | 12
> --------+--------
> 2 | 12
> --------+--------
> 3 | 12
>
> Is it possible for an aggregate function to return a set? I've written
> some test cases and it looks like the answer is No but I'd like
> confirmation.
>
> Thanks,
> Bborie Park
>
> PostGIS Steering Committee
>