postgresql - Generate_series 用于数字和字母的各种混合

Question

我正在使用这种语法:

generate_series(1, COALESCE((string_to_array(table.id_number, '-')) [2] :: INT, 1)) AS n (numbers)

在 ID 为 32.22.1-4 的元素中生成 ID，以获取 ID 为 32.22.1、32.22.2、32.22.3 和 32.22.4 的 4 行。如何将其更改为也接受字母？

所以对于 32.22.a-c 会有:

32.22.a, 32.22.b, 32.22.c

对于 32.22.d1-d4 会有

32.22.d1, 32.22.d2, 32.22.d3, 32.22.d4

编辑:整个代码如下所示:

INSERT INTO ...
(     
     SELECT
     ...
    FROM table
      CROSS JOIN LATERAL
    generate_series(1, COALESCE((string_to_array(table.id_number, '-')) [2] :: INT, 1)) AS n (numbers)
    WHERE table.id_number LIKE ...
  );

Answer 1

WITH t(id_number) AS ( VALUES
  ('32.33.a1-a5'::TEXT),
  ('32.34.a-c'::TEXT),
  ('32.35.b-e'::TEXT)
), stats AS (
    SELECT
      chars,
      chars[1] pattern, -- pattern use
      CASE
      WHEN (ascii(chars[3]) - ascii(chars[2])) = 0
        THEN FALSE
        ELSE TRUE
      END char_pattern, -- check if series of chars
      CASE
        WHEN (ascii(chars[3]) - ascii(chars [2])) = 0
          THEN right(chars[3],1)::INTEGER
          ELSE (ascii(chars[3]) + 1 - ascii(chars[2]))::INTEGER
      END i -- number of series

    FROM t,
    regexp_matches(t.id_number, '(.*\.)(\w*)-(\w*)$') chars
)
SELECT
  CASE WHEN char_pattern
    THEN pattern || chr(ascii(chars[2]) - 1 + s)
    ELSE pattern || left(chars[2],1) || s::TEXT
  END output
FROM stats,generate_series(1,stats.i) s;

结果:

  output   
---------
 32.33.a1
 32.33.a2
 32.33.a3
 32.33.a4
 32.33.a5
 32.34.a
 32.34.b
 32.34.c
 32.35.b
 32.35.c
 32.35.d
 32.35.e
(12 rows)