Самый длинный матч с префиксом

Question

Какой лучший способ получить точные и быстрые запросы в PostgreSQL для самого длинного соответствия префикса?

Это:

A.) выберите * из таблицы, где столбец (подвыбор);

B.) выберите * из таблицы, где strpos(column,column2) = 1
    упорядочить по длине (столбец 2) ограничение по пределу 1;

C.) выберите * из таблицы, где столбец ~ column2
    Порядок по длине (столбец 2) ограничение по объему 1

Я планирую использовать в обновлении. Есть идеи?

Answer 1

Я не знал бы о функции, делающей это из коробки в PostgreSQL.
Рекурсивный CTE был бы ключевым элементом для довольно элегантного решения (доступно в PostgreSQL 8.4 или более поздней версии).

Я беру стол filter держать строки фильтра:

CREATE TABLE filter (f_id int, string text);

И стол tbl чтобы найти самый длинный матч:

CREATE TABLE tbl(t_id int, col text);

запрос

WITH RECURSIVE
     f AS (SELECT f_id, string, length(string) AS flen FROM filter)
    ,t AS (SELECT t_id, col, length(col) AS tlen FROM tbl)
    ,x AS (
    SELECT t.t_id, f.f_id, t.col, f.string
          ,2 AS match, LEAST(flen, tlen) AS len
    FROM   t
    JOIN   f ON left(t.col, 1) = left(f.string, 1)

    UNION ALL
    SELECT t_id, f_id, col, string, match + 1, len
    FROM   x
    WHERE  left(col, match) = left(string, match)
    AND    match <= len
    )
SELECT DISTINCT
       f_id
      ,string
      ,first_value(col) OVER w AS col
      ,first_value(t_id) OVER w AS t_id
      ,(first_value(match) OVER w -1) AS longest_match
FROM   x
WINDOW w AS (PARTITION BY f_id ORDER BY match DESC)
ORDER  BY 2,1,3,4;

Подробное объяснение того, как работает окончательный SELECT в этом ответе.
Рабочая демоверсия по sqlfiddle.

Вы не определили, какое совпадение выбрать из набора одинаково длинных совпадений. Я выбираю одного произвольного победителя из связей.

Я планирую использовать в обновлении.

PostgreSQL 9.1 представил CTE, модифицирующие данные, так что вы можете использовать это в UPDATE Заявление напрямую.