Обсуждение: URL Decode function

CREATE OR REPLACE FUNCTION decode(expr varchar, search varchar, result varchar, dflt varchar) RETURNS varchar AS
$$      
BEGIN
CASE WHEN expr = search THEN RETURN result; ELSE RETURN dflt; END CASE;
END
$$ LANGUAGE plpgsql;

Hi,

In my DB, one of my tables has a field that is URL Encoded (its value is a URL of course).

For example, the value:

https://www.cosmohits.com/ListasPopularidad/ListaPopularidad/1-la-superlista

is URL Encoded like this:

https%3A%2F%2Fwww.cosmohits.com%2FListasPopularidad%2FListaPopularidad%2F1-la-superlista

Is there a PostgreSQL function to URL Decode such field when performing a SELECT statement? I have not found anything in documentation.

Best regards,

Jorge Maldonado

Virus-free. www.avast.com

Hi,

You can create your own "decode" function in pg like this example using varchars as input.  Then simply create other overloaded "decode" functions for different input datatypes.
 

CREATE OR REPLACE FUNCTION decode(expr varchar, search varchar, result varchar, dflt varchar) RETURNS varchar AS
$$      
BEGIN
CASE WHEN expr = search THEN RETURN result; ELSE RETURN dflt; END CASE;
END
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION decode_url_part(p varchar) RETURNS varchar AS $$
SELECT convert_from(CAST(E'\\x' || string_agg(CASE WHEN length(r.m[1]) = 1 THEN encode(convert_to(r.m[1], 'SQL_ASCII'), 'hex') ELSE substring(r.m[1] from 2 for 2) END, '') AS bytea), 'UTF8')
FROM regexp_matches($1, '%[0-9a-f][0-9a-f]|.', 'gi') AS r(m);
$$ LANGUAGE SQL IMMUTABLE STRICT;

På tirsdag 06. april 2021 kl. 14:56:10, skrev MichaelDBA <MichaelDBA@sqlexec.com>:

Hi,

You can create your own "decode" function in pg like this example using varchars as input.  Then simply create other overloaded "decode" functions for different input datatypes.
 

CREATE OR REPLACE FUNCTION decode(expr varchar, search varchar, result varchar, dflt varchar) RETURNS varchar AS
$$      
BEGIN
CASE WHEN expr = search THEN RETURN result; ELSE RETURN dflt; END CASE;
END
$$ LANGUAGE plpgsql;

 

Am I the only one who cannot find anything related to the OP's question in this answer?

 

I found this on SO:

 

CREATE OR REPLACE FUNCTION decode_url_part(p varchar) RETURNS varchar AS $$
SELECT convert_from(CAST(E'\\x' || string_agg(CASE WHEN length(r.m[1]) = 1 THEN encode(convert_to(r.m[1], 'SQL_ASCII'), 'hex') ELSE substring(r.m[1] from 2 for 2) END, '') AS bytea), 'UTF8')
FROM regexp_matches($1, '%[0-9a-f][0-9a-f]|.', 'gi') AS r(m);
$$ LANGUAGE SQL IMMUTABLE STRICT;

 

andreak@[local]:5432 13.2 test=# select decode_url_part('https%3A%2F%2Fwww.cosmohits.com%2FListasPopularidad%2FListaPopularidad%2F1-la-superlista');
┌──────────────────────────────────────────────────────────────────────────────┐
│                               decode_url_part                                │
├──────────────────────────────────────────────────────────────────────────────┤
│ https://www.cosmohits.com/ListasPopularidad/ListaPopularidad/1-la-superlista │
└──────────────────────────────────────────────────────────────────────────────┘
(1 row)
 

--
Andreas Joseph Krogh