Javascript encodeURI as a function in postgresql?

Question

Is there any function / stored procedure in PostgreSQL / plpgsql that is similar to encodeURI javascripts?

What does it mean? Javascript has a convenient built-in function to encode any type of URL:

encodeURI (url) -> returns the encoded URL

For example: encodeURI('http://hu.wikipedia.org/wiki/São_Paulo')→ returns a string that"http://hu.wikipedia.org/wiki/S%C3%A3o_Paulo"

I am looking for exactly the same.

I do not want to encode each parameter separately. I don't need the javascript function encodeURIComponent, which is not the same. In the above example, we get another conclusion with

encodeURIComponent('http://hu.wikipedia.org/wiki/São_Paulo')

-> "http%3A%2F%2Fhu.wikipedia.org%2Fwiki%2FS%C3%A3o_Paulo"

It encodes the entire string, not just part of the path. So this is not what I am looking for. I need a plpgsql function that gives the equivalent output of the javascript encodeURI function.

Thank!

Answer 1

PostgreSQL url_enocode,

postgres=# select url_encode('http://hu.wikipedia.org/wiki/São_Paulo');
                      url_encode                       
───────────────────────────────────────────────────────
http%3A%2F%2Fhu.wikipedia.org%2Fwiki%2FS%C3%A3o_Paulo

postgres=# select uri_encode('http://hu.wikipedia.org/wiki/São_Paulo');
               uri_encode                  
---------------------------------------------
http://hu.wikipedia.org/wiki/S%C3%A3o_Paulo

Answer 2

, C:

CREATE OR REPLACE FUNCTION urlencode(in_str text, OUT _result text)
    STRICT IMMUTABLE AS $urlencode$
DECLARE
    _i      int4;
    _temp   varchar;
    _ascii  int4;
BEGIN
    _result = '';
    FOR _i IN 1 .. length(in_str) LOOP
        _temp := substr(in_str, _i, 1);
        IF _temp ~ '[0-9a-zA-Z:/@._?#-]+' THEN
            _result := _result || _temp;
        ELSE
            _ascii := ascii(_temp);
            IF _ascii > x'07ff'::int4 THEN
                RAISE EXCEPTION 'Won''t deal with 3 (or more) byte sequences.';
            END IF;
            IF _ascii <= x'07f'::int4 THEN
                _temp := '%'||to_hex(_ascii);
            ELSE
                _temp := '%'||to_hex((_ascii & x'03f'::int4)+x'80'::int4);
                _ascii := _ascii >> 6;
                _temp := '%'||to_hex((_ascii & x'01f'::int4)+x'c0'::int4)
                            ||_temp;
            END IF;
            _result := _result || upper(_temp);
        END IF;
    END LOOP;
    RETURN ;
END;
$urlencode$ LANGUAGE plpgsql;

:

# select urlencode('http://hu.wikipedia.org/wiki/São_Paulo');
-[ RECORD 1 ]------------------------------------------
urlencode | http://hu.wikipedia.org/wiki/S%C3%A3o_Paulo

Answer 3

PL/V8... ?

create function encode_uri(text) returns text language plv8 strict immutable as $$
  return encodeURI($1);
$$;

Answer 4

" 3 ( ) ". , @vyegorov , , , bytea "%".

CREATE OR REPLACE FUNCTION urlencode(in_str text, OUT _result text)
    STRICT IMMUTABLE AS $urlencode$
DECLARE
    _i      int4;
    _temp   varchar;
    _hex    varchar;
    _ascii  int4;
BEGIN
    _result = '';
    FOR _i IN 1 .. length(in_str) LOOP
        _temp := substr(in_str, _i, 1);
        IF _temp ~ '[0-9a-zA-Z:/@._?#-]+' THEN
            _result := _result || _temp;
        ELSE
            _hex := encode(_temp::bytea, 'hex');
            _temp := '';
            WHILE LENGTH(_hex) > 0 LOOP
                _temp := _temp || '%' || SUBSTRING(_hex, 1, 2);
                _hex := SUBSTRING(_hex, 3, 999);
            END LOOP;
            _result := _result || upper(_temp);
        END IF;
    END LOOP;
    RETURN ;
END;
$urlencode$ LANGUAGE plpgsql;

,

SELECT urlencode('a') UNION ALL  --> "a"
SELECT urlencode('À') UNION ALL  --> "%C3%80"
SELECT urlencode('Ā') UNION ALL  --> "%C4%80"
SELECT urlencode('ə') UNION ALL  --> "%C9%99"
SELECT urlencode('α') UNION ALL  --> "%CE%B1"
SELECT urlencode('가') UNION ALL --> "%EA%B0%80"
SELECT urlencode('上') UNION ALL --> "%E4%B8%8A"
SELECT urlencode('い')           --> "%E3%81%84"

Answer 5

" SQL" ( plv8, plpython plpgsql) , ( 3- 4- ):

create or replace function urlencode(text) returns text as $$
  select
    string_agg(
      case
        when ol>1 or ch !~ '[0-9a-zA-Z:/@._?#-]+' 
          then regexp_replace(upper(substring(ch::bytea::text, 3)), '(..)', E'%\\1', 'g')
        else ch
      end,
      ''
    )
  from (
    select ch, octet_length(ch) as ol
    from regexp_split_to_table($1, '') as ch
  ) as s;
$$ language sql immutable strict;

(: https://github.com/NikolayS/postgrest-google-translate/pull/8)

Answer 6

The use of :: bytea in the answers of Zohra and Nick is incorrect. Do SELECT '\' :: bytea to see why.

In both cases, convert_to (x, 'utf-8') will give the desired result.