How to escape ">" character in postgres version 9.2

Question

How do i make the > sign display as is in PG version 9.2 ? I do not want it to show as >.

Query in postgreSQL

SELECT 
XPATH ('/BehaviorReportingRanges/BehaviorReportingRange/Range/text()', 
xmlparse(content 
'<BehaviorReportingRanges> 
  <BehaviorReportingRange> 
   <Range>> 3</Range> 
  </BehaviorReportingRange> 
</BehaviorReportingRanges>')) 

Result in version 9.1:

{"> 3"} -- desired result 

result in version 9.2.3:

"{"> 3"}" -- not desired result 

any pointers please ?

Thanks a lot !

Answer 1

Well, if you CREATE LANGUAGE plpythonu; , you can then do the following:

create or replace function unescape(x varchar)
    returns varchar language plpythonu as $$
from xml.sax.saxutils import unescape
return unescape(x)
$$;

Then, your query can become:

SELECT unescape( concat( (
XPATH ('/BehaviorReportingRanges/BehaviorReportingRange/Range/text()', 
xmlparse(content 
'<BehaviorReportingRanges> 
  <BehaviorReportingRange> 
   <Range>> 3</Range> 
  </BehaviorReportingRange> 
</BehaviorReportingRanges>')) )[1]
 ) );

You need to add the indexing to select the element from the array result of xpath, and the concat is to convert from xml to string datatype.

You can also apply the unescape function directly -- but the resulting string is the formatted xml array.