The following bug has been logged on the website:
Bug reference: 12743
Logged by: Jorge barrera
Email address: barrera@mail.bpa.cu
PostgreSQL version: 9.2.10
Operating system: windows 7 32 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;
When I execute this function in Postgres version 9.2 with the table
entrada:
entrada (p3c1 integer, p3c3xml)
containing the 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.