Split a URL into its parts

CREATE TEMP TABLE urls AS SELECT * FROM (VALUES
  (1, 'https://shop.example.com:8080/a/b?q=red%20shoes&page=2#top'),
  (2, 'http://example.org'),
  (3, NULL)
) t(id, url);
CREATE TEMP TABLE result AS
WITH parts AS (
  SELECT id, regexp_extract(url, '^(?:([a-z][a-z0-9+.-]*):)?(?://([^/?#:]*)(?::(\d+))?)?([^?#]*)(?:\?([^#]*))?(?:#(.*))?$',
                            ['scheme', 'host', 'port', 'path', 'query', 'fragment']) AS p FROM urls)
SELECT id, p.*, url_decode(nullif(regexp_extract(p.query, '(?:^|&)q=([^&]*)', 1), '')) AS q_param,
       url_encode('red shoes & more') AS encoded
FROM parts;
SELECT * FROM result ORDER BY id;
SELECT CASE WHEN ((SELECT [scheme, host, port, path, query, fragment, q_param] FROM result WHERE id = 1) = ['https', 'shop.example.com', '8080', '/a/b', 'q=red%20shoes&page=2', 'top', 'red shoes'] AND (SELECT [scheme, host, path, query] FROM result WHERE id = 2) = ['http', 'example.org', '', ''] AND (SELECT q_param FROM result WHERE id = 2) IS NULL AND (SELECT encoded FROM result WHERE id = 1) = 'red%20shoes%20%26%20more' AND (SELECT count(*) FROM result WHERE id = 3 AND host IS NULL) = 1) THEN 'as expected' ELSE error('the URLs were not split into scheme, host, port, path, query and fragment, or the q parameter was not decoded, or text was not encoded') END AS verdict;