How to use ts_rank_cd on full text search?

[jonasstenberg]

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!

[steve-chavez]

Hey @jonasstenberg,

You'll need to create a stored procedure for that: https://postgrest.org/en/stable/api.html#stored-procedures

[jonasstenberg]

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?

[wolfgangwalther]

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.

[jonasstenberg]

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!

[steve-chavez]

There was an idea on #915 (comment) 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.

[wolfgangwalther]

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 ;).

[perelin]

Just hit this wall as well. Any chances of this getting put on the roadmap?

[steve-chavez]

Seeing that FTS rank is a common use case (mentioned on #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
#1758 (comment)

Maybe we can do that with the idea on #2578 (comment)