BUG #12743: error in xml conversion

From: barrera(at)mail(dot)bpa(dot)cu
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #12743: error in xml conversion
Date: 2015-02-06 20:50:09
Message-ID: 20150206205009.25253.37595@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 12743
Logged by: Jorge barrera
Email address: barrera(at)mail(dot)bpa(dot)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;"&lt;a&gt;a123b&lt;/a&gt;"
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 &lt;
and &gt; respectively.

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2015-02-06 21:57:04 Re: Function parameter type precision modifiers ignored.
Previous Message anjana1909 2015-02-06 16:57:55 BUG #12742: non fatal error during istallation