PostgreSQL method to parse URLs

Although PostgreSQL supports a large number of data types, there seems to be no corresponding type that can be stored for URLs. So how do we process URL data in the database?

First of all, it depends on what the URL data you want to store is used for. If it is just to print out the output during future query, then isn't it enough to use types such as varchar or text?

But sometimes we don’t just query these URL data. We may hope to query some additional information through these URLs, such as the protocol used by the URL, the host name, etc. Here we can use the ts_debug function to parse.

The function ts_debug allows simple testing of a text search configuration.

ts_debug([ config regconfig, ] document text,
         OUT alias text,
         OUT description text,
         OUT token text,
         OUT dictionaries regdictionary[],
         OUT dictionary regdictionary,
         OUT lexemes text[])
         returns setof record

ts_debug returns one line for each token identified by the parser in text. The returned column is:

alias text: short name of the token type
description text: Description of the token type
token text: token text
dictionaries regdictionary[]: dictionary configured for this token type selection
dictionary regdictionary: The dictionary that recognizes the token, and if no dictionary can be recognized, it is NULL
lexemes text[] ：
Identify the word position generated by the dictionary of the token, and is NULL if no dictionary can be recognized; an empty array ({}) means that the token is recognized as a stop word

Therefore, we can parse the URL in the following way:

bill@bill=>SELECT
bill-#     alias
bill-#     ,description
bill-#     ,token
bill-# FROM ts_debug('/weixin_39540651');
  alias   |  description  |             token
----------+---------------+-------------------------------
 protocol | Protocol head | https://
 url      | URL           | /weixin_39540651
 host     | Host          | 
 url_path | URL path      | /weixin_39540651
(4 rows)

Reference link:

/questions/41633436/datatype-for-a-url-in-postgresql
/docs/13/#TEXTSEARCH-CONFIGURATION-TESTING

This is the end of this article about PostgreSQL's URL parsing method. For more related PostgreSQL URL parsing content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!