MIME-Version: 1.0
From: =?UTF-8?Q?Viliam_=C4=8Eurina?= <viliam.durina@gmail.com>
Date: Fri, 22 Mar 2024 12:26:27 +0100
Message-ID: <CAO=iB8L4WYSNxCJ8GURRjQsrXEQ2-zn3FiCsh2LMqvWq2WcONg@mail.gmail.com>
Subject: MIN/MAX functions for a record
To: pgsql-hackers@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000071ea6106143e1ad8"
Archived-At: <https://www.postgresql.org/message-id/CAO%3DiB8L4WYSNxCJ8GURRjQsrXEQ2-zn3FiCsh2LMqvWq2WcONg%40mail.gmail.com>
Precedence: bulk

--00000000000071ea6106143e1ad8
Content-Type: text/plain; charset="UTF-8"

In my queries I often need to do MIN/MAX for tuples, for example:

  SELECT MAX(row(year, month))
  FROM (VALUES(2025, 1), (2024,2)) x(year, month);

This query throws:

    ERROR: function max(record) does not exist

In this case you can replace it with `MAX((year||'-'||month||'-1')::date)`.
However in my case I have an event table with `event_time` and `text`
columns, I'm grouping that table by some key and want to have the text for
the newest event. I would do `MAX(ROW(event_time, text)).text`. Workarounds
for this are clumsy, e.g. with a subquery with LIMIT 1.

The lack of this feature is kind of unexpected, because the `>` operator or
`GREATEST` function are defined for records:

    SELECT
        GREATEST((2025, 1), (2024, 2)),
        (2025, 1) > (2024, 2)

Was this ever discussed or is there something preventing the implementation?

Viliam

--00000000000071ea6106143e1ad8
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>In my queries I often need to do MIN/MAX for tuples, =
for example:</div><div><br></div><div><span style=3D"font-family:monospace"=
>=C2=A0 SELECT MAX(row(year, month))=C2=A0</span></div><div><span style=3D"=
font-family:monospace">=C2=A0 FROM (VALUES(2025, 1), (2024,2)) x(year, mont=
h);</span></div><div><br></div><div>This query throws:</div><div><br></div>=
<div>=C2=A0 =C2=A0 <code class=3D"gmail-language-error">ERROR:  function ma=
x(record) does not exist</code></div><div><code class=3D"gmail-language-err=
or"><br></code></div><div>In this case you can replace it with `MAX((year||=
&#39;-&#39;||month||&#39;-1&#39;)::date)`. However in my case I have an eve=
nt table
 with `event_time` and `text` columns, I&#39;m grouping that table by=20
some key and want to have the text for the newest event. I would do=20
`MAX(ROW(event_time, text)).text`. Workarounds for this are clumsy, e.g. wi=
th a subquery with LIMIT 1.

</div><div><code class=3D"gmail-language-error"><br></code></div><div><code=
 class=3D"gmail-language-error"><font face=3D"arial,sans-serif">The lack of=
 this feature is kind of unexpected, because the `&gt;` operator or `GREATE=
ST` function are defined for records:</font></code></div><div><code class=
=3D"gmail-language-error"><font face=3D"arial,sans-serif"><br></font></code=
></div><div>=C2=A0=C2=A0=C2=A0 SELECT=C2=A0</div><div>=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0 GREATEST((2025, 1), (2024, 2)),=C2=A0</div><div>=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 (2025, 1) &gt; (2024, 2) <br></div>=
<div><br></div><div>Was this ever discussed or is there something preventin=
g the implementation?<br></div><div><br></div><div>Viliam<br></div></div>

--00000000000071ea6106143e1ad8--