-- SiftQL parsing, analysis and canonical deparse CREATE EXTENSION pg_sift; CREATE FUNCTION pq(q text) RETURNS text LANGUAGE sql RETURN sift.parse_query(q); -- terms, juxtaposition, keywords (uppercase only), precedence NOT > AND > OR SELECT q, pq(q) FROM (VALUES ('hello'), ('Hello WORLD'), ('a b c'), ('a AND b'), ('a and b'), ('a OR b'), ('a or b'), ('a OR b c'), ('a b OR c'), ('(a OR b) c'), ('a AND (b OR c)'), ('a OR (b OR c)'), ('(a OR b) OR c'), ('a (b c)'), ('a NOT b'), ('a AND NOT b'), ('a NOT b OR c'), ('a NOT (b OR c)'), ('a NOT (b c)'), ('a NOT (b NOT c)'), ('a (NOT b)'), ('(a) (NOT b NOT c)'), ('NOT NOT a'), ('a NOT NOT NOT b'), ('NOT (NOT a)^2 b'), ('(NOT a)^2 b') ) v(q); q | pq -------------------+------------------------- hello | hello Hello WORLD | hello AND world a b c | a AND b AND c a AND b | a AND b a and b | a AND and AND b a OR b | a OR b a or b | a AND b AND or a OR b c | a OR b AND c a b OR c | c OR a AND b (a OR b) c | c AND (a OR b) a AND (b OR c) | a AND (b OR c) a OR (b OR c) | a OR b OR c (a OR b) OR c | a OR b OR c a (b c) | a AND b AND c a NOT b | a AND NOT b a AND NOT b | a AND NOT b a NOT b OR c | c OR a AND NOT b a NOT (b OR c) | a AND NOT (b OR c) a NOT (b c) | a AND NOT (b AND c) a NOT (b NOT c) | a AND NOT (b AND NOT c) a (NOT b) | a AND NOT b (a) (NOT b NOT c) | a AND NOT b AND NOT c NOT NOT a | a a NOT NOT NOT b | a AND NOT b NOT (NOT a)^2 b | a AND b (NOT a)^2 b | b AND NOT a (26 rows) -- canonical order: positives sorted (terms, prefixes, wildcards, regexes, -- fuzzy, phrases, groups), NOTs last; duplicates merge SELECT q, pq(q) FROM (VALUES ('zeta alpha mid'), ('b OR a'), ('"x y" z* /r/ w~2 v?v u (s OR t) NOT q'), ('a a a'), ('a^2 a'), ('a OR a'), ('NOT a b NOT a'), ('a NOT a'), ('a NOT (a)^2'), ('{a b} {b a}'), ('"a b" "A B"'), ('a^6000 a^6000'), ('a^5000 a^5000'), ('x NOT (a OR a^3)') ) v(q); q | pq ---------------------------------------+----------------------------------------------------------------- zeta alpha mid | alpha AND mid AND zeta b OR a | a OR b "x y" z* /r/ w~2 v?v u (s OR t) NOT q | u AND w AND z* AND v?v AND /r/ AND "x y" AND (s OR t) AND NOT q a a a | a^3 a^2 a | a^3 a OR a | a^2 NOT a b NOT a | b AND NOT a a NOT a | "" a NOT (a)^2 | "" {a b} {b a} | (a OR b) AND (a OR b) "a b" "A B" | "a b"^2 a^6000 a^6000 | a^6000 AND a^6000 a^5000 a^5000 | a^10000 x NOT (a OR a^3) | x AND NOT a (14 rows) -- phrases and analysis: multi-token words become phrases, empty leaves drop SELECT q, pq(q) FROM (VALUES ('"Hello World"'), ('"one"'), ('""'), ('" ... "'), ('e-mail'), ('E-Mail^2'), ('don''t stop'), ('--'), ('-- x'), ('x OR --'), ('-- NOT x'), ('x NOT --'), ('x NOT (-- NOT y)'), ('"say \"hi\" \\ now"'), ('"a\b"'), ('foo\(bar\)'), ('\"quoted\"'), ('a_b'), ('x AND ("" OR "")'), ('x AND (-- NOT y)') ) v(q); q | pq ---------------------+------------------ "Hello World" | "hello world" "one" | one "" | "" " ... " | "" e-mail | "e mail" E-Mail^2 | "e mail"^2 don't stop | stop AND "don t" -- | "" -- x | x x OR -- | x -- NOT x | "" x NOT -- | x x NOT (-- NOT y) | x "say \"hi\" \\ now" | "say hi now" "a\b" | ab foo\(bar\) | "foo bar" \"quoted\" | quoted a_b | "a b" x AND ("" OR "") | x x AND (-- NOT y) | x AND NOT y (20 rows) -- boosts: pushed down to leaves, products, shortest float form SELECT q, pq(q) FROM (VALUES ('a^2'), ('a^2.5 b^0'), ('a^0.1'), ('a^.5'), ('a^1e-5'), ('a^1E+3'), ('a^1.0'), ('a^10000'), ('(a b)^2 c^0.5'), ('((a^2 b)^3)'), ('(a OR b^4)^0.5'), ('{x y}^3'), ('"p q"^2'), ('/r.x/^2'), ('fo*^2 OR fo*'), ('a NOT b^5'), ('(a^100)^100'), ('(a^1.1)^1.1'), ('x NOT (a^7 b)^3') ) v(q); q | pq -----------------+----------------------- a^2 | a^2 a^2.5 b^0 | a^2.5 AND b^0 a^0.1 | a^0.1 a^.5 | a^0.5 a^1e-5 | a^1e-05 a^1E+3 | a^1000 a^1.0 | a a^10000 | a^10000 (a b)^2 c^0.5 | a^2 AND b^2 AND c^0.5 ((a^2 b)^3) | a^6 AND b^3 (a OR b^4)^0.5 | a^0.5 OR b^2 {x y}^3 | x^3 OR y^3 "p q"^2 | "p q"^2 /r.x/^2 | /r.x/^2 fo*^2 OR fo* | fo*^3 a NOT b^5 | a AND NOT b (a^100)^100 | a^10000 (a^1.1)^1.1 | a^1.21 x NOT (a^7 b)^3 | x AND NOT (a AND b) (19 rows) -- fuzzy: default distance 1, short terms allow fewer edits SELECT q, pq(q) FROM (VALUES ('foo~'), ('foo~1'), ('foo~2'), ('ab~'), ('abcd~2'), ('abcde~2'), ('abcdef~2'), ('Hello~2^3'), ('\AND~1'), ('foo~ foo~2'), ('(foo~)'), ('ab~ ab'), ('{a a~2}'), ('x-y~1'), ('\(foo-bar\)~1'), ('\(lazzy\)~2'), ('--foobar--~2'), ('--~2') ) v(q); q | pq ---------------+----------- foo~ | foo~1 foo~1 | foo~1 foo~2 | foo~1 ab~ | ab abcd~2 | abcd~1 abcde~2 | abcde~1 abcdef~2 | abcdef~2 Hello~2^3 | hello~1^3 \AND~1 | and~1 foo~ foo~2 | foo~1^2 (foo~) | foo~1 ab~ ab | ab^2 {a a~2} | a^2 x-y~1 | "x y" \(foo-bar\)~1 | "foo bar" \(lazzy\)~2 | lazzy~1 --foobar--~2 | foobar~2 --~2 | "" (18 rows) -- prefix, wildcard, escapes; meta runs are canonicalized SELECT q, pq(q) FROM (VALUES ('fo*'), ('Fo*'), ('fo**'), ('f*o'), ('f?o'), ('a**b?*c'), ('a*?*?b'), ('?a'), ('*a*'), ('foo\*'), ('foo\**'), ('a\?b*'), ('a\\*'), ('\*\?'), ('a\ b*'), ('a\(*'), ('A?B*c'), (E'a*\u0303?b'), (E'ab*\u0303'), (E'\u0303*\u0303') ) v(q); q | pq ---------+-------- fo* | fo* Fo* | fo* fo** | fo* f*o | f*o f?o | f?o a**b?*c | a*b?*c a*?*?b | a??*b ?a | ?a *a* | *a* foo\* | foo foo\** | foo\** a\?b* | a\?b* a\\* | a\\* \*\? | "" a\ b* | a\ b* a\(* | a\(* A?B*c | a?b*c a*̃?b | a?*b ab*̃ | ab* ̃*̃ | "" (20 rows) -- regular expressions: whole-term match, "\/" escapes the delimiter SELECT q, pq(q) FROM (VALUES ('/a.c/'), ('/a\/b/'), ('/a\\/'), ('/a\.b/'), ('/[0-9]+/'), ('/(?i)A.c/'), ('/(?:ab)+/'), ('/x y/'), ('{/a/ b*}'), ('/A/^2 OR /A/') ) v(q); q | pq --------------+----------- /a.c/ | /a.c/ /a\/b/ | /a\/b/ /a\\/ | /a\\/ /a\.b/ | /a\.b/ /[0-9]+/ | /[0-9]+/ /(?i)A.c/ | /(?i)A.c/ /(?:ab)+/ | /(?:ab)+/ /x y/ | /x y/ {/a/ b*} | b* OR /a/ /A/^2 OR /A/ | /A/^3 (10 rows) -- "any of" groups SELECT q, pq(q) FROM (VALUES ('{a b c}'), ('{a}'), ('{a b}^2 c'), ('{a* b?c /d/ e~}'), ('{a b} OR c'), ('x {a b} NOT {c d}') ) v(q); q | pq -------------------+--------------------------------- {a b c} | a OR b OR c {a} | a {a b}^2 c | c AND (a^2 OR b^2) {a* b?c /d/ e~} | e OR a* OR b?c OR /d/ {a b} OR c | a OR b OR c x {a b} NOT {c d} | x AND (a OR b) AND NOT (c OR d) (6 rows) -- keywords as terms need escaping, lowercase keywords are terms SELECT q, pq(q) FROM (VALUES ('\AND'), ('\OR \NOT'), ('and or not'), ('"AND"'), ('ANDROID ORE NOTE'), ('AND*') ) v(q); q | pq ------------------+-------------------------- \AND | and \OR \NOT | not AND or and or not | and AND not AND or "AND" | and ANDROID ORE NOTE | android AND note AND ore AND* | and* (6 rows) -- whitespace and control characters separate words SELECT pq(E'a\tb\nc\rd') AS tabs, pq(E'a\x01b') AS control, pq(E'a\\\x01b') AS escaped_control; tabs | control | escaped_control ---------------------+---------+----------------- a AND b AND c AND d | a AND b | "a b" (1 row) -- canonical output is a fixpoint SELECT q, pq(q) = pq(pq(q)) AS fixpoint FROM (VALUES ('a OR b c NOT (d OR e)^3'), ('{x* y?y /z/} "p q r"^2 NOT w~2'), ('foo\**^2 \AND'), ('/a\\\/b/'), ('(a b)^0 c'), ('x NOT (-- NOT y)') ) v(q); q | fixpoint --------------------------------+---------- a OR b c NOT (d OR e)^3 | t {x* y?y /z/} "p q r"^2 NOT w~2 | t foo\**^2 \AND | t /a\\\/b/ | t (a b)^0 c | t x NOT (-- NOT y) | t (6 rows) -- index argument SELECT sift.parse_query('a', NULL), sift.parse_query(NULL); parse_query | parse_query -------------+------------- a | (1 row) -- InvalidOid selects the default analyzer, including a non-NULL zero OID SELECT sift.parse_query('Hello', 0::oid::regclass); parse_query ------------- hello (1 row) SELECT sift.parse_query('a', 'pg_class'::regclass); ERROR: "pg_class" is not an index SELECT sift.parse_query('a', 'pg_class_oid_index'::regclass); ERROR: "pg_class_oid_index" is not a sift index DROP FUNCTION pq; DROP EXTENSION pg_sift;