Decodes simple URL parameters, getting all of the parameters or just the ones that you want.

get_url_params.sql

CREATE OR REPLACE FUNCTION get_url_params (
  in_url VARCHAR
)
RETURNS TABLE(
  param_number INT,
  raw_key VARCHAR,
  raw_value VARCHAR,
  "key" VARCHAR,
  "value" VARCHAR
)
IMMUTABLE
AS $dbvis$
/*******************************************************************************
 * Author: Chris West
 * Blog Post: http://cwestblog.com/2019/05/20/pl-pgsql-snippet-get_url_params/
 * Gist: https://gist.github.com/westc/7a7e12650683e7863fef43798e2da651
 * Date: 2019-05-20
 * Description:
 *   Decodes simple URL parameters, getting all of the parameters.
 * Example Run:
 *   SELECT * FROM get_url_params('http://example.com/?q=search+term&utm=stuff&x=Hell%C3%B6%20World%21');
 *******************************************************************************/
BEGIN
  RETURN QUERY
    SELECT
      t.param_number,
      t.raw_key_value[1],
      t.raw_key_value[2],
      t.key_value[1],
      t.key_value[2]
    FROM (
      SELECT
        t.param_number,
        array_agg(t.raw_value ORDER BY t.name = 'value') AS raw_key_value,
        array_agg(t.value ORDER BY t.name = 'value') AS key_value
      FROM (
        SELECT
          t.param_number,
          t.name,
          t.value::VARCHAR AS raw_value,
          convert_from(
            string_agg(
              CASE
                WHEN matches[2] IS NOT NULL THEN
                  decode(matches[2], 'hex')
                WHEN matches[1] = '+' THEN
                  ' '::BYTEA
                ELSE
                  matches[1]::BYTEA
              END,
              ''::BYTEA
            ),
            'utf8'
          )::VARCHAR AS value
        FROM (
          SELECT
            t.*,
            regexp_matches(
              t.value,
              '(\+|[^%\+]+|%([0-9A-F][0-9A-F])|.)',
              'gi'
            ) AS matches
          FROM (
            SELECT
              row_number() OVER ()::INT AS param_number,
              unnest('{key,value}'::VARCHAR[]) AS name,
              unnest(a) AS value
            FROM regexp_matches(
              (regexp_match(in_url, '\?[^\?]+$'))[1],
              '[\?&]([^&=]+)(?:=([^\?&]*))?',
              'g'
            ) AS t(a)
          ) AS t
        ) AS t
        GROUP BY 1, 2, 3
      ) AS t
      GROUP BY 1
    ) AS t;
END
$dbvis$ LANGUAGE plpgsql