Re: How to use full-text search URL parser to filter query results bydomain name?

On 07.04.2019 07:06, Jess Wren wrote:
> However, I can't figure out how I would integrate this into the above 
> query to filter out duplicate domains from the results. And because this 
> is the docs for "testing and debugging text search 
> <https://www.postgresql.org/docs/11/textsearch-debugging.html#TEXTSEARCH-PARSER-TESTING>", 
> I don't know if this use of `ts_parse()` is even related to how the URL 
> parser is intended to be used in practice.
> 
> How would I use the "host" parser in my query above to return one row 
> per domain? Also, how would I appropriately index the "links" table for 
> "host" and "url" token lookup?

I think it is normal to use ts_parse(). And I suppose you might use 
windows functions.

For example, you have table links:

=# create table links (score int, link text);
=# insert into links values
   (1, 'http://www.foo.com/bar'),
   (2, 'http://www.foo.com/foo'),
   (2, 'http://www.bar.com/foo'),
   (1, 'http://www.bar.com/bar');

You can use the following query:

=# with l as (
   select score, token, link,
     rank() over (partition by token order by score) as rank
   from links,
     lateral ts_parse('default', link)
   where tokid = 6)
select score, token, link from l where rank = 1;
  score |    token    |          link
-------+-------------+------------------------
      1 | www.bar.com | http://www.bar.com/bar
      1 | www.foo.com | http://www.foo.com/bar

It is just the idea, probably the query might be simpler.

-- 
Arthur Zakirov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company