postgresql - Match a phrase ending in a prefix with full text search

Question

I'm looking for a way to emulate something like SELECT * FROM table WHERE attr LIKE '%text%' using a tsvector in PostgreSQL.

I've created a tsvector attribute without using a dictionary. Now, a query like ...

SELECT title
FROM table
WHERE title_tsv @@ plainto_tsquery('ph:*');  

... would return all titles like 'Physics', 'PHP', etc. But how can I create a query that returns all records where the title start with 'Zend Fram' (which should return for instance 'Zend Framework')?

Of course, I could use something like:

SELECT title
FROM table
WHERE title_tsv @@ to_tsquery('zend')
AND   title_tsv @@ to_tsquery('fram:*');

However, this seems a little awkward.

So, the question is: is there a way to formulate the query given above using something like:

SELECT title
FROM table
WHERE title_tsv @@ to_tsquery('zend fram:*');

Answer 1

SELECT title
FROM table
WHERE title_tsv @@ to_tsquery('zend') and
title_tsv @@ to_tsquery('fram:*')  

is equivalent to:

SELECT title
FROM table
WHERE title_tsv @@ to_tsquery('zend & fram:*')

but of course that finds "Zend has no framework" as well.

You could of course express a regular expression match against title after the tsquery match, but you would have to use explain analyze to make sure that was being executed after the tsquery instead of before.