Jooq: Add support for PostgreSQL textsearch

[lukaseder]

See the documentation:

• http://www.postgresql.org/docs/9.3/static/functions-textsearch.html

And the feature request:

• https://groups.google.com/forum/#!topic/jooq-user/3FNKnkBgoSc

[facundofarias]

Hi Lukas, no news on this, right? :-1:

[lukaseder]

Thank you for your enquiry, Facundo. This feature hasn't been a priorty for us right now. Most PostgreSQL text search features can be covered by writing "plain SQL" utility methods, e.g.

public static Field<Object> toTsvector(Field<String> field) {
    return DSL.field("to_tsvector({0})", field);
}

[facundofarias]

Okay, perfect. Works like a charm!
Thanks a lot!

[ruairiquinn]

Hi Lukas,
Maybe an obvious question but how would you combine tsvector and tsquery? e.g.

SELECT p.page_id,
  p.title,
  p.long_description
FROM pages as p
WHERE to_tsvector(p.title || p.long_description) @@ to_tsquery('cloud:* | protection:*')

[lukaseder]

@ruairiquinn: The "obvious" answer would be like this:

DSL.field("to_tsvector({0} || {1}) @@ to_tsquery({2})",
    Boolean.class,
    p.TITLE, p.LONG_DESCRIPTION, DSL.inline("cloud:* | protection:*"));

This is certainly not as lean as it should be. In Java, it's not easy to generate new types that encapsulate this syntax. If you're using Scala, by coincidence, I could see a couple of tricks using implicit class...

[facundofarias]

Shouldnt you also use COALESCE for each field inside of the to_tsvector function? Otherwise a NULL value on any field does not match the search.

[ruairiquinn]

Thanks @lukaseder, exactly what I was after.

@facundofarias Thanks also for the pointer on COALESCE, luckily the fields in question are NON NULL.