public inbox for [email protected]help / color / mirror / Atom feed
Re: LISTAGG à la Oracle in PostgreSQL 3+ messages / 2 participants [nested] [flat]
* Re: LISTAGG à la Oracle in PostgreSQL @ 2026-03-10 20:23 Pavel Stehule <[email protected]> 2026-03-10 21:46 ` Re: LISTAGG à la Oracle in PostgreSQL Pavel Stehule <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Pavel Stehule @ 2026-03-10 20:23 UTC (permalink / raw) To: Juan Rodrigo Alejandro Burgos Mella <[email protected]>; +Cc: Pierre Forstmann <[email protected]>; Paul A Jungwirth <[email protected]>; [email protected] <[email protected]> Hi út 10. 3. 2026 v 20:58 odesílatel Juan Rodrigo Alejandro Burgos Mella < [email protected]> 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 Regards Pavel > > Atte > JRBM > > El mar, 10 mar 2026 a las 13:53, Pierre Forstmann (< > [email protected]>) 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 >> > <[email protected]> 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; >> > >> >> >> ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: LISTAGG à la Oracle in PostgreSQL 2026-03-10 20:23 Re: LISTAGG à la Oracle in PostgreSQL Pavel Stehule <[email protected]> @ 2026-03-10 21:46 ` Pavel Stehule <[email protected]> 2026-03-11 15:53 ` Re: LISTAGG à la Oracle in PostgreSQL [email protected] 0 siblings, 1 reply; 3+ messages in thread From: Pavel Stehule @ 2026-03-10 21:46 UTC (permalink / raw) To: Juan Rodrigo Alejandro Burgos Mella <[email protected]>; +Cc: Pierre Forstmann <[email protected]>; Paul A Jungwirth <[email protected]>; [email protected] <[email protected]> út 10. 3. 2026 v 21:23 odesílatel Pavel Stehule <[email protected]> napsal: > Hi > > út 10. 3. 2026 v 20:58 odesílatel Juan Rodrigo Alejandro Burgos Mella < > [email protected]> 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 (< >> [email protected]>) 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 >>> > <[email protected]> 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; >>> > >>> >>> >>> ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: LISTAGG à la Oracle in PostgreSQL 2026-03-10 20:23 Re: LISTAGG à la Oracle in PostgreSQL Pavel Stehule <[email protected]> 2026-03-10 21:46 ` Re: LISTAGG à la Oracle in PostgreSQL Pavel Stehule <[email protected]> @ 2026-03-11 15:53 ` [email protected] 0 siblings, 0 replies; 3+ messages in thread From: [email protected] @ 2026-03-11 15:53 UTC (permalink / raw) To: Pavel Stehule <[email protected]>; Juan Rodrigo Alejandro Burgos Mella <[email protected]>; Paul A Jungwirth <[email protected]>; [email protected] <[email protected]> Thanks. On 10/03/2026 22:46, Pavel Stehule <[email protected]> wrote: > > > út 10. 3. 2026 v 21:23 odesílatel Pavel Stehule <[email protected] > <mailto:[email protected]>> napsal: > > Hi > > út 10. 3. 2026 v 20:58 odesílatel Juan Rodrigo Alejandro Burgos > Mella <[email protected] > <mailto:[email protected]>> 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 > <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 > (<[email protected] > <mailto:[email protected]>>) 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 > > <[email protected] > <mailto:[email protected]>> 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; > > > > > ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-03-11 15:53 UTC | newest] Thread overview: 3+ messages (download: mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2026-03-10 20:23 Re: LISTAGG à la Oracle in PostgreSQL Pavel Stehule <[email protected]> 2026-03-10 21:46 ` Pavel Stehule <[email protected]> 2026-03-11 15:53 ` [email protected]
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox