CODEWITHSUNDEEP
postgresqlfull-text-search
Dan Winer
Dan Winer

Reputation: 69

Simplifying PostgreSQL Full Text Search tsvector and tsquery with aliases

I am trying to simplify this query as it is going to be dynmaically generated by PHP and I would like to reduce the processing overhead (the real query will be much longer but the structure will be the same!). 

SELECT title, type_name, ts_rank_cd(ARRAY[0.1,0.2,0.4,1.0],
setweight(to_tsvector(coalesce(title,'')), 'A')
||
setweight(to_tsvector(coalesce(type_name,'')), 'B')
,
to_tsquery('search & query'))
FROM TestView WHERE
setweight(to_tsvector(coalesce(title,'')), 'D')
||
setweight(to_tsvector(coalesce(type_name,'')), 'B')
@@
to_tsquery('search & query');

I am looking to try to reduce the need to specify the tsquery and tsvector twice by defining something like an alias so that it does not have to be specified twice. Something like this (which fails, I am not sure if it is even close to correct!)

SELECT title, type_name, ts_rank_cd(ARRAY[0.1,0.2,0.4,1.0],
searchvector
,
searchquery
FROM TestView WHERE
setweight(to_tsvector(coalesce(title,'')), 'D')
||
setweight(to_tsvector(coalesce(type_name,'')), 'B') AS searchvector
@@
to_tsquery('search & query') AS searchquery;

Is this possible or am I just stuck with generating it all twice.

For context 'TestView' is a view generated from a number of tables.

Any help much appreciated!

Upvotes: 2

Views: 3330

Answers (1)

user330315
user330315

Reputation: 

SELECT title, 
       type_name,
       ts_rank_cd(ARRAY[0.1,0.2,0.4,1.0],weight,query)
FROM (
    SELECT title, 
           type_name, 
           setweight(to_tsvector(coalesce(title,'')), 'A')
              ||setweight(to_tsvector(coalesce(type_name,'')), 'B') as weight,
           to_tsquery('search & query') as query
    FROM TestView 
) t
WHERE weight @@ query

Upvotes: 2

Related Questions