在PostgreSQL中实现自然排序是否有更优方案？

我之前做项目时需要实现自然排序——比如让"Foo 2"排在"Foo 10"前面。在Elixir代码里这事不算难：用正则把字符串拆成数字和文本片段，生成一个混合字符串（小写）和数字的列表，排序时就能按自然逻辑生效。比如我写的这个Elixir工具函数：

@doc """
Generates a natural sort key for a string so that (for example) strings sort as "foo 1", "foo 2", "foo 10",
instead of "foo 1", "foo 10", "foo 2". It does this by splitting the string into a list of lowercase strings and numbers.

Note that numbers sort before strings: in Elixir/Erlang:
`number < atom < reference < function < port < pid < tuple < map < list < bitstring`

Examples:
iex> natural_sort_key("foo 1")
["foo ", 1]
iex> natural_sort_key("FOO 10")
["foo ", 10]
iex> natural_sort_key("foo 1.3")
["foo ", 1.3]
iex> natural_sort_key(" Café 1.3")
[" café ", 1.3]
iex> natural_sort_key("2 foo 1.3 bar 2")
[2, " foo ", 1.3, " bar ", 2]

Sorting examples:
iex> Enum.sort_by(
...> ["1.1B", "1.2A", "1A", "10A", "1B", "1.1A"],
...> &natural_sort_key/1
...> )
["1A", "1B", "1.1A", "1.1B", "1.2A", "10A"]
iex> Enum.sort_by(
...> [%{name: "foo1"}, %{name: "foo10"}, %{name: "foo2"}],
...> fn foo -> natural_sort_key(foo.name) end
...> )
[%{name: "foo1"}, %{name: "foo2"}, %{name: "foo10"}]
iex> Enum.sort_by(
...> ["1", "A", "2", "B"],
...> fn val -> natural_sort_key(val) end
...> )
["1", "2", "A", "B"]
"""
@spec natural_sort_key(String.t()) :: [String.t() | integer() | float()]
def natural_sort_key(string) when is_binary(string) do
  # Use named captures to identify floats, integers, and text parts
  ~r/(?<float>\d+\.\d+)|(?<integer>\d+)|(?<text>[^\d]+)/
  |> Regex.scan(string, capture: :all_names)
  |> Enum.map(fn ["", integer, ""] when integer != "" -> String.to_integer(integer)
                 [float, "", ""] when float != "" -> String.to_float(float)
                 ["", "", text] when text != "" -> String.downcase(text)
              end)
end

但要在PostgreSQL里实现完全一致的自然排序可没这么顺利。我试过用"数字排序规则"，但它覆盖不了我遇到的所有场景。折腾了好久（还借助了AI、写了完整的测试用例，花了不少耐心），终于搞出了一个能用的函数——虽然它看起来复杂，性能也不算最优，但确实能实现需求，这样就能直接用ORDER BY natural_sort_key(你的文本列)来做自然排序了：

-- Allow doing `ORDER BY natural_sort_key(any_textual_column)`
-- such that it sorts numbers naturally (e.g., "HVAC2" before "HVAC10").
-- Works on text, citext, varchar, char columns.
CREATE FUNCTION "natural_sort_key"(input_text text) RETURNS jsonb AS $$
BEGIN
RETURN (
  WITH components AS (
    SELECT jsonb_agg(
      CASE
        -- Split the text into chunks of contiguous numbers and text.
        -- We need jsonb arrays, not normal arrays, to support a mix of numbers and text.
        --
        -- In each case, we wrap the value in an array and add a leading 0 or 1.
        -- This is because JSONB natively sorts numbers after strings, and we need
        -- to overrule that to be consistent with how sorting works elsewhere.
        --
        -- We also need to pad the json array because shorter arrays are
        -- sorted before longer ones.
        WHEN m[1] ~ '^\d+\.\d+$' THEN jsonb_build_array(0, m[1]::numeric)
        WHEN m[1] ~ '^\d+$' THEN jsonb_build_array(0, m[1]::numeric)
        ELSE jsonb_build_array(1, lower(m[1]))
      END
    ) as components_array
    FROM regexp_matches(input_text, '\d+\.\d+|\d+|[a-zA-Z]+', 'g') m
  )
  SELECT components_array || (
    -- To ensure that the number of "chunks" doesn't affect the sorting, ensure
    -- that we always have 10 of them.
    -- (This would break if the input had more than 10 chunks, like
    -- 1A3A5A7D9E11, but that's very unlikely.)
    -- Pad with [2, ''] so that these "blank" chunks sort after all actual numbers or text.
    SELECT jsonb_agg(jsonb_build_array(2, ''))
    FROM generate_series(1, GREATEST(0, 10 - jsonb_array_length(components_array)))
  )
  FROM components
);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

我写了几个测试用例来验证这个函数的效果，确保它能正确处理各种场景：

-- Test Suite: All these cases must sort in the exact order shown
-- Test 1: Purely numeric values
WITH test_purely_numeric AS (
  SELECT unnest(ARRAY['100', '10', '2', '1']) as name
)
SELECT 'Test 1: Purely Numeric' as test_name, 
       string_agg(name, ', ' ORDER BY natural_sort_key(name)) as sorted_result,
       '1, 2, 10, 100' as expected
FROM test_purely_numeric;

-- 你可以根据需要添加更多测试，比如带文本和数字混合的、带特殊字符的、带小数的场景

目前这个函数能覆盖我遇到的大部分自然排序场景，但如果有更高效、更简洁的实现方案，欢迎大家一起讨论分享！

内容来源于stack exchange