BUG #12182: error converting xml to table using developed plpgsql function

From: barrera471009(at)gmail(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #12182: error converting xml to table using developed plpgsql function
Date: 2014-12-08 14:41:52
Message-ID: 20141208144152.2528.39567@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: 12182
Logged by: Jorge barrera ortega
Email address: barrera471009(at)gmail(dot)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;"&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.
Any advise?

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2014-12-08 14:54:00 Re: pg_restore crashes passing NULL to strcmp (9.4 rc1)
Previous Message Ed Avis 2014-12-08 11:39:12 Re: BUG #11986: psql uses pager inside Emacs shell buffer (not a terminal)