Postgresql 网址转码 urldecode 函数


CREATE OR REPLACE FUNCTION urldecode_arr(url TEXT)
  RETURNS TEXT AS
$BODY$
DECLARE ret TEXT;

BEGIN
  BEGIN

    WITH STR AS (
        SELECT
          -- array with all non encoded parts, prepend with '' when the string start is encoded
          CASE WHEN $1 ~ '^%[0-9a-fA-F][0-9a-fA-F]'
            THEN ARRAY ['']
          END
          || regexp_split_to_array($1, '(%[0-9a-fA-F][0-9a-fA-F])+', 'i')                plain,

          -- array with all encoded parts
          array(SELECT (regexp_matches($1, '((?:%[0-9a-fA-F][0-9a-fA-F])+)', 'gi')) [1]) encoded
    )
    SELECT
      string_agg(plain [i] || coalesce(convert_from(decode(replace(encoded [i], '%', ''), 'hex'), 'GB18030'), ''), '')
    FROM STR,
      (SELECT generate_series(1, array_upper(encoded, 1) + 2) i
       FROM STR) blah

    INTO ret;

    EXCEPTION WHEN OTHERS
    THEN
      RAISE NOTICE 'failed: %', url;
      RETURN $1;
  END;

  RETURN coalesce(ret, $1); -- when the string has no encoding;

END;

$BODY$
LANGUAGE plpgsql
IMMUTABLE
STRICT

分类

SQL