PostgREST
How to use ts_rank_cd on full text search?
Environment
- PostgreSQL version: 13.1 & 12.x
- PostgREST version: 7.0.1 (d6050c8)
- Operating system: OSX Catalina & Ubuntu 20.04
Description of issue
When using full text search that's documented here: https://postgrest.org/en/stable/api.html?highlight=order#full-text-search, how do you use ts_rank_cd (https://www.postgresql.org/docs/13/textsearch-controls.html#TEXTSEARCH-RANKING) to rank the results? If you provide me with a good example I can perhaps put together a PR with documentation for it.
Thanks!
Hey @jonasstenberg,
You'll need to create a stored procedure for that: https://postgrest.org/en/stable/api.html#stored-procedures
Alright, thanks!
Don't know Haskell myself but is this a feature planned to be included or is it meant to be implemented in an RPC in the future too?
Don't know Haskell myself but is this a feature planned to be included or is it meant to be implemented in an RPC in the future too?
At this point this is more of a "how would we even do this?" question. Afaik, there hasn't been a suggestion how this could be included in the regular query syntax, yet. I'm failing to come up with something on the spot.
Ok, I understand. I don't really know how such a query syntax would look either so I guess I'd have to settle with the rpc option for now.
Many thanks for the answers though!
There was an idea on https://github.com/PostgREST/postgrest/issues/915#issuecomment-776060792 for calling functions on the url.
It could potentially call /apod?select=rank:ts_rank_cd(...)&order=rank, but that will take a while to get implemented. So RPC is definitely the way to go for now.
There was an idea on #915 (comment) for calling functions on the url.
I understood this as only taking column names as function arguments...
It could potentially call
/apod?select=rank:ts_rank_cd(...)&order=rank, but that will take a while to get implemented.
... and I think ts_rank_cd needs user input. And then we'd need to be able to use aliased expressions by alias in the order param, too. I don't think that's straightforward, either.
So "a while" is a very nice way to put it ;).
Just hit this wall as well. Any chances of this getting put on the roadmap?
Seeing that FTS rank is a common use case (mentioned on https://github.com/PostgREST/postgrest/issues/3820), I was thinking to have a special order_fts_rank query param that would take extra parameters.. but having the rank on the select is also a common use case, so that's a no-go.
It could potentially call /apod?select=rank:ts_rank_cd(...)&order=rank https://github.com/PostgREST/postgrest/issues/1758#issuecomment-779396997
Maybe we can do that with the idea on https://github.com/PostgREST/postgrest/issues/2578#issuecomment-1404037093