Identifying which column matches a full text search - Mailing list pgsql-sql

<div class="Section1"><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Courier New"">Hi all,</span><p
class="MsoNormal"><spanstyle="font-size:10.0pt;font-family:"Courier New""> </span><p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"CourierNew"">The following example is given in the Postgres 8.3 manual regarding
howto create a single ts_vector column for two existing columns:</span><p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"CourierNew""> </span><p class="MsoNormal" style="margin-left:36.0pt"><span
style="font-size:10.0pt;
font-family:"Courier New"">ALTER TABLE pgweb ADD COLUMN textsearchable_index_col tsvector;</span><p class="MsoNormal"
style="margin-left:36.0pt"><spanstyle="font-size:10.0pt; 
font-family:"Courier New"">UPDATE pgweb SET textsearchable_index_col =</span><p class="MsoNormal"
style="margin-left:36.0pt"><spanstyle="font-size:10.0pt; 
font-family:"Courier New"">     to_tsvector('english', coalesce(title,'') || coalesce(body,''));</span><p
class="MsoNormal"style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; 
margin-left:36.0pt"><span style="font-size:12.0pt;font-family:"Times New Roman","serif"">Then we create a GIN index to
speedup the search: </span><p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:10.0pt; 
font-family:"Courier New"">CREATE INDEX textsearch_idx ON pgweb USING gin(textsearchable_index_col);</span><p
class="MsoNormal"style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; 
margin-left:36.0pt"><span style="font-size:12.0pt;font-family:"Times New Roman","serif"">Now we are ready to perform a
fastfull text search: </span><p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:10.0pt; 
font-family:"Courier New"">SELECT title</span><p class="MsoNormal" style="margin-left:36.0pt"><span
style="font-size:10.0pt;
font-family:"Courier New"">FROM pgweb</span><p class="MsoNormal" style="margin-left:36.0pt"><span
style="font-size:10.0pt;
font-family:"Courier New"">WHERE textsearchable_index_col @@ to_tsquery('create & table')</span><p
class="MsoNormal"style="margin-left:36.0pt"><span style="font-size:10.0pt; 
font-family:"Courier New"">ORDER BY last_mod_date DESC LIMIT 10;</span><p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"CourierNew""> </span><p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"CourierNew"">Using this approach. Is there any way of retrieving which of the
originaltwo columns the match was found in?</span><p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"CourierNew""> </span><p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"CourierNew"">Any help would be much appreciated,</span><p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"CourierNew"">Ryan</span></div>