Re: Set returning aggregate?

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

A CTE is much like what you would like bborie :
you can reference previously defined CTE, and you output a set of row.

Cheers,
Rémi-C

2013/12/8 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>

> 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
>>
>
>