Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1w04vW-001b7w-1P for pgsql-general@arkaria.postgresql.org; Tue, 10 Mar 2026 21:47:42 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1w04vS-0067Xx-2r for pgsql-general@arkaria.postgresql.org; Tue, 10 Mar 2026 21:47:39 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1w04vS-0067Xp-16 for pgsql-general@lists.postgresql.org; Tue, 10 Mar 2026 21:47:39 +0000 Received: from mail-yx1-xb136.google.com ([2607:f8b0:4864:20::b136]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1w04vQ-00000001VoL-2Krf for pgsql-general@lists.postgresql.org; Tue, 10 Mar 2026 21:47:37 +0000 Received: by mail-yx1-xb136.google.com with SMTP id 956f58d0204a3-64c9ebd1369so12498043d50.1 for ; Tue, 10 Mar 2026 14:47:36 -0700 (PDT) ARC-Seal: i=1; a=rsa-sha256; t=1773179256; cv=none; d=google.com; s=arc-20240605; b=ip9hy1BH3iMTSLcPa5DwV38ZyaLooPQ7uR3N2RvkynyRl0TajmtHCtzUKVPQwEt6Ei hdUOq9gLAp46mm/2xxqGabfHZhXMNB1qfQU+GTq7rYPan98YA17wWXMXiWTi7KrOG2z1 Qw1M2CdAdyzek1aIZB0RP6oSAMZ+D0M0E3JQFobsHcREf7v08YfcJc3pw03QlSrqz4Qb KhnCcXfeQIWQW4f1K92J9aikPizYl9mhCE8vWszFdHpm1swqV+Z/Prbr/licYLzY7huD Y4x+VlzE9G1hH/mYfNuQmnH0y8NhGfBCyRbFixLGBWFW/zQj/CF/AksCbkSFS19JYrXj mGCg== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:dkim-signature; bh=wkIcFUK0LqlM/k9EgKELHKPpe8Vv1v7Vg/6eh1RMQWE=; fh=AHegU+t/UPvygpOoZ4PmLKPhiQ6BsQM5FDjKykE/xjE=; b=GYIARFtXKloyqNQFVzODwK+hxY6KfzUsoetzDpFC4itVy6dZLtwtLb4GwhfNxCHKsU jjwdpxyUps2nbSOf/YQf1FYGWozk4+r4eMbn/zgZMWOx6OxpqbNoJGo2hq/jaCRUVliI D+aPjezhr97KKwNgNTPdSikfFXdgtjyCf+yT71yjYASvLaS+4unfKOGkhC/4uGpTZMTp cyR4xfdTzyrZcX0b1raHzwpPw7NEQGKB73Wphz84tfSk/2aKG5Gm8gTO1HGMUJnnzG/U JJ3Aw62M+IzYRlhAw3MqGw7+0CXnNABngzK3WCqu6AK2sEgpR5Svpelh0oCLVzgkaPLM 4HiQ==; darn=lists.postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1773179256; x=1773784056; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=wkIcFUK0LqlM/k9EgKELHKPpe8Vv1v7Vg/6eh1RMQWE=; b=haWrc5KKGoFhgtAUya93tD6XOCjGV92iOfKRafmCgEUDB/bt5C6nBZ3xib2WY2DrvX 0ab4dAAAk0UxlMxZ7T99rScfKlpBUiSNJGj2+FvDsMVJfzvZ3mTg4+WgzPo002/HZEfN mR5XgOQELn0Yxx3FyH5WWhqO5BmU2baYJqyJksI0diDX/AAToZ/JunrHVPjnA6vu6rFL 2HQXa7Ce24h8XSzG+OvZPQNKzBSVr1E6n+geLQF0ZkBUmo6pT9gIBXfXkUUfQR3V5aDF PRQ3MbCgyymNJZ3ERnHG4P43969spyfDm50U+5g08fMPmwrH452Ul/Dvm9O+x95gFYdi 5qpg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1773179256; x=1773784056; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=wkIcFUK0LqlM/k9EgKELHKPpe8Vv1v7Vg/6eh1RMQWE=; b=BDzBKkPtBA2jmkmr2rwCkcETrfL4oE9XHPMA4lVq8jYUyhYY9MdV4oAFswNQ099sUp KvKwifwBFgVp+lts5VZaIDVU1bGpsaWvQmjveniFC7S70cQPF7+f6aEsrI9nzu2fUpQV 9ERZORO/YWTzfkUthIYkWuV5uvnfAWjyvNLhNB5YrMdEaGhM/lQyCVsu+h6LcHVfJR5R EKDYlGgXOFcU9oB8BBKHsR8XZg0x2vUr11Q8UGzJNgUhHg2dPRf8IQ8rEm5+m8SGWjQ1 xa28icMDUCdH32ljNMzTvnrhsfSqTtX4P9CxlcixhSqYEw7b4fW41y+nKO7AVo/2IIxF oS8g== X-Forwarded-Encrypted: i=1; AJvYcCWONrb9m3ltjgqxU18MtoO9/f7ak96QYasrCUsGOFqSx2o9JswGmVyOElj0fQYDQ03nBJaz69sTE07L9C7G@lists.postgresql.org X-Gm-Message-State: AOJu0YwIgtwHPDmo3w6+exL/gArWl4yBZL0/woE+n6QN3TK5LkDkonFB B0Ws1x1ai1+UnH25Hv5gM9lPFRaUCd8erBM4msSMAGCSrJ2eOPZb7akNC1s7F5+n2XK/HEV4fa0 ApwJT/JZsCKCbT/IEdgFTizJA1XS/a8o= X-Gm-Gg: ATEYQzyQ8sgk5gwbn3k2oKXxqYMm45yY6bsWCKwFV9L99sKpCtR7GsTObqntUzvi1t2 /PYxYb8zoXWWy5PpqeNNhA2LcSxWcv0wOXt3P0/YFjRlRHFaOn7q1WiXIzVVGhxB8g+PS8X34S9 kDUYvuun5dwnaVRToa+5Z6ukm38jDmDKixEwQRTAggkfmZVE901Ang5tH+BPrhMWTjlsHV30ef9 ZDay9IdEUzaq4w5ZivUA8f2TOGQtW5/G2EaEoDTXvdo7mYwg4BWjkqT+xLjF4ncogtQ10QPXdH3 S/TF0h8R0wlW/k1yDxlDij/PIAC802CF5Fbbk+JKGjFPaGBIM5rsvOaEzBJ39TblLVo9WTKPKOj dY0zrjwbgViOw5x2RK3kuOiR51ddy2iq6giBzBMNvoNw34UyI2P/JBIlfOg== X-Received: by 2002:a53:cdc7:0:b0:64d:601f:1395 with SMTP id 956f58d0204a3-64d657c15b6mr177509d50.61.1773179255816; Tue, 10 Mar 2026 14:47:35 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Pavel Stehule Date: Tue, 10 Mar 2026 22:46:59 +0100 X-Gm-Features: AaiRm53OWqfO5lqeo0bxnAMw99WafWx-5JM7iq0LyHQwvYciAAt3kdJ8u_lHxNk Message-ID: Subject: =?UTF-8?Q?Re=3A_LISTAGG_=C3=A0_la_Oracle_in_PostgreSQL?= To: Juan Rodrigo Alejandro Burgos Mella Cc: Pierre Forstmann , Paul A Jungwirth , "pgsql-general@lists.postgresql.org" Content-Type: multipart/alternative; boundary="000000000000aa8470064cb277e6" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000aa8470064cb277e6 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable =C3=BAt 10. 3. 2026 v 21:23 odes=C3=ADlatel Pavel Stehule napsal: > Hi > > =C3=BAt 10. 3. 2026 v 20:58 odes=C3=ADlatel Juan Rodrigo Alejandro Burgos= Mella < > rodrigoburgosmella@gmail.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.forstmann@gmail.com>) escribi=C3=B3: >> >>> 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 =C3=A0 23:05, Paul A Jungwirth a =C3=A9crit : >>> > On Mon, Mar 9, 2026 at 1:21=E2=80=AFPM Pierre Forstmann >>> > 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; >>> > >>> >>> >>> --000000000000aa8470064cb277e6 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


=C3=BAt 10. 3. = 2026 v=C2=A021:23 odes=C3=ADlatel Pavel Stehule <pavel.stehule@gmail.com> napsal:
Hi
<= br>
=C3=BAt= 10. 3. 2026 v=C2=A020:58 odes=C3=ADlatel Juan Rodrigo Alejandro Burgos Mel= la <ro= drigoburgosmella@gmail.com> napsal:
To do something similar, you wo= uld have to fork the source code and implement the declarations with the sa= me syntax, resulting in something like Postracle.
orafce has listagg function=C2=A0https://github.com/orafce/orafce

but it doesn't support sy= ntax WITHING=C2=A0GROUP syntax. Probably there is not a possibility to impl= ement it in extension without introducing a new kind of aggregate functions= in core, or enhancing behaviour of=C2=A0ordered-set kind of aggregates.=C2=A0= =C2=A0

Regards

Pavel





Regards

Pavel
= =C2=A0

Atte
JRBM

El mar, 10 mar 2026 a las = 13:53, Pierre Forstmann (<pierre.forstmann@gmail.com>) escribi=C3=B3:
I agree but I just wou= ld like to know if there is way to be compatible
with Oracle syntax using aggregate features in PostgreSQL

Thanks.

Le 09/03/2026 =C3=A0 23:05, Paul A Jungwirth a =C3=A9crit=C2=A0:
> On Mon, Mar 9, 2026 at 1:21=E2=80=AFPM Pierre Forstmann
> <pi= erre.forstmann@gmail.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=C2=A0 =C2=A0emp 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,
>>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 listagg(ename, ',') WITH= IN 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 ca= n say:
>
> select deptno,
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 string_agg(ename, ',' ORDER BY enam= e) AS employees
> FROM emp
> GROUP BY deptno
> ORDER BY deptno;
>


--000000000000aa8470064cb277e6--