Postgrest: Extend @@ for plainto_tsquery phraseto_tsquery support

[qjhart]

For our API, we use a tsquery interface. Modifying user input for a good text query can be a bit of a challenge on the client side. We are building a quick rpc interface, to use the postgres services to do this, but that results in two calls to the server.

http --json https://wine-api.library.ucdavis.edu/rpc/strto_tsquery str='Moet & Chandon'
#[  {  "phrase": "moet <-> chandon",   "plain": "moet & chandon" } ]
http --json https://dev-wine-api.library.ucdavis.edu/catalogs select==catalog_id q=='@@.moet <-> chandon'

It seems like there are more sophisticated questions about tsquery, but what about a simple suffix for plain and phrase queries, so you could do interpret the string directly.

http --json https://dev-wine-api.library.ucdavis.edu/catalogs select==catalog_id q=='@@z.Moet & Chandon'

I think the changes would be pretty simple? If I'm not missing something.

diff --git a/src/PostgREST/QueryBuilder.hs b/src/PostgREST/QueryBuilder.hs
index d01f317..8087b8e 100644
--- a/src/PostgREST/QueryBuilder.hs
+++ b/src/PostgREST/QueryBuilder.hs
@@ -421,6 +421,8 @@ pgFmtFilter table (Filter fld (Operation hasNot_ ex)) = notOp <> " " <> case ex
      "like"  -> unknownLiteral (T.map star val)
      "ilike" -> unknownLiteral (T.map star val)
      "@@"    -> "to_tsquery(" <> unknownLiteral val <> ") "
+     "@@z"    -> "phraseto_tsquery(" <> unknownLiteral val <> ") "
+     "@@p"    -> "plainto_tsquery(" <> unknownLiteral val <> ") "
      "is"    -> whiteList val
      "isnot" -> whiteList val
      _       -> unknownLiteral val
diff --git a/src/PostgREST/Types.hs b/src/PostgREST/Types.hs
index 3d2967b..2046cac 100644
--- a/src/PostgREST/Types.hs
+++ b/src/PostgREST/Types.hs
@@ -153,6 +153,8 @@ operators = M.fromList [
   ("isnot", "IS NOT"),
   ("is", "IS"),
   ("@@", "@@"),
+  ("@@z", "@@"),
+  ("@@p", "@@"),
   ("@>", "@>"),
   ("<@", "<@")]

If that sounds reasonable, I could submit a better pull request with tests if that would help.

[begriffs]

Yeah I don't see why not. It's a small change that allows more sophisticated use of the full-text functionality. Please do send a PR, thank you!

[ruslantalpa]

The code changes here will not work since the @@z will not carry on deeper into pgFmtFilter, you will have there @@ (but it can be worked out).

As for the interface, if you want to work on it, i would suggest another approach:
We already have things like in, not.in, eq, not.eq so i would say a better interface is
col=@@.aa, col=plain.@@.aa, col=phrase.@@.aa

[steve-chavez]

The phraseto_tsquery function is only available starting from PostgreSQL 9.6, I realized this while testing the feature in CircleCI https://circleci.com/gh/steve-chavez/postgrest/409, should we increase the required PostgreSQL version to implement this feature?

[ruslantalpa]

i would say no, let this feature be present and if used in <9.6 let the query fail.
as for tests in circle, if we can't get 9.6 there, then just mark the specific tests as pending (after testing offline with 9.6) and leave them pending until there is 9.6 in circle