Thread: BUG #12182: error converting xml to table using developed plpgsql function
BUG #12182: error converting xml to table using developed plpgsql function
From
barrera471009@gmail.com
Date:
The following bug has been logged on the website: Bug reference: 12182 Logged by: Jorge barrera ortega Email address: barrera471009@gmail.com PostgreSQL version: 9.2.0 Operating system: windows 7 64 bits Description: I have the following function for converting xml data in a Postgresql table written in plpgsql: CREATE OR REPLACE FUNCTION inicio_actualizacion(dato xml) RETURNS character varying AS $BODY$ DECLARE VAR TEXT; BEGIN DELETE FROM p11; EXECUTE 'CREATE OR REPLACE VIEW tempinicio AS WITH xml_import (xml_data) AS ( SELECT XMLPARSE(DOCUMENT'||quote_literal(dato)||')),otemp1 AS ( SELECT UNNEST(XPATH('||quote_literal('/pl:entrada/pl:row')||', xml_data, ARRAY[array['||quote_literal('pl')||', '||quote_literal('http://xxx.yy.zz/')||']])) AS vector FROM xml_import) SELECT CAST((XPATH('||quote_literal('/row/p3c1/text()')||', vector)::VARCHAR[])[1] AS integer) AS p1c1, XMLPARSE(CONTENT (XPATH('||quote_literal('/row/p3c3/text()')||', vector)::VARCHAR[])[1]) AS p1c2 ' ||'FROM otemp1'; INSERT INTO p11 SELECT * FROM tempinicio; RETURN '00'; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; ALTER FUNCTION inicio_actualizacion(xml) OWNER TO dueno; When I execute this function in Postgres version 9.2 with the table entrada: entrada (p3c1 integer, p3c3xml) containingthe following two rows: 1;"<![CDATA[<a>a123b</a>]]>" 2;"<b>c345d</b>" I get following results for p11: P11(p1c1 integer, p1c2 xml) 1;"<a>a123b</a>" 2;NULL But the correct result should be: 1;â<a>a123b</a>â 2;NULL As can be seen, the characters â<â and â>â are substituted incorrectly for â<â and â>â respectively. Any advise?