[PATCH v35 6/7] Doc part of shared-memory based stats collector.

public inbox for [email protected]  
help / color / mirror / Atom feed

[PATCH v35 6/7] Doc part of shared-memory based stats collector.
10+ messages / 4 participants
[nested] [flat]

* [PATCH v35 6/7] Doc part of shared-memory based stats collector.
@ 2020-03-19 06:11 Kyotaro Horiguchi <[email protected]>
  0 siblings, 0 replies; 10+ messages in thread

From: Kyotaro Horiguchi @ 2020-03-19 06:11 UTC (permalink / raw)

---
 doc/src/sgml/catalogs.sgml          |   6 +-
 doc/src/sgml/config.sgml            |  34 ++++----
 doc/src/sgml/high-availability.sgml |  13 +--
 doc/src/sgml/monitoring.sgml        | 128 +++++++++++++---------------
 doc/src/sgml/ref/pg_dump.sgml       |   9 +-
 5 files changed, 91 insertions(+), 99 deletions(-)

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 700271fd40..fad9f94ddb 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -9177,9 +9177,9 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
   <para>
    <xref linkend="view-table"/> lists the system views described here.
    More detailed documentation of each view follows below.
-   There are some additional views that provide access to the results of
-   the statistics collector; they are described in <xref
-   linkend="monitoring-stats-views-table"/>.
+   There are some additional views that provide access to the activity
+   statistics; they are described in
+   <xref linkend="monitoring-stats-views-table"/>.
   </para>
 
   <para>
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index aca8f73a50..679135c6b6 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -7113,11 +7113,11 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
     <title>Run-time Statistics</title>
 
     <sect2 id="runtime-config-statistics-collector">
-     <title>Query and Index Statistics Collector</title>
+     <title>Query and Index Activity Statistics</title>
 
      <para>
-      These parameters control server-wide statistics collection features.
-      When statistics collection is enabled, the data that is produced can be
+      These parameters control server-wide activity statistics features.
+      When activity statistics is enabled, the data that is produced can be
       accessed via the <structname>pg_stat</structname> and
       <structname>pg_statio</structname> family of system views.
       Refer to <xref linkend="monitoring"/> for more information.
@@ -7133,14 +7133,13 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
       </term>
       <listitem>
        <para>
-        Enables the collection of information on the currently
-        executing command of each session, along with the time when
-        that command began execution. This parameter is on by
-        default. Note that even when enabled, this information is not
-        visible to all users, only to superusers and the user owning
-        the session being reported on, so it should not represent a
-        security risk.
-        Only superusers can change this setting.
+        Enables activity tracking on the currently executing command of
+        each session, along with the time when that command began
+        execution. This parameter is on by default. Note that even when
+        enabled, this information is not visible to all users, only to
+        superusers and the user owning the session being reported on, so it
+        should not represent a security risk.  Only superusers can change this
+        setting.
        </para>
       </listitem>
      </varlistentry>
@@ -7171,9 +7170,9 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
       </term>
       <listitem>
        <para>
-        Enables collection of statistics on database activity.
+        Enables tracking of database activity.
         This parameter is on by default, because the autovacuum
-        daemon needs the collected information.
+        daemon needs the activity information.
         Only superusers can change this setting.
        </para>
       </listitem>
@@ -8234,7 +8233,7 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
       <listitem>
        <para>
         Specifies the fraction of the total number of heap tuples counted in
-        the previous statistics collection that can be inserted without
+        the previously collected statistics that can be inserted without
         incurring an index scan at the <command>VACUUM</command> cleanup stage.
         This setting currently applies to B-tree indexes only.
        </para>
@@ -8246,9 +8245,10 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
         the index contains deleted pages that can be recycled during cleanup.
         Index statistics are considered to be stale if the number of newly
         inserted tuples exceeds the <varname>vacuum_cleanup_index_scale_factor</varname>
-        fraction of the total number of heap tuples detected by the previous
-        statistics collection. The total number of heap tuples is stored in
-        the index meta-page. Note that the meta-page does not include this data
+
+        fraction of the total number of heap tuples in the previously
+        collected statistics. The total number of heap tuples is stored in the
+        index meta-page. Note that the meta-page does not include this data
         until <command>VACUUM</command> finds no dead tuples, so B-tree index
         scan at the cleanup stage can only be skipped if the second and
         subsequent <command>VACUUM</command> cycles detect no dead tuples.
diff --git a/doc/src/sgml/high-availability.sgml b/doc/src/sgml/high-availability.sgml
index 65c3fc62a9..56ef2f56f6 100644
--- a/doc/src/sgml/high-availability.sgml
+++ b/doc/src/sgml/high-availability.sgml
@@ -2368,12 +2368,13 @@ LOG:  database system is ready to accept read only connections
    </para>
 
    <para>
-    The statistics collector is active during recovery. All scans, reads, blocks,
-    index usage, etc., will be recorded normally on the standby. Replayed
-    actions will not duplicate their effects on primary, so replaying an
-    insert will not increment the Inserts column of pg_stat_user_tables.
-    The stats file is deleted at the start of recovery, so stats from primary
-    and standby will differ; this is considered a feature, not a bug.
+    The activity statistics is collected during recovery. All scans, reads,
+    blocks, index usage, etc., will be recorded normally on the
+    standby. Replayed actions will not duplicate their effects on primary, so
+    replaying an insert will not increment the Inserts column of
+    pg_stat_user_tables.  The activity statistics is reset at the start of
+    recovery, so stats from primary and standby will differ; this is
+    considered a feature, not a bug.
    </para>
 
    <para>
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 89662cc0a3..4973d69cfc 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -22,7 +22,7 @@
   <para>
    Several tools are available for monitoring database activity and
    analyzing performance.  Most of this chapter is devoted to describing
-   <productname>PostgreSQL</productname>'s statistics collector,
+   <productname>PostgreSQL</productname>'s activity statistics,
    but one should not neglect regular Unix monitoring programs such as
    <command>ps</command>, <command>top</command>, <command>iostat</command>, and <command>vmstat</command>.
    Also, once one has identified a
@@ -53,7 +53,6 @@ postgres  15554  0.0  0.0  57536  1184 ?        Ss   18:02   0:00 postgres: back
 postgres  15555  0.0  0.0  57536   916 ?        Ss   18:02   0:00 postgres: checkpointer
 postgres  15556  0.0  0.0  57536   916 ?        Ss   18:02   0:00 postgres: walwriter
 postgres  15557  0.0  0.0  58504  2244 ?        Ss   18:02   0:00 postgres: autovacuum launcher
-postgres  15558  0.0  0.0  17512  1068 ?        Ss   18:02   0:00 postgres: stats collector
 postgres  15582  0.0  0.0  58772  3080 ?        Ss   18:04   0:00 postgres: joe runbug 127.0.0.1 idle
 postgres  15606  0.0  0.0  58772  3052 ?        Ss   18:07   0:00 postgres: tgl regression [local] SELECT waiting
 postgres  15610  0.0  0.0  58772  3056 ?        Ss   18:07   0:00 postgres: tgl regression [local] idle in transaction
@@ -65,9 +64,8 @@ postgres  15610  0.0  0.0  58772  3056 ?        Ss   18:07   0:00 postgres: tgl
    master server process.  The command arguments
    shown for it are the same ones used when it was launched.  The next five
    processes are background worker processes automatically launched by the
-   master process.  (The <quote>stats collector</quote> process will not be present
-   if you have set the system not to start the statistics collector; likewise
-   the <quote>autovacuum launcher</quote> process can be disabled.)
+   master process.  (The <quote>autovacuum launcher</quote> process will not
+   be present if you have set the system not to start it.)
    Each of the remaining
    processes is a server process handling one client connection.  Each such
    process sets its command line display in the form
@@ -130,20 +128,21 @@ postgres   27093  0.0  0.0  30096  2752 ?        Ss   11:34   0:00 postgres: ser
  </sect1>
 
  <sect1 id="monitoring-stats">
-  <title>The Statistics Collector</title>
+  <title>The Activity Statistics</title>
 
   <indexterm zone="monitoring-stats">
    <primary>statistics</primary>
   </indexterm>
 
   <para>
-   <productname>PostgreSQL</productname>'s <firstterm>statistics collector</firstterm>
-   is a subsystem that supports collection and reporting of information about
-   server activity.  Presently, the collector can count accesses to tables
-   and indexes in both disk-block and individual-row terms.  It also tracks
-   the total number of rows in each table, and information about vacuum and
-   analyze actions for each table.  It can also count calls to user-defined
-   functions and the total time spent in each one.
+   <productname>PostgreSQL</productname>'s <firstterm>activity
+   statistics</firstterm> is a subsystem that supports tracking and reporting
+   of information about server activity.  Presently, the activity statistics
+   tracks the count of accesses to tables and indexes in both disk-block and
+   individual-row terms.  It also tracks the total number of rows in each
+   table, and information about vacuum and analyze actions for each table.  It
+   can also track calls to user-defined functions and the total time spent in
+   each one.
   </para>
 
   <para>
@@ -151,15 +150,15 @@ postgres   27093  0.0  0.0  30096  2752 ?        Ss   11:34   0:00 postgres: ser
    information about exactly what is going on in the system right now, such as
    the exact command currently being executed by other server processes, and
    which other connections exist in the system.  This facility is independent
-   of the collector process.
+   of the activity statistics.
   </para>
 
  <sect2 id="monitoring-stats-setup">
-  <title>Statistics Collection Configuration</title>
+  <title>Activity Statistics Configuration</title>
 
   <para>
-   Since collection of statistics adds some overhead to query execution,
-   the system can be configured to collect or not collect information.
+   Since tracking for the activity statistics adds some overhead to query
+   execution, the system can be configured to track or not track activity.
    This is controlled by configuration parameters that are normally set in
    <filename>postgresql.conf</filename>.  (See <xref linkend="runtime-config"/> for
    details about setting configuration parameters.)
@@ -172,7 +171,7 @@ postgres   27093  0.0  0.0  30096  2752 ?        Ss   11:34   0:00 postgres: ser
 
   <para>
    The parameter <xref linkend="guc-track-counts"/> controls whether
-   statistics are collected about table and index accesses.
+   to track activity about table and index accesses.
   </para>
 
   <para>
@@ -196,18 +195,12 @@ postgres   27093  0.0  0.0  30096  2752 ?        Ss   11:34   0:00 postgres: ser
   </para>
 
   <para>
-   The statistics collector transmits the collected information to other
-   <productname>PostgreSQL</productname> processes through temporary files.
-   These files are stored in the directory named by the
-   <xref linkend="guc-stats-temp-directory"/> parameter,
-   <filename>pg_stat_tmp</filename> by default.
-   For better performance, <varname>stats_temp_directory</varname> can be
-   pointed at a RAM-based file system, decreasing physical I/O requirements.
-   When the server shuts down cleanly, a permanent copy of the statistics
-   data is stored in the <filename>pg_stat</filename> subdirectory, so that
-   statistics can be retained across server restarts.  When recovery is
-   performed at server start (e.g. after immediate shutdown, server crash,
-   and point-in-time recovery), all statistics counters are reset.
+   The activity statistics is placed on shared memory. When the server shuts
+   down cleanly, a permanent copy of the statistics data is stored in
+   the <filename>pg_stat</filename> subdirectory, so that statistics can be
+   retained across server restarts.  When recovery is performed at server
+   start (e.g. after immediate shutdown, server crash, and point-in-time
+   recovery), all statistics counters are reset.
   </para>
 
  </sect2>
@@ -220,48 +213,46 @@ postgres   27093  0.0  0.0  30096  2752 ?        Ss   11:34   0:00 postgres: ser
    linkend="monitoring-stats-dynamic-views-table"/>, are available to show
    the current state of the system. There are also several other
    views, listed in <xref
-   linkend="monitoring-stats-views-table"/>, available to show the results
-   of statistics collection.  Alternatively, one can
-   build custom views using the underlying statistics functions, as discussed
-   in <xref linkend="monitoring-stats-functions"/>.
+   linkend="monitoring-stats-views-table"/>, available to show the activity
+   statistics.  Alternatively, one can build custom views using the underlying
+   statistics functions, as discussed in
+   <xref linkend="monitoring-stats-functions"/>.
   </para>
 
   <para>
-   When using the statistics to monitor collected data, it is important
-   to realize that the information does not update instantaneously.
-   Each individual server process transmits new statistical counts to
-   the collector just before going idle; so a query or transaction still in
-   progress does not affect the displayed totals.  Also, the collector itself
-   emits a new report at most once per <varname>PGSTAT_STAT_INTERVAL</varname>
-   milliseconds (500 ms unless altered while building the server).  So the
-   displayed information lags behind actual activity.  However, current-query
-   information collected by <varname>track_activities</varname> is
-   always up-to-date.
+   When using the activity statistics, it is important to realize that the
+   information does not update instantaneously.  Each individual server writes
+   out new statistical counts just before going idle, not frequent than once
+   per <varname>PGSTAT_STAT_INTERVAL</varname> milliseconds (1 second unless
+   altered while building the server); so a query or transaction still in
+   progress does not affect the displayed totals.  However, current-query
+   information tracked by <varname>track_activities</varname> is always
+   up-to-date.
   </para>
 
   <para>
    Another important point is that when a server process is asked to display
-   any of these statistics, it first fetches the most recent report emitted by
-   the collector process and then continues to use this snapshot for all
-   statistical views and functions until the end of its current transaction.
-   So the statistics will show static information as long as you continue the
-   current transaction.  Similarly, information about the current queries of
-   all sessions is collected when any such information is first requested
-   within a transaction, and the same information will be displayed throughout
-   the transaction.
-   This is a feature, not a bug, because it allows you to perform several
-   queries on the statistics and correlate the results without worrying that
-   the numbers are changing underneath you.  But if you want to see new
-   results with each query, be sure to do the queries outside any transaction
-   block.  Alternatively, you can invoke
+   any of these statistics, it first reads the current statistics and then
+   continues to use this snapshot for all statistical views and functions
+   until the end of its current transaction.  So the statistics will show
+   static information as long as you continue the current transaction.
+   Similarly, information about the current queries of all sessions is tracked
+   when any such information is first requested within a transaction, and the
+   same information will be displayed throughout the transaction.  This is a
+   feature, not a bug, because it allows you to perform several queries on the
+   statistics and correlate the results without worrying that the numbers are
+   changing underneath you.  But if you want to see new results with each
+   query, be sure to do the queries outside any transaction block.
+   Alternatively, you can invoke
    <function>pg_stat_clear_snapshot</function>(), which will discard the
    current transaction's statistics snapshot (if any).  The next use of
    statistical information will cause a new snapshot to be fetched.
   </para>
-
+  
   <para>
-   A transaction can also see its own statistics (as yet untransmitted to the
-   collector) in the views <structname>pg_stat_xact_all_tables</structname>,
+   A transaction can also see its own statistics (as yet unwritten to the
+   server-wide activity statistics) in the
+   views <structname>pg_stat_xact_all_tables</structname>,
    <structname>pg_stat_xact_sys_tables</structname>,
    <structname>pg_stat_xact_user_tables</structname>, and
    <structname>pg_stat_xact_user_functions</structname>.  These numbers do not act as
@@ -620,7 +611,7 @@ postgres   27093  0.0  0.0  30096  2752 ?        Ss   11:34   0:00 postgres: ser
    kernel's I/O cache, and might therefore still be fetched without
    requiring a physical read. Users interested in obtaining more
    detailed information on <productname>PostgreSQL</productname> I/O behavior are
-   advised to use the <productname>PostgreSQL</productname> statistics collector
+   advised to use the <productname>PostgreSQL</productname> activity statistics
    in combination with operating system utilities that allow insight
    into the kernel's handling of I/O.
   </para>
@@ -1060,10 +1051,6 @@ postgres   27093  0.0  0.0  30096  2752 ?        Ss   11:34   0:00 postgres: ser
       <entry><literal>LogicalLauncherMain</literal></entry>
       <entry>Waiting in main loop of logical replication launcher process.</entry>
      </row>
-     <row>
-      <entry><literal>PgStatMain</literal></entry>
-      <entry>Waiting in main loop of statistics collector process.</entry>
-     </row>
      <row>
       <entry><literal>RecoveryWalStream</literal></entry>
       <entry>Waiting in main loop of startup process for WAL to arrive, during
@@ -1788,6 +1775,10 @@ postgres   27093  0.0  0.0  30096  2752 ?        Ss   11:34   0:00 postgres: ser
     </thead>
 
     <tbody>
+     <row>
+      <entry><literal>ActivityStatistics</literal></entry>
+      <entry>Waiting to write out activity statistics to shared memory.</entry>
+     </row>
      <row>
       <entry><literal>AddinShmemInit</literal></entry>
       <entry>Waiting to manage an extension's space allocation in shared
@@ -5739,9 +5730,10 @@ SELECT pg_stat_get_backend_pid(s.backendid) AS pid,
      <entry><literal>performing final cleanup</literal></entry>
      <entry>
        <command>VACUUM</command> is performing final cleanup.  During this phase,
-       <command>VACUUM</command> will vacuum the free space map, update statistics
-       in <literal>pg_class</literal>, and report statistics to the statistics
-       collector.  When this phase is completed, <command>VACUUM</command> will end.
+       <command>VACUUM</command> will vacuum the free space map, update
+       statistics in <literal>pg_class</literal>, and system-wide activity
+       statistics.  When this phase is completed, <command>VACUUM</command>
+       will end.
      </entry>
     </row>
    </tbody>
diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml
index 2f0807e912..ce5c60de10 100644
--- a/doc/src/sgml/ref/pg_dump.sgml
+++ b/doc/src/sgml/ref/pg_dump.sgml
@@ -1287,11 +1287,10 @@ PostgreSQL documentation
   </para>
 
   <para>
-   The database activity of <application>pg_dump</application> is
-   normally collected by the statistics collector.  If this is
-   undesirable, you can set parameter <varname>track_counts</varname>
-   to false via <envar>PGOPTIONS</envar> or the <literal>ALTER
-   USER</literal> command.
+   The database activity of <application>pg_dump</application> is normally
+   collected.  If this is undesirable, you can set
+   parameter <varname>track_counts</varname> to false
+   via <envar>PGOPTIONS</envar> or the <literal>ALTER USER</literal> command.
   </para>
 
  </refsect1>
-- 
2.18.2


----Next_Part(Mon_Jun__8_17_32_04_2020_489)--
Content-Type: Text/X-Patch; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="v35-0007-Remove-the-GUC-stats_temp_directory.patch"



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* general purpose array_sort
@ 2024-09-27 13:15 Junwang Zhao <[email protected]>
  2024-09-28 11:52 ` Re: general purpose array_sort Junwang Zhao <[email protected]>
  0 siblings, 1 reply; 10+ messages in thread

From: Junwang Zhao @ 2024-09-27 13:15 UTC (permalink / raw)
  To: PostgreSQL Hackers <[email protected]>; +Cc: David G. Johnston <[email protected]>; [email protected] <[email protected]>; Robert Haas <[email protected]>; Amit Langote <[email protected]>

Hi hackers,

per David's suggestion, this patch implements general
purpose array sort.

We can do the following with this patch:

SELECT array_sort('{1.1,3.3,5.5,2.2,4.4,6.6}'::float8[], 'asc');
SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,ǄxxǄ ǆxxǅ
ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ}'::text[]);
SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,ǄxxǄ ǆxxǅ
ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ}'::text[], 'asc', 'pg_c_utf8');

-- 
Regards
Junwang Zhao


Attachments:

  [application/octet-stream] v1-0001-general-purpose-array_sort.patch (11.1K, ../../CAEG8a3J41a4dpw_-F94fF-JPRXYxw-GfsgoGotKcjs9LVfEEvw@mail.gmail.com/2-v1-0001-general-purpose-array_sort.patch)
  download | inline diff:
From 2981a7ce87277fec51533c9d7cd501919f8909ec Mon Sep 17 00:00:00 2001
From: Junwang Zhao <[email protected]>
Date: Fri, 27 Sep 2024 13:05:40 +0000
Subject: [PATCH v1] general purpose array_sort

Sorts anyarray in either ascending or descending order.
The array must be empty or one-dimensional.

Signed-off-by: Junwang Zhao <[email protected]>
---
 doc/src/sgml/func.sgml                  |  20 ++++
 src/backend/utils/adt/array_userfuncs.c | 120 ++++++++++++++++++++++++
 src/include/catalog/pg_proc.dat         |   9 ++
 src/test/regress/expected/arrays.out    |  67 +++++++++++++
 src/test/regress/sql/arrays.sql         |  13 +++
 5 files changed, 229 insertions(+)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index e39d524b6b..7441d413bd 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -20419,6 +20419,26 @@ SELECT NULLIF(value, '(none)') ...
        </para></entry>
       </row>
 
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>array_sort</primary>
+        </indexterm>
+        <function>array_sort</function> ( <type>anyarray</type> <optional>, <parameter>dir</parameter> </optional> <optional>, <parameter>collation</parameter> </optional>)
+        <returnvalue>anyarray</returnvalue>
+       </para>
+       <para>
+        Sorts the array in either ascending or descending order.
+        <parameter>dir</parameter> must be <literal>asc</literal>
+        or <literal>desc</literal>. The array must be empty or one-dimensional.
+        If the array element is text variants, specify <parameter>collation</parameter> as needed.
+       </para>
+       <para>
+        <literal>array_sort(ARRAY[1,2,5,6,3,4])</literal>
+        <returnvalue>{1,2,3,4,5,6}</returnvalue>
+       </para></entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm id="function-array-to-string">
diff --git a/src/backend/utils/adt/array_userfuncs.c b/src/backend/utils/adt/array_userfuncs.c
index 6599be2ec5..e507eb3dbb 100644
--- a/src/backend/utils/adt/array_userfuncs.c
+++ b/src/backend/utils/adt/array_userfuncs.c
@@ -12,15 +12,18 @@
  */
 #include "postgres.h"
 
+#include "catalog/namespace.h"
 #include "catalog/pg_type.h"
 #include "common/int.h"
 #include "common/pg_prng.h"
 #include "libpq/pqformat.h"
+#include "miscadmin.h"
 #include "port/pg_bitutils.h"
 #include "utils/array.h"
 #include "utils/builtins.h"
 #include "utils/datum.h"
 #include "utils/lsyscache.h"
+#include "utils/tuplesort.h"
 #include "utils/typcache.h"
 
 /*
@@ -1685,3 +1688,120 @@ array_sample(PG_FUNCTION_ARGS)
 
 	PG_RETURN_ARRAYTYPE_P(result);
 }
+
+/*
+ * array_sort
+ *
+ * Sorts the array in either ascending or descending order.
+ * The array must be empty or one-dimensional.
+ */
+Datum
+array_sort(PG_FUNCTION_ARGS)
+{
+	ArrayType  *array = PG_GETARG_ARRAYTYPE_P(0);
+	text	   *dirstr = (fcinfo->nargs > 1) ? PG_GETARG_TEXT_PP(1) : NULL;
+	int32		dc = (dirstr) ? VARSIZE_ANY_EXHDR(dirstr) : 0;
+	char	   *d = (dirstr) ? VARDATA_ANY(dirstr) : NULL;
+	Oid			elmtyp;
+	text	   *collstr = (fcinfo->nargs > 2) ? PG_GETARG_TEXT_PP(2) : NULL;
+	char	   *collname = (collstr) ? text_to_cstring(collstr) : NULL;
+	Oid			collid;
+	int			dir = -1;
+	TypeCacheEntry *typentry;
+	Tuplesortstate *tuplesortstate;
+	ArrayIterator array_iterator;
+	Datum		value;
+	bool		isnull;
+	ArrayBuildState *astate = NULL;
+
+	if (ARR_NDIM(array) > 1)
+		ereport(ERROR,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				 errmsg("multidimensional arrays are not supported")));
+
+	if (ARR_NDIM(array) < 1)
+		PG_RETURN_ARRAYTYPE_P(array);
+
+	if (ARR_HASNULL(array))
+		ereport(ERROR,
+				(errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+				 errmsg("array must not contain nulls")));
+
+	if (dirstr == NULL || (dc == 3
+						   && (d[0] == 'a' || d[0] == 'A')
+						   && (d[1] == 's' || d[1] == 'S')
+						   && (d[2] == 'c' || d[2] == 'C')))
+		dir = 1;
+	else if (dc == 4
+			 && (d[0] == 'd' || d[0] == 'D')
+			 && (d[1] == 'e' || d[1] == 'E')
+			 && (d[2] == 's' || d[2] == 'S')
+			 && (d[3] == 'c' || d[3] == 'C'))
+		dir = 0;
+	if (dir == -1)
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("second parameter must be \"ASC\" or \"DESC\"")));
+
+	elmtyp = ARR_ELEMTYPE(array);
+	typentry = (TypeCacheEntry *) fcinfo->flinfo->fn_extra;
+	if (typentry == NULL || typentry->type_id != elmtyp)
+	{
+		typentry = lookup_type_cache(elmtyp, dir == 1 ? TYPECACHE_LT_OPR : TYPECACHE_GT_OPR);
+		fcinfo->flinfo->fn_extra = (void *) typentry;
+	}
+
+	if (collname == NULL)
+		collid = typentry->typcollation;
+	else
+	{
+		collid = CollationGetCollid(collname);
+		if (!OidIsValid(collid))
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+					 errmsg("collation \"%s\" not found", collname)));
+	}
+
+	tuplesortstate = tuplesort_begin_datum(elmtyp,
+										   dir == 1 ? typentry->lt_opr : typentry->gt_opr,
+										   collid,
+										   false, work_mem, NULL, false);
+
+	array_iterator = array_create_iterator(array, 0, NULL);
+	while (array_iterate(array_iterator, &value, &isnull))
+	{
+		Assert(!isnull);
+		tuplesort_putdatum(tuplesortstate, value, isnull);
+	}
+	array_free_iterator(array_iterator);
+
+	/*
+	 * Do the sort.
+	 */
+	tuplesort_performsort(tuplesortstate);
+
+	while (tuplesort_getdatum(tuplesortstate, true, false, &value, &isnull, NULL))
+	{
+		Assert(!isnull);
+		astate = accumArrayResult(astate, value, false,
+								  elmtyp, CurrentMemoryContext);
+	}
+
+	tuplesort_end(tuplesortstate);
+
+	/* Avoid leaking memory when handed toasted input */
+	PG_FREE_IF_COPY(array, 0);
+	PG_RETURN_DATUM(makeArrayResult(astate, CurrentMemoryContext));
+}
+
+Datum
+array_sort_order(PG_FUNCTION_ARGS)
+{
+	return array_sort(fcinfo);
+}
+
+Datum
+array_sort_order_collation(PG_FUNCTION_ARGS)
+{
+	return array_sort(fcinfo);
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 43f608d7a0..1769a5d2c5 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -1734,6 +1734,15 @@
 { oid => '6216', descr => 'take samples from array',
   proname => 'array_sample', provolatile => 'v', prorettype => 'anyarray',
   proargtypes => 'anyarray int4', prosrc => 'array_sample' },
+{ oid => '8810', descr => 'sort array',
+  proname => 'array_sort', provolatile => 'v', prorettype => 'anyarray',
+  proargtypes => 'anyarray', prosrc => 'array_sort'},
+{ oid => '8811', descr => 'sort array',
+  proname => 'array_sort', provolatile => 'v', prorettype => 'anyarray',
+  proargtypes => 'anyarray text', prosrc => 'array_sort_order'},
+{ oid => '8812', descr => 'sort array',
+  proname => 'array_sort', provolatile => 'v', prorettype => 'anyarray',
+  proargtypes => 'anyarray text text', prosrc => 'array_sort_order_collation'},
 { oid => '3816', descr => 'array typanalyze',
   proname => 'array_typanalyze', provolatile => 's', prorettype => 'bool',
   proargtypes => 'internal', prosrc => 'array_typanalyze' },
diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out
index a6d81fd5f9..9326009188 100644
--- a/src/test/regress/expected/arrays.out
+++ b/src/test/regress/expected/arrays.out
@@ -2703,3 +2703,70 @@ SELECT array_sample('{1,2,3,4,5,6}'::int[], -1); -- fail
 ERROR:  sample size must be between 0 and 6
 SELECT array_sample('{1,2,3,4,5,6}'::int[], 7); --fail
 ERROR:  sample size must be between 0 and 6
+-- array_sort
+SELECT array_sort('{}'::int[]);
+ array_sort 
+------------
+ {}
+(1 row)
+
+SELECT array_sort('{1,3,5,2,4,6}'::int[]);
+  array_sort   
+---------------
+ {1,2,3,4,5,6}
+(1 row)
+
+SELECT array_sort('{1,3,5,2,4,6}'::int[], 'desc');
+  array_sort   
+---------------
+ {6,5,4,3,2,1}
+(1 row)
+
+SELECT array_sort('{1.1,3.3,5.5,2.2,4.4,6.6}'::float8[], 'asc');
+        array_sort         
+---------------------------
+ {1.1,2.2,3.3,4.4,5.5,6.6}
+(1 row)
+
+SELECT array_sort('{1.1,3.3,5.5,2.2,4.4,6.6}'::float8[], 'desc');
+        array_sort         
+---------------------------
+ {6.6,5.5,4.4,3.3,2.2,1.1}
+(1 row)
+
+SELECT array_sort('{1.1,3.3,5.5,2.2,4.4,6.6}'::numeric[]);
+        array_sort         
+---------------------------
+ {1.1,2.2,3.3,4.4,5.5,6.6}
+(1 row)
+
+SELECT array_sort('{1.1,3.3,5.5,2.2,4.4,6.6}'::numeric[], 'desc');
+        array_sort         
+---------------------------
+ {6.6,5.5,4.4,3.3,2.2,1.1}
+(1 row)
+
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ}'::text[]);
+                            array_sort                            
+------------------------------------------------------------------
+ {"abc DEF 123abc","ábc sßs ßss DÉF",ⱥȺ,ⱥⱥⱥ,ȺȺȺ,"ǄxxǄ ǆxxǅ ǅxxǆ"}
+(1 row)
+
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ}'::text[], 'desc');
+                            array_sort                            
+------------------------------------------------------------------
+ {"ǄxxǄ ǆxxǅ ǅxxǆ",ȺȺȺ,ⱥⱥⱥ,ⱥȺ,"ábc sßs ßss DÉF","abc DEF 123abc"}
+(1 row)
+
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ}'::text[], 'asc', 'pg_c_utf8');
+                            array_sort                            
+------------------------------------------------------------------
+ {"abc DEF 123abc","ábc sßs ßss DÉF","ǄxxǄ ǆxxǅ ǅxxǆ",ȺȺȺ,ⱥȺ,ⱥⱥⱥ}
+(1 row)
+
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ}'::text[], 'desc', 'pg_c_utf8');
+                            array_sort                            
+------------------------------------------------------------------
+ {ⱥⱥⱥ,ⱥȺ,ȺȺȺ,"ǄxxǄ ǆxxǅ ǅxxǆ","ábc sßs ßss DÉF","abc DEF 123abc"}
+(1 row)
+
diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql
index 47058dfde5..b9bf5fd294 100644
--- a/src/test/regress/sql/arrays.sql
+++ b/src/test/regress/sql/arrays.sql
@@ -827,3 +827,16 @@ SELECT array_dims(array_sample('[-1:2][2:3]={{1,2},{3,NULL},{5,6},{7,8}}'::int[]
 SELECT array_dims(array_sample('{{{1,2},{3,NULL}},{{5,6},{7,8}},{{9,10},{11,12}}}'::int[], 2));
 SELECT array_sample('{1,2,3,4,5,6}'::int[], -1); -- fail
 SELECT array_sample('{1,2,3,4,5,6}'::int[], 7); --fail
+
+-- array_sort
+SELECT array_sort('{}'::int[]);
+SELECT array_sort('{1,3,5,2,4,6}'::int[]);
+SELECT array_sort('{1,3,5,2,4,6}'::int[], 'desc');
+SELECT array_sort('{1.1,3.3,5.5,2.2,4.4,6.6}'::float8[], 'asc');
+SELECT array_sort('{1.1,3.3,5.5,2.2,4.4,6.6}'::float8[], 'desc');
+SELECT array_sort('{1.1,3.3,5.5,2.2,4.4,6.6}'::numeric[]);
+SELECT array_sort('{1.1,3.3,5.5,2.2,4.4,6.6}'::numeric[], 'desc');
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ}'::text[]);
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ}'::text[], 'desc');
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ}'::text[], 'asc', 'pg_c_utf8');
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ}'::text[], 'desc', 'pg_c_utf8');
-- 
2.39.5



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: general purpose array_sort
  2024-09-27 13:15 general purpose array_sort Junwang Zhao <[email protected]>
@ 2024-09-28 11:52 ` Junwang Zhao <[email protected]>
  2024-09-28 14:40   ` Re: general purpose array_sort jian he <[email protected]>
  0 siblings, 1 reply; 10+ messages in thread

From: Junwang Zhao @ 2024-09-28 11:52 UTC (permalink / raw)
  To: PostgreSQL Hackers <[email protected]>; +Cc: David G. Johnston <[email protected]>; [email protected] <[email protected]>; Robert Haas <[email protected]>; Amit Langote <[email protected]>

On Fri, Sep 27, 2024 at 9:15 PM Junwang Zhao <[email protected]> wrote:
>
> Hi hackers,
>
> per David's suggestion, this patch implements general
> purpose array sort.
>
> We can do the following with this patch:
>
> SELECT array_sort('{1.1,3.3,5.5,2.2,4.4,6.6}'::float8[], 'asc');
> SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,ǄxxǄ ǆxxǅ
> ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ}'::text[]);
> SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,ǄxxǄ ǆxxǅ
> ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ}'::text[], 'asc', 'pg_c_utf8');
>
> --
> Regards
> Junwang Zhao

PFA v2, use COLLATE keyword to supply the collation suggested by
Andreas offlist.

SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,ǄxxǄ ǆxxǅ
ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ}'::text[]);
SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,ǄxxǄ ǆxxǅ
ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ}'::text[] COLLATE "pg_c_utf8");

I also created a CF entry[1] so it can be easily reviewed.

[1]: https://commitfest.postgresql.org/50/5277/

-- 
Regards
Junwang Zhao


Attachments:

  [application/octet-stream] v2-0001-general-purpose-array_sort.patch (10.3K, ../../CAEG8a3KxjQR_soZDX89W8LCkNhVyLDff_MLP77kmbh10OCa4Wg@mail.gmail.com/2-v2-0001-general-purpose-array_sort.patch)
  download | inline diff:
From 95b3b6cde4c2312d25c3b6b26f44064abc61e371 Mon Sep 17 00:00:00 2001
From: Junwang Zhao <[email protected]>
Date: Fri, 27 Sep 2024 13:05:40 +0000
Subject: [PATCH v2] general purpose array_sort

Sorts anyarray in either ascending or descending order.
The array must be empty or one-dimensional.

Signed-off-by: Junwang Zhao <[email protected]>
---
 doc/src/sgml/func.sgml                  |  19 +++++
 src/backend/utils/adt/array_userfuncs.c | 102 ++++++++++++++++++++++++
 src/include/catalog/pg_proc.dat         |   6 ++
 src/test/regress/expected/arrays.out    |  67 ++++++++++++++++
 src/test/regress/sql/arrays.sql         |  13 +++
 5 files changed, 207 insertions(+)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index e39d524b6b..b517c7e35f 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -20419,6 +20419,25 @@ SELECT NULLIF(value, '(none)') ...
        </para></entry>
       </row>
 
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>array_sort</primary>
+        </indexterm>
+        <function>array_sort</function> ( <type>anyarray</type> <optional>, <parameter>dir</parameter> </optional>)
+        <returnvalue>anyarray</returnvalue>
+       </para>
+       <para>
+        Sorts the array in either ascending or descending order.
+        <parameter>dir</parameter> must be <literal>asc</literal>
+        or <literal>desc</literal>. The array must be empty or one-dimensional.
+       </para>
+       <para>
+        <literal>array_sort(ARRAY[1,2,5,6,3,4])</literal>
+        <returnvalue>{1,2,3,4,5,6}</returnvalue>
+       </para></entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm id="function-array-to-string">
diff --git a/src/backend/utils/adt/array_userfuncs.c b/src/backend/utils/adt/array_userfuncs.c
index 6599be2ec5..841fe435e1 100644
--- a/src/backend/utils/adt/array_userfuncs.c
+++ b/src/backend/utils/adt/array_userfuncs.c
@@ -12,15 +12,18 @@
  */
 #include "postgres.h"
 
+#include "catalog/namespace.h"
 #include "catalog/pg_type.h"
 #include "common/int.h"
 #include "common/pg_prng.h"
 #include "libpq/pqformat.h"
+#include "miscadmin.h"
 #include "port/pg_bitutils.h"
 #include "utils/array.h"
 #include "utils/builtins.h"
 #include "utils/datum.h"
 #include "utils/lsyscache.h"
+#include "utils/tuplesort.h"
 #include "utils/typcache.h"
 
 /*
@@ -1685,3 +1688,102 @@ array_sample(PG_FUNCTION_ARGS)
 
 	PG_RETURN_ARRAYTYPE_P(result);
 }
+
+/*
+ * array_sort
+ *
+ * Sorts the array in either ascending or descending order.
+ * The array must be empty or one-dimensional.
+ */
+Datum
+array_sort(PG_FUNCTION_ARGS)
+{
+	ArrayType  *array = PG_GETARG_ARRAYTYPE_P(0);
+	text	   *dirstr = (fcinfo->nargs > 1) ? PG_GETARG_TEXT_PP(1) : NULL;
+	int32		dc = (dirstr) ? VARSIZE_ANY_EXHDR(dirstr) : 0;
+	char	   *d = (dirstr) ? VARDATA_ANY(dirstr) : NULL;
+	Oid			elmtyp;
+	Oid			collation = PG_GET_COLLATION();
+	int			dir = -1;
+	TypeCacheEntry *typentry;
+	Tuplesortstate *tuplesortstate;
+	ArrayIterator array_iterator;
+	Datum		value;
+	bool		isnull;
+	ArrayBuildState *astate = NULL;
+
+	if (ARR_NDIM(array) > 1)
+		ereport(ERROR,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				 errmsg("multidimensional arrays are not supported")));
+
+	if (ARR_NDIM(array) < 1)
+		PG_RETURN_ARRAYTYPE_P(array);
+
+	if (ARR_HASNULL(array))
+		ereport(ERROR,
+				(errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+				 errmsg("array must not contain nulls")));
+
+	if (dirstr == NULL || (dc == 3
+						   && (d[0] == 'a' || d[0] == 'A')
+						   && (d[1] == 's' || d[1] == 'S')
+						   && (d[2] == 'c' || d[2] == 'C')))
+		dir = 1;
+	else if (dc == 4
+			 && (d[0] == 'd' || d[0] == 'D')
+			 && (d[1] == 'e' || d[1] == 'E')
+			 && (d[2] == 's' || d[2] == 'S')
+			 && (d[3] == 'c' || d[3] == 'C'))
+		dir = 0;
+	if (dir == -1)
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("second parameter must be \"ASC\" or \"DESC\"")));
+
+	elmtyp = ARR_ELEMTYPE(array);
+	typentry = (TypeCacheEntry *) fcinfo->flinfo->fn_extra;
+	if (typentry == NULL || typentry->type_id != elmtyp)
+	{
+		typentry = lookup_type_cache(elmtyp, dir == 1 ? TYPECACHE_LT_OPR : TYPECACHE_GT_OPR);
+		fcinfo->flinfo->fn_extra = (void *) typentry;
+	}
+
+	tuplesortstate = tuplesort_begin_datum(elmtyp,
+										   dir == 1 ? typentry->lt_opr : typentry->gt_opr,
+										   collation,
+										   false, work_mem, NULL, false);
+
+	array_iterator = array_create_iterator(array, 0, NULL);
+	while (array_iterate(array_iterator, &value, &isnull))
+	{
+		Assert(!isnull);
+		tuplesort_putdatum(tuplesortstate, value, isnull);
+	}
+	array_free_iterator(array_iterator);
+
+	/*
+	 * Do the sort.
+	 */
+	tuplesort_performsort(tuplesortstate);
+
+	while (tuplesort_getdatum(tuplesortstate, true, false, &value, &isnull, NULL))
+	{
+		Assert(!isnull);
+		astate = accumArrayResult(astate, value, false,
+								  elmtyp, CurrentMemoryContext);
+	}
+
+	tuplesort_end(tuplesortstate);
+
+	/* Avoid leaking memory when handed toasted input */
+	PG_FREE_IF_COPY(array, 0);
+	PG_RETURN_DATUM(makeArrayResult(astate, CurrentMemoryContext));
+}
+
+Datum
+array_sort_order(PG_FUNCTION_ARGS)
+{
+	return array_sort(fcinfo);
+}
+
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 43f608d7a0..1b2d64ac39 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -1734,6 +1734,12 @@
 { oid => '6216', descr => 'take samples from array',
   proname => 'array_sample', provolatile => 'v', prorettype => 'anyarray',
   proargtypes => 'anyarray int4', prosrc => 'array_sample' },
+{ oid => '8810', descr => 'sort array',
+  proname => 'array_sort', provolatile => 'v', prorettype => 'anyarray',
+  proargtypes => 'anyarray', prosrc => 'array_sort'},
+{ oid => '8811', descr => 'sort array',
+  proname => 'array_sort', provolatile => 'v', prorettype => 'anyarray',
+  proargtypes => 'anyarray text', prosrc => 'array_sort_order'},
 { oid => '3816', descr => 'array typanalyze',
   proname => 'array_typanalyze', provolatile => 's', prorettype => 'bool',
   proargtypes => 'internal', prosrc => 'array_typanalyze' },
diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out
index a6d81fd5f9..d9dc11d98c 100644
--- a/src/test/regress/expected/arrays.out
+++ b/src/test/regress/expected/arrays.out
@@ -2703,3 +2703,70 @@ SELECT array_sample('{1,2,3,4,5,6}'::int[], -1); -- fail
 ERROR:  sample size must be between 0 and 6
 SELECT array_sample('{1,2,3,4,5,6}'::int[], 7); --fail
 ERROR:  sample size must be between 0 and 6
+-- array_sort
+SELECT array_sort('{}'::int[]);
+ array_sort 
+------------
+ {}
+(1 row)
+
+SELECT array_sort('{1,3,5,2,4,6}'::int[]);
+  array_sort   
+---------------
+ {1,2,3,4,5,6}
+(1 row)
+
+SELECT array_sort('{1,3,5,2,4,6}'::int[], 'desc');
+  array_sort   
+---------------
+ {6,5,4,3,2,1}
+(1 row)
+
+SELECT array_sort('{1.1,3.3,5.5,2.2,4.4,6.6}'::float8[], 'asc');
+        array_sort         
+---------------------------
+ {1.1,2.2,3.3,4.4,5.5,6.6}
+(1 row)
+
+SELECT array_sort('{1.1,3.3,5.5,2.2,4.4,6.6}'::float8[], 'desc');
+        array_sort         
+---------------------------
+ {6.6,5.5,4.4,3.3,2.2,1.1}
+(1 row)
+
+SELECT array_sort('{1.1,3.3,5.5,2.2,4.4,6.6}'::numeric[]);
+        array_sort         
+---------------------------
+ {1.1,2.2,3.3,4.4,5.5,6.6}
+(1 row)
+
+SELECT array_sort('{1.1,3.3,5.5,2.2,4.4,6.6}'::numeric[], 'desc');
+        array_sort         
+---------------------------
+ {6.6,5.5,4.4,3.3,2.2,1.1}
+(1 row)
+
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ}'::text[]);
+                            array_sort                            
+------------------------------------------------------------------
+ {"abc DEF 123abc","ábc sßs ßss DÉF",ⱥȺ,ⱥⱥⱥ,ȺȺȺ,"ǄxxǄ ǆxxǅ ǅxxǆ"}
+(1 row)
+
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ}'::text[], 'desc');
+                            array_sort                            
+------------------------------------------------------------------
+ {"ǄxxǄ ǆxxǅ ǅxxǆ",ȺȺȺ,ⱥⱥⱥ,ⱥȺ,"ábc sßs ßss DÉF","abc DEF 123abc"}
+(1 row)
+
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ}'::text[] COLLATE "pg_c_utf8", 'asc');
+                            array_sort                            
+------------------------------------------------------------------
+ {"abc DEF 123abc","ábc sßs ßss DÉF","ǄxxǄ ǆxxǅ ǅxxǆ",ȺȺȺ,ⱥȺ,ⱥⱥⱥ}
+(1 row)
+
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ}'::text[] COLLATE "pg_c_utf8", 'desc');
+                            array_sort                            
+------------------------------------------------------------------
+ {ⱥⱥⱥ,ⱥȺ,ȺȺȺ,"ǄxxǄ ǆxxǅ ǅxxǆ","ábc sßs ßss DÉF","abc DEF 123abc"}
+(1 row)
+
diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql
index 47058dfde5..3bb13b49de 100644
--- a/src/test/regress/sql/arrays.sql
+++ b/src/test/regress/sql/arrays.sql
@@ -827,3 +827,16 @@ SELECT array_dims(array_sample('[-1:2][2:3]={{1,2},{3,NULL},{5,6},{7,8}}'::int[]
 SELECT array_dims(array_sample('{{{1,2},{3,NULL}},{{5,6},{7,8}},{{9,10},{11,12}}}'::int[], 2));
 SELECT array_sample('{1,2,3,4,5,6}'::int[], -1); -- fail
 SELECT array_sample('{1,2,3,4,5,6}'::int[], 7); --fail
+
+-- array_sort
+SELECT array_sort('{}'::int[]);
+SELECT array_sort('{1,3,5,2,4,6}'::int[]);
+SELECT array_sort('{1,3,5,2,4,6}'::int[], 'desc');
+SELECT array_sort('{1.1,3.3,5.5,2.2,4.4,6.6}'::float8[], 'asc');
+SELECT array_sort('{1.1,3.3,5.5,2.2,4.4,6.6}'::float8[], 'desc');
+SELECT array_sort('{1.1,3.3,5.5,2.2,4.4,6.6}'::numeric[]);
+SELECT array_sort('{1.1,3.3,5.5,2.2,4.4,6.6}'::numeric[], 'desc');
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ}'::text[]);
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ}'::text[], 'desc');
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ}'::text[] COLLATE "pg_c_utf8", 'asc');
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ}'::text[] COLLATE "pg_c_utf8", 'desc');
-- 
2.39.5



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: general purpose array_sort
  2024-09-27 13:15 general purpose array_sort Junwang Zhao <[email protected]>
  2024-09-28 11:52 ` Re: general purpose array_sort Junwang Zhao <[email protected]>
@ 2024-09-28 14:40   ` jian he <[email protected]>
  2024-09-29 02:05     ` Re: general purpose array_sort Junwang Zhao <[email protected]>
  0 siblings, 1 reply; 10+ messages in thread

From: jian he @ 2024-09-28 14:40 UTC (permalink / raw)
  To: Junwang Zhao <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; David G. Johnston <[email protected]>; [email protected] <[email protected]>; Robert Haas <[email protected]>; Amit Langote <[email protected]>

On Sat, Sep 28, 2024 at 7:52 PM Junwang Zhao <[email protected]> wrote:
>
> PFA v2, use COLLATE keyword to supply the collation suggested by
> Andreas offlist.
>
this is better. otherwise we need extra care to handle case like:
SELECT array_sort('{1,3,5,2,4,6}'::int[] COLLATE "pg_c_utf8");

+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>array_sort</primary>
+        </indexterm>
+        <function>array_sort</function> ( <type>anyarray</type>
<optional>, <parameter>dir</parameter> </optional>)
+        <returnvalue>anyarray</returnvalue>
+       </para>
+       <para>
+        Sorts the array in either ascending or descending order.
+        <parameter>dir</parameter> must be <literal>asc</literal>
+        or <literal>desc</literal>. The array must be empty or one-dimensional.
+       </para>
+       <para>
+        <literal>array_sort(ARRAY[1,2,5,6,3,4])</literal>
+        <returnvalue>{1,2,3,4,5,6}</returnvalue>
+       </para></entry>
+      </row>
I am confused with <parameter>dir</parameter>. I guess you want to say
"direction"
But here, I think <parameter>sort_asc</parameter> would be more appropriate?

<parameter>dir</parameter> can have only two potential values, make it
as a boolean would be more easier?
you didn't mention information:  "by default, it will sort by
ascending order; the sort collation by default is using the array
element type's collation"

tuplesort_begin_datum can do null-first, null-last, so the
one-dimension array can allow null values.

Based on the above and others, I did some refactoring, feel free to take it.
my changes, changed the function signature, so you need to pay
attention to sql test file.

Attachments:

  [application/octet-stream] array_sort_changes.no-cfbot (10.4K, ../../CACJufxF-oQQUxwDOPp96iiyRh7_TQgbVwqA-Qa1hyVL1czSzSw@mail.gmail.com/2-array_sort_changes.no-cfbot)
  download

^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: general purpose array_sort
  2024-09-27 13:15 general purpose array_sort Junwang Zhao <[email protected]>
  2024-09-28 11:52 ` Re: general purpose array_sort Junwang Zhao <[email protected]>
  2024-09-28 14:40   ` Re: general purpose array_sort jian he <[email protected]>
@ 2024-09-29 02:05     ` Junwang Zhao <[email protected]>
  2024-09-29 02:50       ` Re: general purpose array_sort David G. Johnston <[email protected]>
  0 siblings, 1 reply; 10+ messages in thread

From: Junwang Zhao @ 2024-09-29 02:05 UTC (permalink / raw)
  To: jian he <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; David G. Johnston <[email protected]>; [email protected] <[email protected]>; Robert Haas <[email protected]>; Amit Langote <[email protected]>

On Sat, Sep 28, 2024 at 10:41 PM jian he <[email protected]> wrote:
>
> On Sat, Sep 28, 2024 at 7:52 PM Junwang Zhao <[email protected]> wrote:
> >
> > PFA v2, use COLLATE keyword to supply the collation suggested by
> > Andreas offlist.
> >
> this is better. otherwise we need extra care to handle case like:
> SELECT array_sort('{1,3,5,2,4,6}'::int[] COLLATE "pg_c_utf8");
>
>
> +      <row>
> +       <entry role="func_table_entry"><para role="func_signature">
> +        <indexterm>
> +         <primary>array_sort</primary>
> +        </indexterm>
> +        <function>array_sort</function> ( <type>anyarray</type>
> <optional>, <parameter>dir</parameter> </optional>)
> +        <returnvalue>anyarray</returnvalue>
> +       </para>
> +       <para>
> +        Sorts the array in either ascending or descending order.
> +        <parameter>dir</parameter> must be <literal>asc</literal>
> +        or <literal>desc</literal>. The array must be empty or one-dimensional.
> +       </para>
> +       <para>
> +        <literal>array_sort(ARRAY[1,2,5,6,3,4])</literal>
> +        <returnvalue>{1,2,3,4,5,6}</returnvalue>
> +       </para></entry>
> +      </row>
> I am confused with <parameter>dir</parameter>. I guess you want to say
> "direction"
> But here, I think <parameter>sort_asc</parameter> would be more appropriate?

This doc is mostly copied and edited from intarray.sgml sort part.

And the logic is basically the same, you can check the intarray module.

>
>
> <parameter>dir</parameter> can have only two potential values, make it
> as a boolean would be more easier?
> you didn't mention information:  "by default, it will sort by
> ascending order; the sort collation by default is using the array
> element type's collation"
>
> tuplesort_begin_datum can do null-first, null-last, so the
> one-dimension array can allow null values.

The following(create extension intarry first) will give an error, I
keep the same for array_sort.

SELECT sort('{1234234,-30,234234, null}');

>
> Based on the above and others, I did some refactoring, feel free to take it.
> my changes, changed the function signature, so you need to pay
> attention to sql test file.

Thanks for your refactor, I will take some in the next version.


-- 
Regards
Junwang Zhao






^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: general purpose array_sort
  2024-09-27 13:15 general purpose array_sort Junwang Zhao <[email protected]>
  2024-09-28 11:52 ` Re: general purpose array_sort Junwang Zhao <[email protected]>
  2024-09-28 14:40   ` Re: general purpose array_sort jian he <[email protected]>
  2024-09-29 02:05     ` Re: general purpose array_sort Junwang Zhao <[email protected]>
@ 2024-09-29 02:50       ` David G. Johnston <[email protected]>
  2024-09-30 05:01         ` Re: general purpose array_sort Junwang Zhao <[email protected]>
  0 siblings, 1 reply; 10+ messages in thread

From: David G. Johnston @ 2024-09-29 02:50 UTC (permalink / raw)
  To: Junwang Zhao <[email protected]>; +Cc: jian he <[email protected]>; PostgreSQL Hackers <[email protected]>; [email protected] <[email protected]>; Robert Haas <[email protected]>; Amit Langote <[email protected]>

On Sat, Sep 28, 2024 at 7:05 PM Junwang Zhao <[email protected]> wrote:

> On Sat, Sep 28, 2024 at 10:41 PM jian he <[email protected]>
> wrote:
> >
> > <parameter>dir</parameter> can have only two potential values, make it
> > as a boolean would be more easier?
> > you didn't mention information:  "by default, it will sort by
> > ascending order; the sort collation by default is using the array
> > element type's collation"
> >
> > tuplesort_begin_datum can do null-first, null-last, so the
> > one-dimension array can allow null values.
>
> The following(create extension intarry first) will give an error, I
> keep the same for array_sort.
>
> SELECT sort('{1234234,-30,234234, null}');
>
>
I would suggest accepting:
asc
desc
asc nulls first
asc nulls last *
desc nulls first *
desc nulls last

As valid inputs for "dir" - and that the starred options are the defaults
when null position is omitted.

In short, mimic create index.

David J.


^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: general purpose array_sort
  2024-09-27 13:15 general purpose array_sort Junwang Zhao <[email protected]>
  2024-09-28 11:52 ` Re: general purpose array_sort Junwang Zhao <[email protected]>
  2024-09-28 14:40   ` Re: general purpose array_sort jian he <[email protected]>
  2024-09-29 02:05     ` Re: general purpose array_sort Junwang Zhao <[email protected]>
  2024-09-29 02:50       ` Re: general purpose array_sort David G. Johnston <[email protected]>
@ 2024-09-30 05:01         ` Junwang Zhao <[email protected]>
  2024-09-30 15:13           ` Re: general purpose array_sort jian he <[email protected]>
  0 siblings, 1 reply; 10+ messages in thread

From: Junwang Zhao @ 2024-09-30 05:01 UTC (permalink / raw)
  To: David G. Johnston <[email protected]>; +Cc: jian he <[email protected]>; PostgreSQL Hackers <[email protected]>; [email protected] <[email protected]>; Robert Haas <[email protected]>; Amit Langote <[email protected]>

On Sun, Sep 29, 2024 at 10:51 AM David G. Johnston
<[email protected]> wrote:
>
> On Sat, Sep 28, 2024 at 7:05 PM Junwang Zhao <[email protected]> wrote:
>>
>> On Sat, Sep 28, 2024 at 10:41 PM jian he <[email protected]> wrote:
>> >
>> > <parameter>dir</parameter> can have only two potential values, make it
>> > as a boolean would be more easier?
>> > you didn't mention information:  "by default, it will sort by
>> > ascending order; the sort collation by default is using the array
>> > element type's collation"
>> >
>> > tuplesort_begin_datum can do null-first, null-last, so the
>> > one-dimension array can allow null values.
>>
>> The following(create extension intarry first) will give an error, I
>> keep the same for array_sort.
>>
>> SELECT sort('{1234234,-30,234234, null}');
>>
>
> I would suggest accepting:
> asc
> desc
> asc nulls first
> asc nulls last *
> desc nulls first *
> desc nulls last
>
> As valid inputs for "dir" - and that the starred options are the defaults when null position is omitted.
>
> In short, mimic create index.
>
> David J.
>

PFA v3 with David's suggestion addressed.

-- 
Regards
Junwang Zhao


Attachments:

  [application/octet-stream] v3-0001-general-purpose-array_sort.patch (17.7K, ../../CAEG8a3+B=QpHWuLc3OqyF9GxzNPofQBwv9k3rdZty+-LYoYCJA@mail.gmail.com/2-v3-0001-general-purpose-array_sort.patch)
  download | inline diff:
From 8f5678e921cfc128749d8be9203d544dca033697 Mon Sep 17 00:00:00 2001
From: Junwang Zhao <[email protected]>
Date: Fri, 27 Sep 2024 13:05:40 +0000
Subject: [PATCH v3] general purpose array_sort

Sorts anyarray in either ascending or descending order.
The array must be empty or one-dimensional.

Signed-off-by: Junwang Zhao <[email protected]>
---
 doc/src/sgml/func.sgml                  |  32 +++++
 src/backend/utils/adt/array_userfuncs.c | 180 ++++++++++++++++++++++++
 src/include/catalog/pg_proc.dat         |   6 +
 src/test/regress/expected/arrays.out    | 122 ++++++++++++++++
 src/test/regress/sql/arrays.sql         |  23 +++
 src/tools/pgindent/typedefs.list        |   1 +
 6 files changed, 364 insertions(+)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index e39d524b6b..cf30484e2a 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -20419,6 +20419,38 @@ SELECT NULLIF(value, '(none)') ...
        </para></entry>
       </row>
 
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>array_sort</primary>
+        </indexterm>
+        <function>array_sort</function> ( <type>anyarray</type> <optional> COLLATE <replaceable>collation_name</replaceable> </optional> <optional> , <parameter>dir</parameter> </optional>)
+        <returnvalue>anyarray</returnvalue>
+       </para>
+       <para>
+        Sorts the array based on the given parameter. The array must be empty or one-dimensional.
+       </para>
+       <para>
+        If the <literal>COLLATE</literal> option is specified then sorting is based on <replaceable>collation_name</replaceable>, otherwise
+        using array element type's collation.
+        <parameter>dir</parameter> must be one of the following, and that the starred options are the defaults when null position is omitted.
+<programlisting>
+ asc
+ desc
+ asc nulls first
+ asc nulls last *
+ desc nulls first *
+ desc nulls last
+ nulls first
+ nulls last
+</programlisting>
+       </para>
+       <para>
+        <literal>array_sort(ARRAY[1,2,5,6,3,4])</literal>
+        <returnvalue>{1,2,3,4,5,6}</returnvalue>
+       </para></entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm id="function-array-to-string">
diff --git a/src/backend/utils/adt/array_userfuncs.c b/src/backend/utils/adt/array_userfuncs.c
index 6599be2ec5..0ff1a4f6f4 100644
--- a/src/backend/utils/adt/array_userfuncs.c
+++ b/src/backend/utils/adt/array_userfuncs.c
@@ -12,15 +12,18 @@
  */
 #include "postgres.h"
 
+#include "catalog/namespace.h"
 #include "catalog/pg_type.h"
 #include "common/int.h"
 #include "common/pg_prng.h"
 #include "libpq/pqformat.h"
+#include "miscadmin.h"
 #include "port/pg_bitutils.h"
 #include "utils/array.h"
 #include "utils/builtins.h"
 #include "utils/datum.h"
 #include "utils/lsyscache.h"
+#include "utils/tuplesort.h"
 #include "utils/typcache.h"
 
 /*
@@ -1685,3 +1688,180 @@ array_sample(PG_FUNCTION_ARGS)
 
 	PG_RETURN_ARRAYTYPE_P(result);
 }
+
+
+#define WHITESPACE " \t\n\r"
+
+typedef enum
+{
+	PARSE_SORT_ORDER_INIT,
+	PARSE_SORT_ORDER_DIRECTION_SET,
+	PARSE_SORT_ORDER_NULLS_OPTION,
+	PARSE_SORT_ORDER_ERROR,
+	PARSE_SORT_ORDER_DONE
+} ParseSortOrderState;
+
+static bool
+parse_sort_order(const char *str, bool *sort_asc, bool *nulls_first)
+{
+	char	   *token;
+	char	   *saveptr;
+	char	   *str_copy = pstrdup(str);
+	bool		nulls_first_set = false;
+	ParseSortOrderState state = PARSE_SORT_ORDER_INIT;
+
+	token = strtok_r(str_copy, WHITESPACE, &saveptr);
+
+	while (token != NULL && state != PARSE_SORT_ORDER_ERROR)
+	{
+		switch (state)
+		{
+			case PARSE_SORT_ORDER_INIT:
+				if (pg_strcasecmp(token, "ASC") == 0)
+				{
+					*sort_asc = true;
+					state = PARSE_SORT_ORDER_DIRECTION_SET;
+				}
+				else if (pg_strcasecmp(token, "DESC") == 0)
+				{
+					*sort_asc = false;
+					state = PARSE_SORT_ORDER_DIRECTION_SET;
+				}
+				else if (pg_strcasecmp(token, "NULLS") == 0)
+					state = PARSE_SORT_ORDER_NULLS_OPTION;
+				else
+					state = PARSE_SORT_ORDER_ERROR;
+				break;
+
+			case PARSE_SORT_ORDER_DIRECTION_SET:
+				if (pg_strcasecmp(token, "NULLS") == 0)
+					state = PARSE_SORT_ORDER_NULLS_OPTION;
+				else
+					state = PARSE_SORT_ORDER_ERROR;
+				break;
+
+			case PARSE_SORT_ORDER_NULLS_OPTION:
+				if (pg_strcasecmp(token, "FIRST") == 0)
+				{
+					*nulls_first = true;
+					nulls_first_set = true;
+					state = PARSE_SORT_ORDER_DONE;
+				}
+				else if (pg_strcasecmp(token, "LAST") == 0)
+				{
+					*nulls_first = false;
+					nulls_first_set = true;
+					state = PARSE_SORT_ORDER_DONE;
+				}
+				else
+					state = PARSE_SORT_ORDER_ERROR;
+				break;
+
+			case PARSE_SORT_ORDER_DONE:
+				/* No more tokens should be processed after first/last */
+				state = PARSE_SORT_ORDER_ERROR;
+				break;
+
+			default:
+				state = PARSE_SORT_ORDER_ERROR;
+				break;
+		}
+
+		token = strtok_r(NULL, WHITESPACE, &saveptr);
+	}
+
+	if (state == PARSE_SORT_ORDER_INIT ||
+		state == PARSE_SORT_ORDER_DIRECTION_SET)
+		state = PARSE_SORT_ORDER_DONE;
+
+	if (state == PARSE_SORT_ORDER_NULLS_OPTION)
+		state = PARSE_SORT_ORDER_ERROR;
+
+	if (!nulls_first_set && state == PARSE_SORT_ORDER_DONE)
+		*nulls_first = !*sort_asc;
+
+	pfree(str_copy);
+	return state == PARSE_SORT_ORDER_DONE;
+}
+
+/*
+ * array_sort
+ *
+ * Sorts the array in either ascending or descending order.
+ * The array must be empty or one-dimensional.
+ */
+Datum
+array_sort(PG_FUNCTION_ARGS)
+{
+	ArrayType  *array = PG_GETARG_ARRAYTYPE_P(0);
+	text	   *dirstr = (fcinfo->nargs > 1) ? PG_GETARG_TEXT_PP(1) : NULL;
+	bool		sort_asc = true;
+	bool		nulls_first = false;
+	Oid			elmtyp;
+	Oid			collation = PG_GET_COLLATION();
+	TypeCacheEntry *typentry;
+	Tuplesortstate *tuplesortstate;
+	ArrayIterator array_iterator;
+	Datum		value;
+	bool		isnull;
+	ArrayBuildState *astate = NULL;
+
+	if (ARR_NDIM(array) > 1)
+		ereport(ERROR,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				 errmsg("multidimensional arrays sorting are not supported")));
+
+	if (ARR_NDIM(array) < 1)
+		PG_RETURN_ARRAYTYPE_P(array);
+
+	if (dirstr != NULL)
+	{
+		if (!parse_sort_order(text_to_cstring(dirstr), &sort_asc, &nulls_first))
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+					 errmsg("second parameter must be a valid sort direction")));
+	}
+
+	elmtyp = ARR_ELEMTYPE(array);
+	typentry = (TypeCacheEntry *) fcinfo->flinfo->fn_extra;
+	if (typentry == NULL || typentry->type_id != elmtyp)
+	{
+		typentry = lookup_type_cache(elmtyp, sort_asc ? TYPECACHE_LT_OPR : TYPECACHE_GT_OPR);
+		fcinfo->flinfo->fn_extra = (void *) typentry;
+	}
+
+	tuplesortstate = tuplesort_begin_datum(elmtyp,
+										   sort_asc ? typentry->lt_opr : typentry->gt_opr,
+										   collation,
+										   nulls_first, work_mem, NULL, false);
+
+	array_iterator = array_create_iterator(array, 0, NULL);
+	while (array_iterate(array_iterator, &value, &isnull))
+	{
+		tuplesort_putdatum(tuplesortstate, value, isnull);
+	}
+	array_free_iterator(array_iterator);
+
+	/*
+	 * Do the sort.
+	 */
+	tuplesort_performsort(tuplesortstate);
+
+	while (tuplesort_getdatum(tuplesortstate, true, false, &value, &isnull, NULL))
+	{
+		astate = accumArrayResult(astate, value, isnull,
+								  elmtyp, CurrentMemoryContext);
+	}
+
+	tuplesort_end(tuplesortstate);
+
+	/* Avoid leaking memory when handed toasted input */
+	PG_FREE_IF_COPY(array, 0);
+	PG_RETURN_DATUM(makeArrayResult(astate, CurrentMemoryContext));
+}
+
+Datum
+array_sort_order(PG_FUNCTION_ARGS)
+{
+	return array_sort(fcinfo);
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 43f608d7a0..1b2d64ac39 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -1734,6 +1734,12 @@
 { oid => '6216', descr => 'take samples from array',
   proname => 'array_sample', provolatile => 'v', prorettype => 'anyarray',
   proargtypes => 'anyarray int4', prosrc => 'array_sample' },
+{ oid => '8810', descr => 'sort array',
+  proname => 'array_sort', provolatile => 'v', prorettype => 'anyarray',
+  proargtypes => 'anyarray', prosrc => 'array_sort'},
+{ oid => '8811', descr => 'sort array',
+  proname => 'array_sort', provolatile => 'v', prorettype => 'anyarray',
+  proargtypes => 'anyarray text', prosrc => 'array_sort_order'},
 { oid => '3816', descr => 'array typanalyze',
   proname => 'array_typanalyze', provolatile => 's', prorettype => 'bool',
   proargtypes => 'internal', prosrc => 'array_typanalyze' },
diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out
index a6d81fd5f9..9c4116cf04 100644
--- a/src/test/regress/expected/arrays.out
+++ b/src/test/regress/expected/arrays.out
@@ -2703,3 +2703,125 @@ SELECT array_sample('{1,2,3,4,5,6}'::int[], -1); -- fail
 ERROR:  sample size must be between 0 and 6
 SELECT array_sample('{1,2,3,4,5,6}'::int[], 7); --fail
 ERROR:  sample size must be between 0 and 6
+-- array_sort
+SELECT array_sort('{}'::int[]);
+ array_sort 
+------------
+ {}
+(1 row)
+
+SELECT array_sort('{1,3,5,2,4,6}'::int[]);
+  array_sort   
+---------------
+ {1,2,3,4,5,6}
+(1 row)
+
+SELECT array_sort('{1,3,5,2,4,6}'::int[], 'desc');
+  array_sort   
+---------------
+ {6,5,4,3,2,1}
+(1 row)
+
+SELECT array_sort('{1.1,3.3,5.5,2.2,4.4,6.6}'::float8[], 'asc');
+        array_sort         
+---------------------------
+ {1.1,2.2,3.3,4.4,5.5,6.6}
+(1 row)
+
+SELECT array_sort('{1.1,3.3,5.5,2.2,4.4,6.6}'::float8[], 'desc');
+        array_sort         
+---------------------------
+ {6.6,5.5,4.4,3.3,2.2,1.1}
+(1 row)
+
+SELECT array_sort('{1.1,3.3,5.5,2.2,4.4,6.6}'::numeric[]);
+        array_sort         
+---------------------------
+ {1.1,2.2,3.3,4.4,5.5,6.6}
+(1 row)
+
+SELECT array_sort('{1.1,3.3,5.5,2.2,4.4,6.6}'::numeric[], 'desc');
+        array_sort         
+---------------------------
+ {6.6,5.5,4.4,3.3,2.2,1.1}
+(1 row)
+
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ}'::text[]);
+                            array_sort                            
+------------------------------------------------------------------
+ {"abc DEF 123abc","ábc sßs ßss DÉF",ⱥȺ,ⱥⱥⱥ,ȺȺȺ,"ǄxxǄ ǆxxǅ ǅxxǆ"}
+(1 row)
+
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ}'::text[], 'desc');
+                            array_sort                            
+------------------------------------------------------------------
+ {"ǄxxǄ ǆxxǅ ǅxxǆ",ȺȺȺ,ⱥⱥⱥ,ⱥȺ,"ábc sßs ßss DÉF","abc DEF 123abc"}
+(1 row)
+
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ}'::text[] COLLATE "pg_c_utf8", 'asc');
+                            array_sort                            
+------------------------------------------------------------------
+ {"abc DEF 123abc","ábc sßs ßss DÉF","ǄxxǄ ǆxxǅ ǅxxǆ",ȺȺȺ,ⱥȺ,ⱥⱥⱥ}
+(1 row)
+
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ}'::text[] COLLATE "pg_c_utf8", 'desc');
+                            array_sort                            
+------------------------------------------------------------------
+ {ⱥⱥⱥ,ⱥȺ,ȺȺȺ,"ǄxxǄ ǆxxǅ ǅxxǆ","ábc sßs ßss DÉF","abc DEF 123abc"}
+(1 row)
+
+-- nulls first/last tests
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,null,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ,null}'::text[]);
+                                 array_sort                                 
+----------------------------------------------------------------------------
+ {"abc DEF 123abc","ábc sßs ßss DÉF",ⱥȺ,ⱥⱥⱥ,ȺȺȺ,"ǄxxǄ ǆxxǅ ǅxxǆ",NULL,NULL}
+(1 row)
+
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,null,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ,null}'::text[], 'asc');
+                                 array_sort                                 
+----------------------------------------------------------------------------
+ {"abc DEF 123abc","ábc sßs ßss DÉF",ⱥȺ,ⱥⱥⱥ,ȺȺȺ,"ǄxxǄ ǆxxǅ ǅxxǆ",NULL,NULL}
+(1 row)
+
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,null,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ,null}'::text[], 'desc');
+                                 array_sort                                 
+----------------------------------------------------------------------------
+ {NULL,NULL,"ǄxxǄ ǆxxǅ ǅxxǆ",ȺȺȺ,ⱥⱥⱥ,ⱥȺ,"ábc sßs ßss DÉF","abc DEF 123abc"}
+(1 row)
+
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,null,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ,null}'::text[], 'nulls first');
+                                 array_sort                                 
+----------------------------------------------------------------------------
+ {NULL,NULL,"abc DEF 123abc","ábc sßs ßss DÉF",ⱥȺ,ⱥⱥⱥ,ȺȺȺ,"ǄxxǄ ǆxxǅ ǅxxǆ"}
+(1 row)
+
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,null,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ,null}'::text[], 'nulls first');
+                                 array_sort                                 
+----------------------------------------------------------------------------
+ {NULL,NULL,"abc DEF 123abc","ábc sßs ßss DÉF",ⱥȺ,ⱥⱥⱥ,ȺȺȺ,"ǄxxǄ ǆxxǅ ǅxxǆ"}
+(1 row)
+
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,null,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ,null}'::text[], 'asc nulls first');
+                                 array_sort                                 
+----------------------------------------------------------------------------
+ {NULL,NULL,"abc DEF 123abc","ábc sßs ßss DÉF",ⱥȺ,ⱥⱥⱥ,ȺȺȺ,"ǄxxǄ ǆxxǅ ǅxxǆ"}
+(1 row)
+
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,null,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ,null}'::text[], 'asc nulls last');
+                                 array_sort                                 
+----------------------------------------------------------------------------
+ {"abc DEF 123abc","ábc sßs ßss DÉF",ⱥȺ,ⱥⱥⱥ,ȺȺȺ,"ǄxxǄ ǆxxǅ ǅxxǆ",NULL,NULL}
+(1 row)
+
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,null,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ,null}'::text[], 'desc nulls first');
+                                 array_sort                                 
+----------------------------------------------------------------------------
+ {NULL,NULL,"ǄxxǄ ǆxxǅ ǅxxǆ",ȺȺȺ,ⱥⱥⱥ,ⱥȺ,"ábc sßs ßss DÉF","abc DEF 123abc"}
+(1 row)
+
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,null,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ,null}'::text[], 'desc nulls last');
+                                 array_sort                                 
+----------------------------------------------------------------------------
+ {"ǄxxǄ ǆxxǅ ǅxxǆ",ȺȺȺ,ⱥⱥⱥ,ⱥȺ,"ábc sßs ßss DÉF","abc DEF 123abc",NULL,NULL}
+(1 row)
+
diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql
index 47058dfde5..2cdd26386b 100644
--- a/src/test/regress/sql/arrays.sql
+++ b/src/test/regress/sql/arrays.sql
@@ -827,3 +827,26 @@ SELECT array_dims(array_sample('[-1:2][2:3]={{1,2},{3,NULL},{5,6},{7,8}}'::int[]
 SELECT array_dims(array_sample('{{{1,2},{3,NULL}},{{5,6},{7,8}},{{9,10},{11,12}}}'::int[], 2));
 SELECT array_sample('{1,2,3,4,5,6}'::int[], -1); -- fail
 SELECT array_sample('{1,2,3,4,5,6}'::int[], 7); --fail
+
+-- array_sort
+SELECT array_sort('{}'::int[]);
+SELECT array_sort('{1,3,5,2,4,6}'::int[]);
+SELECT array_sort('{1,3,5,2,4,6}'::int[], 'desc');
+SELECT array_sort('{1.1,3.3,5.5,2.2,4.4,6.6}'::float8[], 'asc');
+SELECT array_sort('{1.1,3.3,5.5,2.2,4.4,6.6}'::float8[], 'desc');
+SELECT array_sort('{1.1,3.3,5.5,2.2,4.4,6.6}'::numeric[]);
+SELECT array_sort('{1.1,3.3,5.5,2.2,4.4,6.6}'::numeric[], 'desc');
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ}'::text[]);
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ}'::text[], 'desc');
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ}'::text[] COLLATE "pg_c_utf8", 'asc');
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ}'::text[] COLLATE "pg_c_utf8", 'desc');
+-- nulls first/last tests
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,null,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ,null}'::text[]);
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,null,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ,null}'::text[], 'asc');
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,null,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ,null}'::text[], 'desc');
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,null,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ,null}'::text[], 'nulls first');
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,null,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ,null}'::text[], 'nulls first');
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,null,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ,null}'::text[], 'asc nulls first');
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,null,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ,null}'::text[], 'asc nulls last');
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,null,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ,null}'::text[], 'desc nulls first');
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,null,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ,null}'::text[], 'desc nulls last');
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index b6135f0347..99f1abe869 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2025,6 +2025,7 @@ ParseLoc
 ParseNamespaceColumn
 ParseNamespaceItem
 ParseParamRefHook
+ParseSortOrderState
 ParseState
 ParsedLex
 ParsedScript
-- 
2.39.5



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: general purpose array_sort
  2024-09-27 13:15 general purpose array_sort Junwang Zhao <[email protected]>
  2024-09-28 11:52 ` Re: general purpose array_sort Junwang Zhao <[email protected]>
  2024-09-28 14:40   ` Re: general purpose array_sort jian he <[email protected]>
  2024-09-29 02:05     ` Re: general purpose array_sort Junwang Zhao <[email protected]>
  2024-09-29 02:50       ` Re: general purpose array_sort David G. Johnston <[email protected]>
  2024-09-30 05:01         ` Re: general purpose array_sort Junwang Zhao <[email protected]>
@ 2024-09-30 15:13           ` jian he <[email protected]>
  2024-10-01 05:23             ` Re: general purpose array_sort Junwang Zhao <[email protected]>
  0 siblings, 1 reply; 10+ messages in thread

From: jian he @ 2024-09-30 15:13 UTC (permalink / raw)
  To: Junwang Zhao <[email protected]>; +Cc: David G. Johnston <[email protected]>; PostgreSQL Hackers <[email protected]>; [email protected] <[email protected]>; Robert Haas <[email protected]>; Amit Langote <[email protected]>

On Mon, Sep 30, 2024 at 1:01 PM Junwang Zhao <[email protected]> wrote:
>
> > I would suggest accepting:
> > asc
> > desc
> > asc nulls first
> > asc nulls last *
> > desc nulls first *
> > desc nulls last
> >
> > As valid inputs for "dir" - and that the starred options are the defaults when null position is omitted.
> >
> > In short, mimic create index.
> >
> > David J.
> >
>
> PFA v3 with David's suggestion addressed.
>

I think just adding 2 bool arguments (asc/desc, nulls last/not nulls
last) would be easier.
but either way, (i don't have a huge opinion)
but document the second argument, imagine case
SELECT array_sort('{a,B}'::text[] , E'aSc NulLs  LaST \t\r\n');
would be tricky?

errmsg("multidimensional arrays sorting are not supported")));
write a sql test to trigger the error message that would be great.

you can add two or one example to collate.icu.utf8.sql to demo that it
actually works with COLLATE  collation_name
like:
SELECT array_sort('{a,B}'::text[] COLLATE case_insensitive);
SELECT array_sort('{a,B}'::text[] COLLATE "C");

#define WHITESPACE " \t\n\r"
you may also check function scanner_isspace

+ typentry = (TypeCacheEntry *) fcinfo->flinfo->fn_extra;
+ if (typentry == NULL || typentry->type_id != elmtyp)
+ {
+ typentry = lookup_type_cache(elmtyp, sort_asc ? TYPECACHE_LT_OPR :
TYPECACHE_GT_OPR);
+ fcinfo->flinfo->fn_extra = (void *) typentry;
+ }
you need to one-time check typentry->lt_opr or typentry->gt_opr exists?
see CreateStatistics.
            /* Disallow data types without a less-than operator */
            type = lookup_type_cache(attForm->atttypid, TYPECACHE_LT_OPR);
            if (type->lt_opr == InvalidOid)
                ereport(ERROR,
                        (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
                         errmsg("column \"%s\" cannot be used in
statistics because its type %s has no default btree operator class",
                                attname, format_type_be(attForm->atttypid))));

^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: general purpose array_sort
  2024-09-27 13:15 general purpose array_sort Junwang Zhao <[email protected]>
  2024-09-28 11:52 ` Re: general purpose array_sort Junwang Zhao <[email protected]>
  2024-09-28 14:40   ` Re: general purpose array_sort jian he <[email protected]>
  2024-09-29 02:05     ` Re: general purpose array_sort Junwang Zhao <[email protected]>
  2024-09-29 02:50       ` Re: general purpose array_sort David G. Johnston <[email protected]>
  2024-09-30 05:01         ` Re: general purpose array_sort Junwang Zhao <[email protected]>
  2024-09-30 15:13           ` Re: general purpose array_sort jian he <[email protected]>
@ 2024-10-01 05:23             ` Junwang Zhao <[email protected]>
  2024-10-02 01:50               ` Re: general purpose array_sort jian he <[email protected]>
  0 siblings, 1 reply; 10+ messages in thread

From: Junwang Zhao @ 2024-10-01 05:23 UTC (permalink / raw)
  To: jian he <[email protected]>; +Cc: David G. Johnston <[email protected]>; PostgreSQL Hackers <[email protected]>; [email protected] <[email protected]>; Robert Haas <[email protected]>; Amit Langote <[email protected]>

Hi Jian,

On Mon, Sep 30, 2024 at 11:13 PM jian he <[email protected]> wrote:
>
> On Mon, Sep 30, 2024 at 1:01 PM Junwang Zhao <[email protected]> wrote:
> >
> > > I would suggest accepting:
> > > asc
> > > desc
> > > asc nulls first
> > > asc nulls last *
> > > desc nulls first *
> > > desc nulls last
> > >
> > > As valid inputs for "dir" - and that the starred options are the defaults when null position is omitted.
> > >
> > > In short, mimic create index.
> > >
> > > David J.
> > >
> >
> > PFA v3 with David's suggestion addressed.
> >
>
> I think just adding 2 bool arguments (asc/desc, nulls last/not nulls
> last) would be easier.
Yeah, this would be easier, it's just the intarray module use
the direction parameter, I keep it here for the same user
experience, I don't insist if some committer thinks 2 bool arguments
would be a better option.

> but either way, (i don't have a huge opinion)
> but document the second argument, imagine case
> SELECT array_sort('{a,B}'::text[] , E'aSc NulLs  LaST \t\r\n');
> would be tricky?

The case you provide should give the correct results, but
I doubt users will do this.
I'm not good at document wording, so you might give me some help
with the document part.

>
>
> errmsg("multidimensional arrays sorting are not supported")));
> write a sql test to trigger the error message that would be great.
>
> you can add two or one example to collate.icu.utf8.sql to demo that it
> actually works with COLLATE  collation_name
> like:
> SELECT array_sort('{a,B}'::text[] COLLATE case_insensitive);
> SELECT array_sort('{a,B}'::text[] COLLATE "C");
>
Fixed.

>
> #define WHITESPACE " \t\n\r"
> you may also check function scanner_isspace
>
Fixed.

>
> + typentry = (TypeCacheEntry *) fcinfo->flinfo->fn_extra;
> + if (typentry == NULL || typentry->type_id != elmtyp)
> + {
> + typentry = lookup_type_cache(elmtyp, sort_asc ? TYPECACHE_LT_OPR :
> TYPECACHE_GT_OPR);
> + fcinfo->flinfo->fn_extra = (void *) typentry;
> + }
> you need to one-time check typentry->lt_opr or typentry->gt_opr exists?
> see CreateStatistics.
>             /* Disallow data types without a less-than operator */
>             type = lookup_type_cache(attForm->atttypid, TYPECACHE_LT_OPR);
>             if (type->lt_opr == InvalidOid)
>                 ereport(ERROR,
>                         (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
>                          errmsg("column \"%s\" cannot be used in
> statistics because its type %s has no default btree operator class",
>                                 attname, format_type_be(attForm->atttypid))));

I added an Assert for this part, not sure if that is enough.

-- 
Regards
Junwang Zhao


Attachments:

  [application/octet-stream] v4-0001-general-purpose-array_sort.patch (19.7K, ../../CAEG8a3+nQoMbMOAQOeqLLZeeVPgW4Y5bpvCze9tmunq1u1EGuw@mail.gmail.com/2-v4-0001-general-purpose-array_sort.patch)
  download | inline diff:
From 0ca06aed04cbafe69c021bbf991b44595b77b1b9 Mon Sep 17 00:00:00 2001
From: Junwang Zhao <[email protected]>
Date: Fri, 27 Sep 2024 13:05:40 +0000
Subject: [PATCH v4] general purpose array_sort

Sorts anyarray in either ascending or descending order.
The array must be empty or one-dimensional.

Signed-off-by: Junwang Zhao <[email protected]>
---
 doc/src/sgml/func.sgml                        |  32 ++++
 src/backend/utils/adt/array_userfuncs.c       | 181 ++++++++++++++++++
 src/include/catalog/pg_proc.dat               |   6 +
 src/test/regress/expected/arrays.out          | 125 ++++++++++++
 .../regress/expected/collate.icu.utf8.out     |  13 ++
 src/test/regress/sql/arrays.sql               |  25 +++
 src/test/regress/sql/collate.icu.utf8.sql     |   4 +
 src/tools/pgindent/typedefs.list              |   1 +
 8 files changed, 387 insertions(+)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index e39d524b6b..cf30484e2a 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -20419,6 +20419,38 @@ SELECT NULLIF(value, '(none)') ...
        </para></entry>
       </row>
 
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>array_sort</primary>
+        </indexterm>
+        <function>array_sort</function> ( <type>anyarray</type> <optional> COLLATE <replaceable>collation_name</replaceable> </optional> <optional> , <parameter>dir</parameter> </optional>)
+        <returnvalue>anyarray</returnvalue>
+       </para>
+       <para>
+        Sorts the array based on the given parameter. The array must be empty or one-dimensional.
+       </para>
+       <para>
+        If the <literal>COLLATE</literal> option is specified then sorting is based on <replaceable>collation_name</replaceable>, otherwise
+        using array element type's collation.
+        <parameter>dir</parameter> must be one of the following, and that the starred options are the defaults when null position is omitted.
+<programlisting>
+ asc
+ desc
+ asc nulls first
+ asc nulls last *
+ desc nulls first *
+ desc nulls last
+ nulls first
+ nulls last
+</programlisting>
+       </para>
+       <para>
+        <literal>array_sort(ARRAY[1,2,5,6,3,4])</literal>
+        <returnvalue>{1,2,3,4,5,6}</returnvalue>
+       </para></entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm id="function-array-to-string">
diff --git a/src/backend/utils/adt/array_userfuncs.c b/src/backend/utils/adt/array_userfuncs.c
index 6599be2ec5..3cb890a734 100644
--- a/src/backend/utils/adt/array_userfuncs.c
+++ b/src/backend/utils/adt/array_userfuncs.c
@@ -12,15 +12,18 @@
  */
 #include "postgres.h"
 
+#include "catalog/namespace.h"
 #include "catalog/pg_type.h"
 #include "common/int.h"
 #include "common/pg_prng.h"
 #include "libpq/pqformat.h"
+#include "miscadmin.h"
 #include "port/pg_bitutils.h"
 #include "utils/array.h"
 #include "utils/builtins.h"
 #include "utils/datum.h"
 #include "utils/lsyscache.h"
+#include "utils/tuplesort.h"
 #include "utils/typcache.h"
 
 /*
@@ -1685,3 +1688,181 @@ array_sample(PG_FUNCTION_ARGS)
 
 	PG_RETURN_ARRAYTYPE_P(result);
 }
+
+
+#define WHITESPACE " \t\n\r\v\f"
+
+typedef enum
+{
+	PARSE_SORT_ORDER_INIT,
+	PARSE_SORT_ORDER_DIRECTION_SET,
+	PARSE_SORT_ORDER_NULLS_OPTION,
+	PARSE_SORT_ORDER_ERROR,
+	PARSE_SORT_ORDER_DONE
+} ParseSortOrderState;
+
+static bool
+parse_sort_order(const char *str, bool *sort_asc, bool *nulls_first)
+{
+	char	   *token;
+	char	   *saveptr;
+	char	   *str_copy = pstrdup(str);
+	bool		nulls_first_set = false;
+	ParseSortOrderState state = PARSE_SORT_ORDER_INIT;
+
+	token = strtok_r(str_copy, WHITESPACE, &saveptr);
+
+	while (token != NULL && state != PARSE_SORT_ORDER_ERROR)
+	{
+		switch (state)
+		{
+			case PARSE_SORT_ORDER_INIT:
+				if (pg_strcasecmp(token, "ASC") == 0)
+				{
+					*sort_asc = true;
+					state = PARSE_SORT_ORDER_DIRECTION_SET;
+				}
+				else if (pg_strcasecmp(token, "DESC") == 0)
+				{
+					*sort_asc = false;
+					state = PARSE_SORT_ORDER_DIRECTION_SET;
+				}
+				else if (pg_strcasecmp(token, "NULLS") == 0)
+					state = PARSE_SORT_ORDER_NULLS_OPTION;
+				else
+					state = PARSE_SORT_ORDER_ERROR;
+				break;
+
+			case PARSE_SORT_ORDER_DIRECTION_SET:
+				if (pg_strcasecmp(token, "NULLS") == 0)
+					state = PARSE_SORT_ORDER_NULLS_OPTION;
+				else
+					state = PARSE_SORT_ORDER_ERROR;
+				break;
+
+			case PARSE_SORT_ORDER_NULLS_OPTION:
+				if (pg_strcasecmp(token, "FIRST") == 0)
+				{
+					*nulls_first = true;
+					nulls_first_set = true;
+					state = PARSE_SORT_ORDER_DONE;
+				}
+				else if (pg_strcasecmp(token, "LAST") == 0)
+				{
+					*nulls_first = false;
+					nulls_first_set = true;
+					state = PARSE_SORT_ORDER_DONE;
+				}
+				else
+					state = PARSE_SORT_ORDER_ERROR;
+				break;
+
+			case PARSE_SORT_ORDER_DONE:
+				/* No more tokens should be processed after first/last */
+				state = PARSE_SORT_ORDER_ERROR;
+				break;
+
+			default:
+				state = PARSE_SORT_ORDER_ERROR;
+				break;
+		}
+
+		token = strtok_r(NULL, WHITESPACE, &saveptr);
+	}
+
+	if (state == PARSE_SORT_ORDER_INIT ||
+		state == PARSE_SORT_ORDER_DIRECTION_SET)
+		state = PARSE_SORT_ORDER_DONE;
+
+	if (state == PARSE_SORT_ORDER_NULLS_OPTION)
+		state = PARSE_SORT_ORDER_ERROR;
+
+	if (!nulls_first_set && state == PARSE_SORT_ORDER_DONE)
+		*nulls_first = !*sort_asc;
+
+	pfree(str_copy);
+	return state == PARSE_SORT_ORDER_DONE;
+}
+
+/*
+ * array_sort
+ *
+ * Sorts the array in either ascending or descending order.
+ * The array must be empty or one-dimensional.
+ */
+Datum
+array_sort(PG_FUNCTION_ARGS)
+{
+	ArrayType  *array = PG_GETARG_ARRAYTYPE_P(0);
+	text	   *dirstr = (fcinfo->nargs > 1) ? PG_GETARG_TEXT_PP(1) : NULL;
+	bool		sort_asc = true;
+	bool		nulls_first = false;
+	Oid			elmtyp;
+	Oid			collation = PG_GET_COLLATION();
+	TypeCacheEntry *typentry;
+	Tuplesortstate *tuplesortstate;
+	ArrayIterator array_iterator;
+	Datum		value;
+	bool		isnull;
+	ArrayBuildState *astate = NULL;
+
+	if (ARR_NDIM(array) > 1)
+		ereport(ERROR,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				 errmsg("multidimensional arrays sorting are not supported")));
+
+	if (ARR_NDIM(array) < 1)
+		PG_RETURN_ARRAYTYPE_P(array);
+
+	if (dirstr != NULL)
+	{
+		if (!parse_sort_order(text_to_cstring(dirstr), &sort_asc, &nulls_first))
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+					 errmsg("second parameter must be a valid sort direction")));
+	}
+
+	elmtyp = ARR_ELEMTYPE(array);
+	typentry = (TypeCacheEntry *) fcinfo->flinfo->fn_extra;
+	if (typentry == NULL || typentry->type_id != elmtyp)
+	{
+		typentry = lookup_type_cache(elmtyp, sort_asc ? TYPECACHE_LT_OPR : TYPECACHE_GT_OPR);
+		fcinfo->flinfo->fn_extra = (void *) typentry;
+	}
+
+	Assert(OidIsValid(typentry->lt_opr) || OidIsValid(typentry->gt_opr));
+	tuplesortstate = tuplesort_begin_datum(elmtyp,
+										   sort_asc ? typentry->lt_opr : typentry->gt_opr,
+										   collation,
+										   nulls_first, work_mem, NULL, false);
+
+	array_iterator = array_create_iterator(array, 0, NULL);
+	while (array_iterate(array_iterator, &value, &isnull))
+	{
+		tuplesort_putdatum(tuplesortstate, value, isnull);
+	}
+	array_free_iterator(array_iterator);
+
+	/*
+	 * Do the sort.
+	 */
+	tuplesort_performsort(tuplesortstate);
+
+	while (tuplesort_getdatum(tuplesortstate, true, false, &value, &isnull, NULL))
+	{
+		astate = accumArrayResult(astate, value, isnull,
+								  elmtyp, CurrentMemoryContext);
+	}
+
+	tuplesort_end(tuplesortstate);
+
+	/* Avoid leaking memory when handed toasted input */
+	PG_FREE_IF_COPY(array, 0);
+	PG_RETURN_DATUM(makeArrayResult(astate, CurrentMemoryContext));
+}
+
+Datum
+array_sort_order(PG_FUNCTION_ARGS)
+{
+	return array_sort(fcinfo);
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 43f608d7a0..1b2d64ac39 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -1734,6 +1734,12 @@
 { oid => '6216', descr => 'take samples from array',
   proname => 'array_sample', provolatile => 'v', prorettype => 'anyarray',
   proargtypes => 'anyarray int4', prosrc => 'array_sample' },
+{ oid => '8810', descr => 'sort array',
+  proname => 'array_sort', provolatile => 'v', prorettype => 'anyarray',
+  proargtypes => 'anyarray', prosrc => 'array_sort'},
+{ oid => '8811', descr => 'sort array',
+  proname => 'array_sort', provolatile => 'v', prorettype => 'anyarray',
+  proargtypes => 'anyarray text', prosrc => 'array_sort_order'},
 { oid => '3816', descr => 'array typanalyze',
   proname => 'array_typanalyze', provolatile => 's', prorettype => 'bool',
   proargtypes => 'internal', prosrc => 'array_typanalyze' },
diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out
index a6d81fd5f9..9c71a65b5a 100644
--- a/src/test/regress/expected/arrays.out
+++ b/src/test/regress/expected/arrays.out
@@ -2703,3 +2703,128 @@ SELECT array_sample('{1,2,3,4,5,6}'::int[], -1); -- fail
 ERROR:  sample size must be between 0 and 6
 SELECT array_sample('{1,2,3,4,5,6}'::int[], 7); --fail
 ERROR:  sample size must be between 0 and 6
+-- array_sort
+SELECT array_sort('{}'::int[]);
+ array_sort 
+------------
+ {}
+(1 row)
+
+SELECT array_sort('{1,3,5,2,4,6}'::int[]);
+  array_sort   
+---------------
+ {1,2,3,4,5,6}
+(1 row)
+
+SELECT array_sort('{1,3,5,2,4,6}'::int[], 'desc');
+  array_sort   
+---------------
+ {6,5,4,3,2,1}
+(1 row)
+
+SELECT array_sort('{1.1,3.3,5.5,2.2,4.4,6.6}'::float8[], 'asc');
+        array_sort         
+---------------------------
+ {1.1,2.2,3.3,4.4,5.5,6.6}
+(1 row)
+
+SELECT array_sort('{1.1,3.3,5.5,2.2,4.4,6.6}'::float8[], 'desc');
+        array_sort         
+---------------------------
+ {6.6,5.5,4.4,3.3,2.2,1.1}
+(1 row)
+
+SELECT array_sort('{1.1,3.3,5.5,2.2,4.4,6.6}'::numeric[]);
+        array_sort         
+---------------------------
+ {1.1,2.2,3.3,4.4,5.5,6.6}
+(1 row)
+
+SELECT array_sort('{1.1,3.3,5.5,2.2,4.4,6.6}'::numeric[], 'desc');
+        array_sort         
+---------------------------
+ {6.6,5.5,4.4,3.3,2.2,1.1}
+(1 row)
+
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ}'::text[]);
+                            array_sort                            
+------------------------------------------------------------------
+ {"abc DEF 123abc","ábc sßs ßss DÉF",ⱥȺ,ⱥⱥⱥ,ȺȺȺ,"ǄxxǄ ǆxxǅ ǅxxǆ"}
+(1 row)
+
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ}'::text[], 'desc');
+                            array_sort                            
+------------------------------------------------------------------
+ {"ǄxxǄ ǆxxǅ ǅxxǆ",ȺȺȺ,ⱥⱥⱥ,ⱥȺ,"ábc sßs ßss DÉF","abc DEF 123abc"}
+(1 row)
+
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ}'::text[] COLLATE "pg_c_utf8", 'asc');
+                            array_sort                            
+------------------------------------------------------------------
+ {"abc DEF 123abc","ábc sßs ßss DÉF","ǄxxǄ ǆxxǅ ǅxxǆ",ȺȺȺ,ⱥȺ,ⱥⱥⱥ}
+(1 row)
+
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ}'::text[] COLLATE "pg_c_utf8", 'desc');
+                            array_sort                            
+------------------------------------------------------------------
+ {ⱥⱥⱥ,ⱥȺ,ȺȺȺ,"ǄxxǄ ǆxxǅ ǅxxǆ","ábc sßs ßss DÉF","abc DEF 123abc"}
+(1 row)
+
+-- nulls first/last tests
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,null,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ,null}'::text[]);
+                                 array_sort                                 
+----------------------------------------------------------------------------
+ {"abc DEF 123abc","ábc sßs ßss DÉF",ⱥȺ,ⱥⱥⱥ,ȺȺȺ,"ǄxxǄ ǆxxǅ ǅxxǆ",NULL,NULL}
+(1 row)
+
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,null,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ,null}'::text[], 'asc');
+                                 array_sort                                 
+----------------------------------------------------------------------------
+ {"abc DEF 123abc","ábc sßs ßss DÉF",ⱥȺ,ⱥⱥⱥ,ȺȺȺ,"ǄxxǄ ǆxxǅ ǅxxǆ",NULL,NULL}
+(1 row)
+
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,null,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ,null}'::text[], 'desc');
+                                 array_sort                                 
+----------------------------------------------------------------------------
+ {NULL,NULL,"ǄxxǄ ǆxxǅ ǅxxǆ",ȺȺȺ,ⱥⱥⱥ,ⱥȺ,"ábc sßs ßss DÉF","abc DEF 123abc"}
+(1 row)
+
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,null,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ,null}'::text[], 'nulls first');
+                                 array_sort                                 
+----------------------------------------------------------------------------
+ {NULL,NULL,"abc DEF 123abc","ábc sßs ßss DÉF",ⱥȺ,ⱥⱥⱥ,ȺȺȺ,"ǄxxǄ ǆxxǅ ǅxxǆ"}
+(1 row)
+
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,null,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ,null}'::text[], 'nulls first');
+                                 array_sort                                 
+----------------------------------------------------------------------------
+ {NULL,NULL,"abc DEF 123abc","ábc sßs ßss DÉF",ⱥȺ,ⱥⱥⱥ,ȺȺȺ,"ǄxxǄ ǆxxǅ ǅxxǆ"}
+(1 row)
+
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,null,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ,null}'::text[], 'asc nulls first');
+                                 array_sort                                 
+----------------------------------------------------------------------------
+ {NULL,NULL,"abc DEF 123abc","ábc sßs ßss DÉF",ⱥȺ,ⱥⱥⱥ,ȺȺȺ,"ǄxxǄ ǆxxǅ ǅxxǆ"}
+(1 row)
+
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,null,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ,null}'::text[], 'asc nulls last');
+                                 array_sort                                 
+----------------------------------------------------------------------------
+ {"abc DEF 123abc","ábc sßs ßss DÉF",ⱥȺ,ⱥⱥⱥ,ȺȺȺ,"ǄxxǄ ǆxxǅ ǅxxǆ",NULL,NULL}
+(1 row)
+
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,null,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ,null}'::text[], 'desc nulls first');
+                                 array_sort                                 
+----------------------------------------------------------------------------
+ {NULL,NULL,"ǄxxǄ ǆxxǅ ǅxxǆ",ȺȺȺ,ⱥⱥⱥ,ⱥȺ,"ábc sßs ßss DÉF","abc DEF 123abc"}
+(1 row)
+
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,null,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ,null}'::text[], 'desc nulls last');
+                                 array_sort                                 
+----------------------------------------------------------------------------
+ {"ǄxxǄ ǆxxǅ ǅxxǆ",ȺȺȺ,ⱥⱥⱥ,ⱥȺ,"ábc sßs ßss DÉF","abc DEF 123abc",NULL,NULL}
+(1 row)
+
+-- multidimensional array tests
+SELECT array_sort(ARRAY[[1,2],[3,4],[5,6]]);  -- fail
+ERROR:  multidimensional arrays sorting are not supported
diff --git a/src/test/regress/expected/collate.icu.utf8.out b/src/test/regress/expected/collate.icu.utf8.out
index 31345295c1..743b4d8199 100644
--- a/src/test/regress/expected/collate.icu.utf8.out
+++ b/src/test/regress/expected/collate.icu.utf8.out
@@ -1338,6 +1338,19 @@ SELECT 'abc' <= 'ABC' COLLATE case_insensitive, 'abc' >= 'ABC' COLLATE case_inse
  t        | t
 (1 row)
 
+-- tests with array_sort
+SELECT array_sort('{a,B}'::text[] COLLATE case_insensitive);
+ array_sort 
+------------
+ {a,B}
+(1 row)
+
+SELECT array_sort('{a,B}'::text[] COLLATE "C");
+ array_sort 
+------------
+ {B,a}
+(1 row)
+
 -- test language tags
 CREATE COLLATION lt_insensitive (provider = icu, locale = 'en-u-ks-level1', deterministic = false);
 SELECT 'aBcD' COLLATE lt_insensitive = 'AbCd' COLLATE lt_insensitive;
diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql
index 47058dfde5..30606700ef 100644
--- a/src/test/regress/sql/arrays.sql
+++ b/src/test/regress/sql/arrays.sql
@@ -827,3 +827,28 @@ SELECT array_dims(array_sample('[-1:2][2:3]={{1,2},{3,NULL},{5,6},{7,8}}'::int[]
 SELECT array_dims(array_sample('{{{1,2},{3,NULL}},{{5,6},{7,8}},{{9,10},{11,12}}}'::int[], 2));
 SELECT array_sample('{1,2,3,4,5,6}'::int[], -1); -- fail
 SELECT array_sample('{1,2,3,4,5,6}'::int[], 7); --fail
+
+-- array_sort
+SELECT array_sort('{}'::int[]);
+SELECT array_sort('{1,3,5,2,4,6}'::int[]);
+SELECT array_sort('{1,3,5,2,4,6}'::int[], 'desc');
+SELECT array_sort('{1.1,3.3,5.5,2.2,4.4,6.6}'::float8[], 'asc');
+SELECT array_sort('{1.1,3.3,5.5,2.2,4.4,6.6}'::float8[], 'desc');
+SELECT array_sort('{1.1,3.3,5.5,2.2,4.4,6.6}'::numeric[]);
+SELECT array_sort('{1.1,3.3,5.5,2.2,4.4,6.6}'::numeric[], 'desc');
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ}'::text[]);
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ}'::text[], 'desc');
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ}'::text[] COLLATE "pg_c_utf8", 'asc');
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ}'::text[] COLLATE "pg_c_utf8", 'desc');
+-- nulls first/last tests
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,null,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ,null}'::text[]);
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,null,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ,null}'::text[], 'asc');
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,null,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ,null}'::text[], 'desc');
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,null,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ,null}'::text[], 'nulls first');
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,null,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ,null}'::text[], 'nulls first');
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,null,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ,null}'::text[], 'asc nulls first');
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,null,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ,null}'::text[], 'asc nulls last');
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,null,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ,null}'::text[], 'desc nulls first');
+SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,null,ǄxxǄ ǆxxǅ ǅxxǆ,ȺȺȺ,ⱥⱥⱥ,ⱥȺ,null}'::text[], 'desc nulls last');
+-- multidimensional array tests
+SELECT array_sort(ARRAY[[1,2],[3,4],[5,6]]);  -- fail
diff --git a/src/test/regress/sql/collate.icu.utf8.sql b/src/test/regress/sql/collate.icu.utf8.sql
index 80f28a97d7..3c739d332b 100644
--- a/src/test/regress/sql/collate.icu.utf8.sql
+++ b/src/test/regress/sql/collate.icu.utf8.sql
@@ -536,6 +536,10 @@ CREATE COLLATION case_insensitive (provider = icu, locale = '@colStrength=second
 SELECT 'abc' <= 'ABC' COLLATE case_sensitive, 'abc' >= 'ABC' COLLATE case_sensitive;
 SELECT 'abc' <= 'ABC' COLLATE case_insensitive, 'abc' >= 'ABC' COLLATE case_insensitive;
 
+-- tests with array_sort
+SELECT array_sort('{a,B}'::text[] COLLATE case_insensitive);
+SELECT array_sort('{a,B}'::text[] COLLATE "C");
+
 -- test language tags
 CREATE COLLATION lt_insensitive (provider = icu, locale = 'en-u-ks-level1', deterministic = false);
 SELECT 'aBcD' COLLATE lt_insensitive = 'AbCd' COLLATE lt_insensitive;
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index b6135f0347..99f1abe869 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2025,6 +2025,7 @@ ParseLoc
 ParseNamespaceColumn
 ParseNamespaceItem
 ParseParamRefHook
+ParseSortOrderState
 ParseState
 ParsedLex
 ParsedScript
-- 
2.39.5



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: general purpose array_sort
  2024-09-27 13:15 general purpose array_sort Junwang Zhao <[email protected]>
  2024-09-28 11:52 ` Re: general purpose array_sort Junwang Zhao <[email protected]>
  2024-09-28 14:40   ` Re: general purpose array_sort jian he <[email protected]>
  2024-09-29 02:05     ` Re: general purpose array_sort Junwang Zhao <[email protected]>
  2024-09-29 02:50       ` Re: general purpose array_sort David G. Johnston <[email protected]>
  2024-09-30 05:01         ` Re: general purpose array_sort Junwang Zhao <[email protected]>
  2024-09-30 15:13           ` Re: general purpose array_sort jian he <[email protected]>
  2024-10-01 05:23             ` Re: general purpose array_sort Junwang Zhao <[email protected]>
@ 2024-10-02 01:50               ` jian he <[email protected]>
  0 siblings, 0 replies; 10+ messages in thread

From: jian he @ 2024-10-02 01:50 UTC (permalink / raw)
  To: Junwang Zhao <[email protected]>; +Cc: David G. Johnston <[email protected]>; PostgreSQL Hackers <[email protected]>; [email protected] <[email protected]>; Robert Haas <[email protected]>; Amit Langote <[email protected]>

> >
> > + typentry = (TypeCacheEntry *) fcinfo->flinfo->fn_extra;
> > + if (typentry == NULL || typentry->type_id != elmtyp)
> > + {
> > + typentry = lookup_type_cache(elmtyp, sort_asc ? TYPECACHE_LT_OPR :
> > TYPECACHE_GT_OPR);
> > + fcinfo->flinfo->fn_extra = (void *) typentry;
> > + }
> > you need to one-time check typentry->lt_opr or typentry->gt_opr exists?
> > see CreateStatistics.
> >             /* Disallow data types without a less-than operator */
> >             type = lookup_type_cache(attForm->atttypid, TYPECACHE_LT_OPR);
> >             if (type->lt_opr == InvalidOid)
> >                 ereport(ERROR,
> >                         (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
> >                          errmsg("column \"%s\" cannot be used in
> > statistics because its type %s has no default btree operator class",
> >                                 attname, format_type_be(attForm->atttypid))));
>
> I added an Assert for this part, not sure if that is enough.
>

i think it really should be:

if (typentry == NULL || typentry->type_id != elmtyp)
{
 typentry = lookup_type_cache(elmtyp, sort_asc ? TYPECACHE_LT_OPR :
TYPECACHE_GT_OPR);
 fcinfo->flinfo->fn_extra = (void *) typentry;
if ((sort_asc && !OidIsValid(typentry->lt_opr) || (!sort_as &&
OidIsValid(typentry->gt_opr));
ereport(ERROR,....)
}

Imagine a type that doesn't have TYPECACHE_LT_OPR or TYPECACHE_GT_OPR
then we cannot do the sort, we should just error out.

I just tried this colour type [1] with (CREATE TYPE colour (INPUT =
colour_in, OUTPUT = colour_out, LIKE = pg_catalog.int4);

select array_sort('{#FF0000, #FF0000}'::colour[]);
of course it will segfault  with your new Assert.


[1] https://github.com/hlinnaka/colour-datatype/blob/master/colour.c






^ permalink  raw  reply  [nested|flat] 10+ messages in thread

end of thread, other threads:[~2024-10-02 01:50 UTC | newest]

Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-03-19 06:11 [PATCH v35 6/7] Doc part of shared-memory based stats collector. Kyotaro Horiguchi <[email protected]>
2024-09-27 13:15 general purpose array_sort Junwang Zhao <[email protected]>
2024-09-28 11:52 ` Re: general purpose array_sort Junwang Zhao <[email protected]>
2024-09-28 14:40   ` Re: general purpose array_sort jian he <[email protected]>
2024-09-29 02:05     ` Re: general purpose array_sort Junwang Zhao <[email protected]>
2024-09-29 02:50       ` Re: general purpose array_sort David G. Johnston <[email protected]>
2024-09-30 05:01         ` Re: general purpose array_sort Junwang Zhao <[email protected]>
2024-09-30 15:13           ` Re: general purpose array_sort jian he <[email protected]>
2024-10-01 05:23             ` Re: general purpose array_sort Junwang Zhao <[email protected]>
2024-10-02 01:50               ` Re: general purpose array_sort jian he <[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