Postgresql – Retrieve list of matched words in PostgreSQL

database-tuningpostgresql

I'm new to PostgreSQL and really more than your more basic queries/inserts.

I've created a TSVector column to my table that I'm searching and have set the column to look at the specific column in the table.

Now what I'm trying to do is find out what words matched in a query.

If I had the query

SELECT ts_headline('the big red ball' , 'big & ball');

It would obviously return:

"the big red ball"

Is there a way I can get it to return a list instead so I just see big and red in seperate rows?

Best Answer

What about using the regexp_split_to_table function?

SELECT regexp_split_to_table(ts_headline('the big red ball'::text , 'big & ball'),' ');
 regexp_split_to_table 
-----------------------
 the
 <b>big</b>
 red
 <b>ball</b>
(4 rows)