BUG #12182: error converting xml to table using developed plpgsql function - Mailing list pgsql-bugs

From barrera471009@gmail.com
Subject BUG #12182: error converting xml to table using developed plpgsql function
Date
Msg-id 20141208144152.2528.39567@wrigleys.postgresql.org
Whole thread Raw
List pgsql-bugs
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?

pgsql-bugs by date:

Previous
From: Pierre Slania
Date:
Subject: Out of memory: postgres killed after changing "checkoint_completion_target" and reloading postgres configuration file
Next
From: Mark Kirkwood
Date:
Subject: Re: regression, deadlock in high frequency single-row UPDATE