Skip to content

Instantly share code, notes, and snippets.

@JLarky
Created August 27, 2013 06:22
Show Gist options
  • Save JLarky/6350222 to your computer and use it in GitHub Desktop.
Save JLarky/6350222 to your computer and use it in GitHub Desktop.
escape_json plpgsql function for postgres versions not having json like 9.0 and 9.1 (9.2 and 9.3 have them). Source http://stackoverflow.com/q/4995945/74167
-- based on http://stackoverflow.com/q/4995945/74167
create or replace function escape_json(i_text text) returns text as $$
declare
idx integer;
text_len integer;
cur_char_unicode integer;
rtn_value text := i_text;
begin
text_len = length(rtn_value);
idx = 1;
while (idx <= text_len) loop
cur_char_unicode = ascii(substr(rtn_value, idx, 1));
if cur_char_unicode > 255 then
rtn_value = overlay(rtn_value placing (e'\\u' || lpad(upper(to_hex(cur_char_unicode)), 4, '0')) from idx for 1);
idx = idx + 5;
text_len = text_len + 5;
else
/* is the current character one of the following: " \ / bs ff nl cr tab */
if cur_char_unicode in (34, 92, 47, 8, 12, 10, 13, 9) then
rtn_value = overlay(rtn_value placing (e'\\' || (
case cur_char_unicode
when 34 then '"'
when 92 then e'\\'
when 47 then '/'
when 8 then 'b'
when 12 then 'f'
when 10 then 'n'
when 13 then 'r'
when 9 then 't'
end)) from idx for 1);
idx = idx + 1;
text_len = text_len + 1;
end if;
end if;
idx = idx + 1;
end loop;
return rtn_value;
end;
$$ language plpgsql;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment