agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: MichaelDBA <MichaelDBA@sqlexec.com>
To: Andreas Joseph Krogh <andreas@visena.com>
Cc: JORGE MALDONADO <jorgemal1960@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: URL Decode function
Date: Tue, 6 Apr 2021 09:07:03 -0400
Message-ID: <13235391-a48a-e855-4782-c24864af582e@sqlexec.com> (raw)
In-Reply-To: <VisenaEmail.34.12921fa671e91ac5.178a744245a@tc7-visena>
References: <CAAY=A79T+2DtCt6pv2AKAnP2Azsb6MPLJxZSV0C3GS=AvjOq9g@mail.gmail.com>
<ed9bfcd4-f052-5ccd-5304-b70f8643a171@sqlexec.com>
<VisenaEmail.34.12921fa671e91ac5.178a744245a@tc7-visena>
ha, funny. I thought he was looking for something that acts like the
oracle decode function. Now that I reread it, it is really talking
about endcoding/decoding stuff.
my bad
Regards,
Michael Vitale
Andreas Joseph Krogh wrote on 4/6/2021 9:03 AM:
> 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]:543213.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)
Message-ID: <13235391-a48a-e855-4782-c24864af582e@sqlexec.com>
Permalink: ../13235391-a48a-e855-4782-c24864af582e@sqlexec.com/
Also on: postgresql.org/message-id/13235391-a48a-e855-4782-c24864af582e@sqlexec.com
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: MichaelDBA@sqlexec.com, andreas@visena.com, jorgemal1960@gmail.com
Subject: Re: URL Decode function
In-Reply-To: <13235391-a48a-e855-4782-c24864af582e@sqlexec.com>
* 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