Thread: Set returning aggregate?
I'm wondering if an aggregate function can return a set of records?
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
Hello
2013/12/8 Bborie Park <dustymugs@gmail.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
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 | 12Is 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 ParkPostGIS Steering Committee
Hello,
could it be possible then to define a custom CTE as the result of a function?somthing like :
with first_cte AS (
select blabla)
, second_cte AS (
a_function_returning_a_set(parameters)
)
)
SELECT blabla
Cheers,
Rémi-C
2013/12/8 Pavel Stehule <pavel.stehule@gmail.com>
Hello2013/12/8 Bborie Park <dustymugs@gmail.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 msIt will be hard defined a expected behaviour when somebody use more these aggregates in same query and returns different number of rows.RegardsPavelSay 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 | 12Is 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 ParkPostGIS Steering Committee