Re: LISTAGG à la Oracle in PostgreSQL

út 10. 3. 2026 v 21:23 odesílatel Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
napsal:

> Hi
>
> út 10. 3. 2026 v 20:58 odesílatel Juan Rodrigo Alejandro Burgos Mella <
> rodrigoburgosmella(at)gmail(dot)com> napsal:
>
>> To do something similar, you would have to fork the source code and
>> implement the declarations with the same syntax, resulting in something
>> like Postracle.
>>
>
> orafce has listagg function https://github.com/orafce/orafce
>

but it doesn't support syntax WITHING GROUP syntax. Probably there is not a
possibility to implement it in extension without introducing a new kind of
aggregate functions in core, or enhancing behaviour of ordered-set kind of
aggregates.

Regards

Pavel

> Regards
>
> Pavel
>
>
>>
>> Atte
>> JRBM
>>
>> El mar, 10 mar 2026 a las 13:53, Pierre Forstmann (<
>> pierre(dot)forstmann(at)gmail(dot)com>) escribió:
>>
>>> I agree but I just would like to know if there is way to be compatible
>>> with Oracle syntax using aggregate features in PostgreSQL
>>>
>>> Thanks.
>>>
>>> Le 09/03/2026 à 23:05, Paul A Jungwirth a écrit :
>>> > On Mon, Mar 9, 2026 at 1:21 PM Pierre Forstmann
>>> > <pierre(dot)forstmann(at)gmail(dot)com> wrote:
>>> >> Hello,
>>> >>
>>> >> I can write a LISTAGG aggregate for:
>>> >>
>>> >> create table emp(deptno numeric, ename text);
>>> >>
>>> >> SELECT deptno, LISTAGG(ename, ','::text ORDER BY ename) AS employees
>>> >> FROM emp GROUP BY deptno ORDER BY deptno;
>>> >>
>>> >> I would like to know if is possible to create an aggregate LISTAGG
>>> that
>>> >> would work like in Oracle:
>>> >>
>>> >> SELECT deptno,
>>> >> listagg(ename, ',') WITHIN GROUP (ORDER BY ename) AS
>>> employees
>>> >> FROM emp
>>> >> GROUP BY deptno
>>> >> ORDER BY deptno;
>>> > I don't think you need a custom aggregate here. In Postgres you can
>>> say:
>>> >
>>> > select deptno,
>>> > string_agg(ename, ',' ORDER BY ename) AS employees
>>> > FROM emp
>>> > GROUP BY deptno
>>> > ORDER BY deptno;
>>> >
>>>
>>>
>>>