在PostgreSQL 9.0中优化转义JSON [英] Optimize escape JSON in PostgreSQL 9.0

问题描述

我目前正在PostgreSQL中使用此JSON转义函数作为将来对本机JSON支持的代表.虽然有效，但它也限制了我们的系统性能.如何进行优化?也许是某种查找数组?

I'm currently using this JSON escaping function in PostgreSQL as a stand in for future native JSON support. While it works, it's also limiting our systems performance. How can I go about optimizing it? Maybe some kind of lookup array?

CREATE OR REPLACE FUNCTION escape_json(i_text TEXT) 
RETURNS TEXT AS
$body$                                                  
DECLARE
  idx INTEGER;
  text_len INTEGER;   
  cur_char_unicode INTEGER;
  rtn_value TEXT := i_text;
BEGIN
  -- $Rev: $ --
  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;
$body$
LANGUAGE plpgsql;

推荐答案

我所有的方法都归结为以其他方式做":

All my approaches boil down to "do it some other way":

• 用其他某种语言(例如使用pl/perl，pl/python，pl/ruby​​
• 为一些用C语言编写的外部JSON库编写包装器
• 在客户端而不是在查询中进行JSON转义(假设您的客户端具有良好的JSON转义支持)

以我的经验，pl/pgsql在这种情况下并不快-它的优势在于它对与数据库交换数据的整体支持，而不是作为通用编程语言.

In my experience pl/pgsql isn't fast at this sort of thing- its strength is in its integral support for exchanging data with the database, not as a general-purpose programming language.

示例:

create or replace function escape_json_perl(text) returns text 
  strict immutable
  language plperlu as $$
    use JSON;
    return JSON->new->allow_nonref->encode($_[0]);
  $$;

一项快速测试表明，它的速度比plpgsql函数快15倍(尽管它会返回您可能要剥离的值附近的引号)

A quick test suggests this is on the order of 15x faster than the plpgsql function (although it returns quotes around the value which you probably want to strip off)

这篇关于在PostgreSQL 9.0中优化转义JSON的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！