|
84 | 84 | identical).
|
85 | 85 | </entry>
|
86 | 86 | </row>
|
| 87 | + <row> |
| 88 | + <entry><function>substring_similarity(text, text)</function><indexterm><primary>substring_similarity</primary></indexterm></entry> |
| 89 | + <entry><type>real</type></entry> |
| 90 | + <entry> |
| 91 | + Returns a number that indicates how similar the first string |
| 92 | + to the most similar substring of the second string. The range of |
| 93 | + the result is zero (indicating that the two strings are completely |
| 94 | + dissimilar) to one (indicating that the first string is identical |
| 95 | + to substring of the second substring). |
| 96 | + </entry> |
| 97 | + </row> |
87 | 98 | <row>
|
88 | 99 | <entry><function>show_trgm(text)</function><indexterm><primary>show_trgm</primary></indexterm></entry>
|
89 | 100 | <entry><type>text[]</type></entry>
|
|
111 | 122 | Returns the same value passed in.
|
112 | 123 | </entry>
|
113 | 124 | </row>
|
| 125 | + <row> |
| 126 | + <entry><function>show_substring_limit()</function><indexterm><primary>show_substring_limit</primary></indexterm></entry> |
| 127 | + <entry><type>real</type></entry> |
| 128 | + <entry> |
| 129 | + Returns the current similarity threshold used by the <literal><%</> |
| 130 | + operator. This sets the minimum substring similarity between |
| 131 | + two phrases. |
| 132 | + </entry> |
| 133 | + </row> |
| 134 | + <row> |
| 135 | + <entry><function>set_substring_limit(real)</function><indexterm><primary>set_substring_limit</primary></indexterm></entry> |
| 136 | + <entry><type>real</type></entry> |
| 137 | + <entry> |
| 138 | + Sets the current substring similarity threshold that is used by |
| 139 | + the <literal><%</> operator. The threshold must be between |
| 140 | + 0 and 1 (default is 0.6). Returns the same value passed in. |
| 141 | + </entry> |
| 142 | + </row> |
114 | 143 | </tbody>
|
115 | 144 | </tgroup>
|
116 | 145 | </table>
|
|
136 | 165 | <function>set_limit</>.
|
137 | 166 | </entry>
|
138 | 167 | </row>
|
| 168 | + <row> |
| 169 | + <entry><type>text</> <literal><%</literal> <type>text</></entry> |
| 170 | + <entry><type>boolean</type></entry> |
| 171 | + <entry> |
| 172 | + Returns <literal>true</> if its arguments have a substring similarity |
| 173 | + that is greater than the current substring similarity threshold set by |
| 174 | + <function>set_substring_limit</>. |
| 175 | + </entry> |
| 176 | + </row> |
139 | 177 | <row>
|
140 | 178 | <entry><type>text</> <literal><-></literal> <type>text</></entry>
|
141 | 179 | <entry><type>real</type></entry>
|
@@ -203,6 +241,21 @@ SELECT t, t <-> '<replaceable>word</>' AS dist
|
203 | 241 | a small number of the closest matches is wanted.
|
204 | 242 | </para>
|
205 | 243 |
|
| 244 | + <para> |
| 245 | + Also you can use an index on the <structfield>t</> column for substring |
| 246 | + similarity. For example: |
| 247 | +<programlisting> |
| 248 | +SELECT t, substring_similarity('<replaceable>word</>', t) AS sml |
| 249 | + FROM test_trgm |
| 250 | + WHERE '<replaceable>word</>' <% t |
| 251 | + ORDER BY sml DESC, t; |
| 252 | +</programlisting> |
| 253 | + This will return all values in the text column that have a substring |
| 254 | + which sufficiently similar to <replaceable>word</>, sorted from best |
| 255 | + match to worst. The index will be used to make this a fast operation |
| 256 | + even over very large data sets. |
| 257 | + </para> |
| 258 | + |
206 | 259 | <para>
|
207 | 260 | Beginning in <productname>PostgreSQL</> 9.1, these index types also support
|
208 | 261 | index searches for <literal>LIKE</> and <literal>ILIKE</>, for example
|
|