Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lTlFo-0007ES-A1 for pgsql-sql@arkaria.postgresql.org; Tue, 06 Apr 2021 12:56:24 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lTlFm-0001bv-7R for pgsql-sql@arkaria.postgresql.org; Tue, 06 Apr 2021 12:56:22 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lTlFm-0001bn-0i for pgsql-sql@lists.postgresql.org; Tue, 06 Apr 2021 12:56:22 +0000 Received: from mout.perfora.net ([74.208.4.196]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lTlFj-0006Hx-58 for pgsql-sql@postgresql.org; Tue, 06 Apr 2021 12:56:21 +0000 Received: from [192.168.1.165] ([98.169.214.254]) by mrelay.perfora.net (mreueus004 [74.208.5.2]) with ESMTPSA (Nemesis) id 1MK470-1l9Kwy3Uf0-00LT0T; Tue, 06 Apr 2021 14:56:14 +0200 Subject: Re: URL Decode function To: JORGE MALDONADO Cc: pgsql-sql@postgresql.org References: From: MichaelDBA Message-ID: Date: Tue, 6 Apr 2021 08:56:10 -0400 User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:52.0) Gecko/20100101 PostboxApp/7.0.47 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------FDD6BB3EA43C3C92C694D9C0" Content-Language: en-US X-Provags-ID: V03:K1:5KjB6MJDY3hY18Ev6WrlJwL9tKVwgZ0l5heC0Md5rupcyhqXpIg Z1DEUGsF+rLW5lUowxe6YeCLbU2WonmsOz1J6JFi0rse0W0vcOjVfWMuDYR9lBbkwSaarrf nr0gZdhgt8rCCovlk5YCYrtZ6gP8GF3gyjvvD7El36YwC1fnERkZDxEB7pa0kxT/ymbV5G0 ZzA0VbeOnEYPP60emEp3Q== X-Spam-Flag: NO X-UI-Out-Filterresults: notjunk:1;V03:K0:3KXHalPb95k=:4LPSOBD7WGwGbuPqWd9j/9 4g/M4yUJLJVDq6qAeZgYs9tnzzK1zegFmWaFDT5UB/Ej9IzPVVlNy74r9GrI99p/2I+/17tkz tudMgG/8lhhnAF5Lbyj6kRl5zef4jOl0DplVVRZZJmJIEbDGiR8kOU5FAi+EyUYynYEUAZGyZ KrQM53Z0ViBem394QnyvhflhqO/lBYEVU1/h3b05lpMeeTdLdKEnOqSAVO1t65Cq7ppkHQRw6 8S/u3zNp/5XRFm2JUTaewYC/nNSW0nQvDiXWUWztZtuZppbBQWFoaSTbUhwvkCJfPcsqB0gAa oCxRrzghUD9E663iMxxtijxJzN3bJceiG4SqyP7SfgY5uf8XIhtXLf2ijXxmWyu9CK/9Fyjjz +tfOPELyjK3U+vc1dwfVavvraVK2Td8/7Cd2ZPqKWc5DzPdtc6po0EAEO/p3HTlVjGhCFe8Gv 2GN3E5wplcjYhza9h7Uw7sA+xhVQojo9nqMf45668fvVNst4TkbF List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------FDD6BB3EA43C3C92C694D9C0 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit 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; Regards, Michael Vitale JORGE MALDONADO wrote on 4/6/2021 12:34 AM: > 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 > > > --------------FDD6BB3EA43C3C92C694D9C0 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit 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;

Regards,
Michael Vitale

JORGE MALDONADO wrote on 4/6/2021 12:34 AM:
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:
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

--------------FDD6BB3EA43C3C92C694D9C0--