CODEWITHSUNDEEP
postgresqlpostgresql-9.2
svoop
svoop

Reputation: 3464

Another way to write a complex tsvector query on hstore columns

I'm generating rather complex fulltext searches on hstore columns such as:

SELECT "users".* 
  FROM "users"  
  WHERE (
    TO_TSVECTOR('german', description -> 'de') @@ PLAINTO_TSQUERY('german', 'foobar') OR 
    TO_TSVECTOR('french', description -> 'fr') @@ PLAINTO_TSQUERY('french', 'foobar') OR 
    TO_TSVECTOR('english', description -> 'en') @@ PLAINTO_TSQUERY('english', 'foobar')
  )

Due to limitations on the application layer, I can't put together the above SQL. All I can do is to build a query which would look as follows:

WHERE [whatever] [predicate] 'foobar'

Say with the = predicate:

WHERE [whatever] = 'foobar'

Now, I'm pretty sure it's not possible to rewrite the complex query above in a manner I can handle on the application layer. But I'd love to be proven wrong here.

Thanks for your ideas!

Upvotes: 0

Views: 193

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125444

WHERE
    (TO_TSVECTOR('german', description -> 'de') @@
        PLAINTO_TSQUERY('german', 'foobar')
    )::int +
    (TO_TSVECTOR('french', description -> 'fr') @@
        PLAINTO_TSQUERY('french', 'foobar')
    )::int +
    (TO_TSVECTOR('english', description -> 'en') @@
        PLAINTO_TSQUERY('english', 'foobar')
    )::int
    > '0'

A cast of a boolean to integer is 0 or 1. When comparing an integer to a text the text will be cast to integer.

Upvotes: 1

Related Questions