PolarDB：prefix（首碼匹配）

前提條件

支援的PolarDB PostgreSQL版的版本如下：

PostgreSQL 14（核心小版本14.10.18.0及以上）。

SELECT version();

背景資訊

字串首碼匹配是一個常見的業務功能。例如，在電話應用中，電訊廠商可能希望在使用者輸入電話號碼首碼時，能夠即時檢索出滿足首碼匹配的電話號碼的完整列表。PolarDB PostgreSQL版支援prefix外掛程式用於實現字串首碼匹配的功能。以下是一個最長首碼匹配的樣本：

SELECT *
    FROM prefixes
    WHERE prefix @> '0123456789'
ORDER BY length(prefix) DESC
    LIMIT 1;

使用方法

建立外掛程式

CREATE EXTENSION prefix;

進行首碼匹配

1. 準備基礎資料，建立表和索引。

   CREATE TABLE prefixes (
           prefix    prefix_range PRIMARY KEY,
           name      TEXT NOT NULL,
           shortname TEXT,
           status    CHAR DEFAULT 'S',
   
           CHECK( status IN ('S', 'R') )
   );
   COMMENT ON COLUMN prefixes.status IS 'S:   - R: reserved';
   
   INSERT INTO prefixes (prefix, name, shortname) VALUES
   ('010001','COLT TELECOMMUNICATIONS FRANCE','COLT'),
   ('010002','EQUANT France','EQFR'),
   ('010003','NUMERICABLE','NURC');
   
   CREATE INDEX idx_prefix ON prefixes USING gist(prefix);

2. 執行如下語句，進行首碼匹配。

   • SELECT '123'::prefix_range @> '123456';

     返回結果如下：

      ?column?
     ----------
      t
     (1 row)

   • SELECT * FROM prefixes WHERE prefix @> '01000123';

     返回結果如下：

      prefix |              name              | shortname | status
     --------+--------------------------------+-----------+--------
      010001 | COLT TELECOMMUNICATIONS FRANCE | COLT      | S
     (1 row)

可使用的操作符

prefix外掛程式提供了一系列操作符，包括常規的<=、<、=、<>、>=和>操作符，它們的含義與字串比較操作符相同。此外，還有@>（包含）、<@（被包含）、&&（重疊）、|（並集）以及&（交集）。

• SELECT a, b,
         a <= b AS "<=", a < b AS "<", a = b AS "=", a <> b AS "<>", a >= b AS ">=", a > b AS ">",
         a @> b AS "@>", a <@ b AS "<@", a && b AS "&&"
     FROM (SELECT a::prefix_range, b::prefix_range
           FROM (VALUES('123', '123'),
                           ('123', '124'),
                           ('123', '123[4-5]'),
                           ('123[4-5]', '123[2-7]'),
                           ('123', '[2-3]')) AS t(a, b)) AS x;

  返回結果如下：

      a     |    b     | <= | < | = | <> | >= | > | @> | <@ | &&
  ----------+----------+----+---+---+----+----+---+----+----+----
   123      | 123      | t  | f | t | f  | t  | f | t  | t  | t
   123      | 124      | t  | t | f | t  | f  | f | f  | f  | f
   123      | 123[4-5] | t  | t | f | t  | f  | f | t  | f  | t
   123[4-5] | 123[2-7] | f  | f | f | t  | t  | t | f  | t  | t
   123      | [2-3]    | t  | t | f | t  | f  | f | f  | f  | f
  (5 rows)

• SELECT a, b, a | b AS UNION, a & b AS INTERSECT
     FROM (SELECT a::prefix_range, b::prefix_range
           FROM (VALUES('123', '123'),
                           ('123', '124'),
                           ('123', '123[4-5]'),
                           ('123[4-5]', '123[2-7]'),
                           ('123', '[2-3]')) AS t(a, b)) AS x;

  返回結果如下：

      a     |    b     |  union   | intersect 
  ----------+----------+----------+-----------
   123      | 123      | 123      | 123
   123      | 124      | 12[3-4]  | 
   123      | 123[4-5] | 123      | 123[4-5]
   123[4-5] | 123[2-7] | 123[2-7] | 123[4-5]
   123      | [2-3]    | [1-3]    | 
  (5 rows)