agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Andreas Joseph Krogh <andreas@visena.com>
To: MichaelDBA <MichaelDBA@sqlexec.com>
Cc: JORGE MALDONADO <jorgemal1960@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: URL Decode function
Date: Tue, 6 Apr 2021 15:03:02 +0200 (CEST)
Message-ID: <VisenaEmail.34.12921fa671e91ac5.178a744245a@tc7-visena> (raw)
In-Reply-To: <ed9bfcd4-f052-5ccd-5304-b70f8643a171@sqlexec.com>
References: <CAAY=A79T+2DtCt6pv2AKAnP2Azsb6MPLJxZSV0C3GS=AvjOq9g@mail.gmail.com>
<ed9bfcd4-f052-5ccd-5304-b70f8643a171@sqlexec.com>
På tirsdag 06. april 2021 kl. 14:56:10, skrev MichaelDBA <
MichaelDBA@sqlexec.com <mailto: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
view thread (4+ messages) latest in thread
Message-ID: <VisenaEmail.34.12921fa671e91ac5.178a744245a@tc7-visena>
Permalink: ../VisenaEmail.34.12921fa671e91ac5.178a744245a@tc7-visena/
Also on: postgresql.org/message-id/VisenaEmail.34.12921fa671e91ac5.178a744245a@tc7-visena
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: andreas@visena.com, MichaelDBA@sqlexec.com, jorgemal1960@gmail.com
Subject: Re: URL Decode function
In-Reply-To: <VisenaEmail.34.12921fa671e91ac5.178a744245a@tc7-visena>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox