agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedURL Decode function
4+ messages / 3 participants
[nested] [flat]
* URL Decode function
@ 2021-04-06 04:34 JORGE MALDONADO <jorgemal1960@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: JORGE MALDONADO @ 2021-04-06 04:34 UTC (permalink / raw)
To: pgsql-sql
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
<https://www.cosmohits.com/ListasPopularidad/ListaPopularidad/1-la-superlista>*
is URL Encoded like this:
*https%3A%2F%2Fwww.cosmohits.com
<http://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
<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&...;
Virus-free.
www.avast.com
<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&...;
<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: URL Decode function
@ 2021-04-06 12:56 MichaelDBA <MichaelDBA@sqlexec.com>
parent: JORGE MALDONADO <jorgemal1960@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: MichaelDBA @ 2021-04-06 12:56 UTC (permalink / raw)
To: JORGE MALDONADO <jorgemal1960@gmail.com>; +Cc: pgsql-sql
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
> <http://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
>
> <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&...;
> Virus-free. www.avast.com
> <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&...;
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: URL Decode function
@ 2021-04-06 13:03 Andreas Joseph Krogh <andreas@visena.com>
parent: MichaelDBA <MichaelDBA@sqlexec.com>
0 siblings, 1 reply; 4+ messages in thread
From: Andreas Joseph Krogh @ 2021-04-06 13:03 UTC (permalink / raw)
To: MichaelDBA <MichaelDBA@sqlexec.com>; +Cc: JORGE MALDONADO <jorgemal1960@gmail.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: URL Decode function
@ 2021-04-06 13:07 MichaelDBA <MichaelDBA@sqlexec.com>
parent: Andreas Joseph Krogh <andreas@visena.com>
0 siblings, 0 replies; 4+ messages in thread
From: MichaelDBA @ 2021-04-06 13:07 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreas@visena.com>; +Cc: JORGE MALDONADO <jorgemal1960@gmail.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2021-04-06 13:07 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-04-06 04:34 URL Decode function JORGE MALDONADO <jorgemal1960@gmail.com>
2021-04-06 12:56 ` MichaelDBA <MichaelDBA@sqlexec.com>
2021-04-06 13:03 ` Andreas Joseph Krogh <andreas@visena.com>
2021-04-06 13:07 ` MichaelDBA <MichaelDBA@sqlexec.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox