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;"<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?
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) |