From 6eb9c5a5657d1fe77b55cc261450f3538d35a94d Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sat, 4 May 2024 14:19:15 +0200 Subject: Adding upstream version 13.4. Signed-off-by: Daniel Baumann --- src/test/regress/expected/xml.out | 1567 +++++++++++++++++++++++++++++++++++++ 1 file changed, 1567 insertions(+) create mode 100644 src/test/regress/expected/xml.out (limited to 'src/test/regress/expected/xml.out') diff --git a/src/test/regress/expected/xml.out b/src/test/regress/expected/xml.out new file mode 100644 index 0000000..55b65ef --- /dev/null +++ b/src/test/regress/expected/xml.out @@ -0,0 +1,1567 @@ +CREATE TABLE xmltest ( + id int, + data xml +); +INSERT INTO xmltest VALUES (1, 'one'); +INSERT INTO xmltest VALUES (2, 'two'); +INSERT INTO xmltest VALUES (3, 'one + 2 | two +(2 rows) + +SELECT xmlcomment('test'); + xmlcomment +------------- + +(1 row) + +SELECT xmlcomment('-test'); + xmlcomment +-------------- + +(1 row) + +SELECT xmlcomment('test-'); +ERROR: invalid XML comment +SELECT xmlcomment('--test'); +ERROR: invalid XML comment +SELECT xmlcomment('te st'); + xmlcomment +-------------- + +(1 row) + +SELECT xmlconcat(xmlcomment('hello'), + xmlelement(NAME qux, 'foo'), + xmlcomment('world')); + xmlconcat +---------------------------------------- + foo +(1 row) + +SELECT xmlconcat('hello', 'you'); + xmlconcat +----------- + helloyou +(1 row) + +SELECT xmlconcat(1, 2); +ERROR: argument of XMLCONCAT must be type xml, not type integer +LINE 1: SELECT xmlconcat(1, 2); + ^ +SELECT xmlconcat('bad', '', NULL, ''); + xmlconcat +-------------- + +(1 row) + +SELECT xmlconcat('', NULL, ''); + xmlconcat +----------------------------------- + +(1 row) + +SELECT xmlconcat(NULL); + xmlconcat +----------- + +(1 row) + +SELECT xmlconcat(NULL, NULL); + xmlconcat +----------- + +(1 row) + +SELECT xmlelement(name element, + xmlattributes (1 as one, 'deuce' as two), + 'content'); + xmlelement +------------------------------------------------ + content +(1 row) + +SELECT xmlelement(name element, + xmlattributes ('unnamed and wrong')); +ERROR: unnamed XML attribute value must be a column reference +LINE 2: xmlattributes ('unnamed and wrong')); + ^ +SELECT xmlelement(name element, xmlelement(name nested, 'stuff')); + xmlelement +------------------------------------------- + stuff +(1 row) + +SELECT xmlelement(name employee, xmlforest(name, age, salary as pay)) FROM emp; + xmlelement +---------------------------------------------------------------------- + sharon251000 + sam302000 + bill201000 + jeff23600 + cim30400 + linda19100 +(6 rows) + +SELECT xmlelement(name duplicate, xmlattributes(1 as a, 2 as b, 3 as a)); +ERROR: XML attribute name "a" appears more than once +LINE 1: ...ment(name duplicate, xmlattributes(1 as a, 2 as b, 3 as a)); + ^ +SELECT xmlelement(name num, 37); + xmlelement +--------------- + 37 +(1 row) + +SELECT xmlelement(name foo, text 'bar'); + xmlelement +---------------- + bar +(1 row) + +SELECT xmlelement(name foo, xml 'bar'); + xmlelement +---------------- + bar +(1 row) + +SELECT xmlelement(name foo, text 'br'); + xmlelement +------------------------- + b<a/>r +(1 row) + +SELECT xmlelement(name foo, xml 'br'); + xmlelement +------------------- + br +(1 row) + +SELECT xmlelement(name foo, array[1, 2, 3]); + xmlelement +------------------------------------------------------------------------- + 123 +(1 row) + +SET xmlbinary TO base64; +SELECT xmlelement(name foo, bytea 'bar'); + xmlelement +----------------- + YmFy +(1 row) + +SET xmlbinary TO hex; +SELECT xmlelement(name foo, bytea 'bar'); + xmlelement +------------------- + 626172 +(1 row) + +SELECT xmlelement(name foo, xmlattributes(true as bar)); + xmlelement +------------------- + +(1 row) + +SELECT xmlelement(name foo, xmlattributes('2009-04-09 00:24:37'::timestamp as bar)); + xmlelement +---------------------------------- + +(1 row) + +SELECT xmlelement(name foo, xmlattributes('infinity'::timestamp as bar)); +ERROR: timestamp out of range +DETAIL: XML does not support infinite timestamp values. +SELECT xmlelement(name foo, xmlattributes('<>&"''' as funny, xml 'br' as funnier)); + xmlelement +------------------------------------------------------------ + +(1 row) + +SELECT xmlparse(content ''); + xmlparse +---------- + +(1 row) + +SELECT xmlparse(content ' '); + xmlparse +---------- + +(1 row) + +SELECT xmlparse(content 'abc'); + xmlparse +---------- + abc +(1 row) + +SELECT xmlparse(content 'x'); + xmlparse +-------------- + x +(1 row) + +SELECT xmlparse(content '&'); +ERROR: invalid XML content +DETAIL: line 1: xmlParseEntityRef: no name +& + ^ +line 1: chunk is not well balanced +& + ^ +SELECT xmlparse(content '&idontexist;'); +ERROR: invalid XML content +DETAIL: line 1: Entity 'idontexist' not defined +&idontexist; + ^ +line 1: chunk is not well balanced +&idontexist; + ^ +SELECT xmlparse(content ''); + xmlparse +--------------------------- + +(1 row) + +SELECT xmlparse(content ''); + xmlparse +-------------------------------- + +(1 row) + +SELECT xmlparse(content '&idontexist;'); +ERROR: invalid XML content +DETAIL: line 1: Entity 'idontexist' not defined +&idontexist; + ^ +line 1: Opening and ending tag mismatch: twoerrors line 1 and unbalanced +&idontexist; + ^ +line 1: chunk is not well balanced +&idontexist; + ^ +SELECT xmlparse(content ''); + xmlparse +--------------------- + +(1 row) + +SELECT xmlparse(document ' '); +ERROR: invalid XML document +DETAIL: line 1: Start tag expected, '<' not found + + ^ +SELECT xmlparse(document 'abc'); +ERROR: invalid XML document +DETAIL: line 1: Start tag expected, '<' not found +abc +^ +SELECT xmlparse(document 'x'); + xmlparse +-------------- + x +(1 row) + +SELECT xmlparse(document '&'); +ERROR: invalid XML document +DETAIL: line 1: xmlParseEntityRef: no name +& + ^ +line 1: Opening and ending tag mismatch: invalidentity line 1 and abc +& + ^ +SELECT xmlparse(document '&idontexist;'); +ERROR: invalid XML document +DETAIL: line 1: Entity 'idontexist' not defined +&idontexist; + ^ +line 1: Opening and ending tag mismatch: undefinedentity line 1 and abc +&idontexist; + ^ +SELECT xmlparse(document ''); + xmlparse +--------------------------- + +(1 row) + +SELECT xmlparse(document ''); + xmlparse +-------------------------------- + +(1 row) + +SELECT xmlparse(document '&idontexist;'); +ERROR: invalid XML document +DETAIL: line 1: Entity 'idontexist' not defined +&idontexist; + ^ +line 1: Opening and ending tag mismatch: twoerrors line 1 and unbalanced +&idontexist; + ^ +SELECT xmlparse(document ''); + xmlparse +--------------------- + +(1 row) + +SELECT xmlpi(name foo); + xmlpi +--------- + +(1 row) + +SELECT xmlpi(name xml); +ERROR: invalid XML processing instruction +DETAIL: XML processing instruction target name cannot be "xml". +SELECT xmlpi(name xmlstuff); + xmlpi +-------------- + +(1 row) + +SELECT xmlpi(name foo, 'bar'); + xmlpi +------------- + +(1 row) + +SELECT xmlpi(name foo, 'in?>valid'); +ERROR: invalid XML processing instruction +DETAIL: XML processing instruction cannot contain "?>". +SELECT xmlpi(name foo, null); + xmlpi +------- + +(1 row) + +SELECT xmlpi(name xml, null); +ERROR: invalid XML processing instruction +DETAIL: XML processing instruction target name cannot be "xml". +SELECT xmlpi(name xmlstuff, null); + xmlpi +------- + +(1 row) + +SELECT xmlpi(name "xml-stylesheet", 'href="mystyle.css" type="text/css"'); + xmlpi +------------------------------------------------------- + +(1 row) + +SELECT xmlpi(name foo, ' bar'); + xmlpi +------------- + +(1 row) + +SELECT xmlroot(xml '', version no value, standalone no value); + xmlroot +--------- + +(1 row) + +SELECT xmlroot(xml '', version '2.0'); + xmlroot +----------------------------- + +(1 row) + +SELECT xmlroot(xml '', version no value, standalone yes); + xmlroot +---------------------------------------------- + +(1 row) + +SELECT xmlroot(xml '', version no value, standalone yes); + xmlroot +---------------------------------------------- + +(1 row) + +SELECT xmlroot(xmlroot(xml '', version '1.0'), version '1.1', standalone no); + xmlroot +--------------------------------------------- + +(1 row) + +SELECT xmlroot('', version no value, standalone no); + xmlroot +--------------------------------------------- + +(1 row) + +SELECT xmlroot('', version no value, standalone no value); + xmlroot +--------- + +(1 row) + +SELECT xmlroot('', version no value); + xmlroot +---------------------------------------------- + +(1 row) + +SELECT xmlroot ( + xmlelement ( + name gazonk, + xmlattributes ( + 'val' AS name, + 1 + 1 AS num + ), + xmlelement ( + NAME qux, + 'foo' + ) + ), + version '1.0', + standalone yes +); + xmlroot +------------------------------------------------------------------------------------------ + foo +(1 row) + +SELECT xmlserialize(content data as character varying(20)) FROM xmltest; + xmlserialize +-------------------- + one + two +(2 rows) + +SELECT xmlserialize(content 'good' as char(10)); + xmlserialize +-------------- + good +(1 row) + +SELECT xmlserialize(document 'bad' as text); +ERROR: not an XML document +SELECT xml 'bar' IS DOCUMENT; + ?column? +---------- + t +(1 row) + +SELECT xml 'barfoo' IS DOCUMENT; + ?column? +---------- + f +(1 row) + +SELECT xml '' IS NOT DOCUMENT; + ?column? +---------- + f +(1 row) + +SELECT xml 'abc' IS NOT DOCUMENT; + ?column? +---------- + t +(1 row) + +SELECT '<>' IS NOT DOCUMENT; +ERROR: invalid XML content +LINE 1: SELECT '<>' IS NOT DOCUMENT; + ^ +DETAIL: line 1: StartTag: invalid element name +<> + ^ +SELECT xmlagg(data) FROM xmltest; + xmlagg +-------------------------------------- + onetwo +(1 row) + +SELECT xmlagg(data) FROM xmltest WHERE id > 10; + xmlagg +-------- + +(1 row) + +SELECT xmlelement(name employees, xmlagg(xmlelement(name name, name))) FROM emp; + xmlelement +-------------------------------------------------------------------------------------------------------------------------------- + sharonsambilljeffcimlinda +(1 row) + +-- Check mapping SQL identifier to XML name +SELECT xmlpi(name ":::_xml_abc135.%-&_"); + xmlpi +------------------------------------------------- + +(1 row) + +SELECT xmlpi(name "123"); + xmlpi +--------------- + +(1 row) + +PREPARE foo (xml) AS SELECT xmlconcat('', $1); +SET XML OPTION DOCUMENT; +EXECUTE foo (''); + xmlconcat +-------------- + +(1 row) + +EXECUTE foo ('bad'); +ERROR: invalid XML document +LINE 1: EXECUTE foo ('bad'); + ^ +DETAIL: line 1: Start tag expected, '<' not found +bad +^ +SELECT xml ''; +ERROR: invalid XML document +LINE 1: SELECT xml ''; + ^ +DETAIL: line 1: Extra content at the end of the document + + ^ +SET XML OPTION CONTENT; +EXECUTE foo (''); + xmlconcat +-------------- + +(1 row) + +EXECUTE foo ('good'); + xmlconcat +------------ + good +(1 row) + +SELECT xml ' '; + xml +-------------------------------------------------------------------- + +(1 row) + +SELECT xml ' '; + xml +------------------------------ + +(1 row) + +SELECT xml ''; + xml +------------------ + +(1 row) + +SELECT xml ' oops '; +ERROR: invalid XML content +LINE 1: SELECT xml ' oops '; + ^ +DETAIL: line 1: StartTag: invalid element name + oops + ^ +SELECT xml ' '; +ERROR: invalid XML content +LINE 1: SELECT xml ' '; + ^ +DETAIL: line 1: StartTag: invalid element name + + ^ +SELECT xml ''; +ERROR: invalid XML content +LINE 1: SELECT xml ''; + ^ +DETAIL: line 1: Extra content at the end of the document + + ^ +-- Test backwards parsing +CREATE VIEW xmlview1 AS SELECT xmlcomment('test'); +CREATE VIEW xmlview2 AS SELECT xmlconcat('hello', 'you'); +CREATE VIEW xmlview3 AS SELECT xmlelement(name element, xmlattributes (1 as ":one:", 'deuce' as two), 'content&'); +CREATE VIEW xmlview4 AS SELECT xmlelement(name employee, xmlforest(name, age, salary as pay)) FROM emp; +CREATE VIEW xmlview5 AS SELECT xmlparse(content 'x'); +CREATE VIEW xmlview6 AS SELECT xmlpi(name foo, 'bar'); +CREATE VIEW xmlview7 AS SELECT xmlroot(xml '', version no value, standalone yes); +CREATE VIEW xmlview8 AS SELECT xmlserialize(content 'good' as char(10)); +CREATE VIEW xmlview9 AS SELECT xmlserialize(content 'good' as text); +SELECT table_name, view_definition FROM information_schema.views + WHERE table_name LIKE 'xmlview%' ORDER BY 1; + table_name | view_definition +------------+------------------------------------------------------------------------------------------------------------------- + xmlview1 | SELECT xmlcomment('test'::text) AS xmlcomment; + xmlview2 | SELECT XMLCONCAT('hello'::xml, 'you'::xml) AS "xmlconcat"; + xmlview3 | SELECT XMLELEMENT(NAME element, XMLATTRIBUTES(1 AS ":one:", 'deuce' AS two), 'content&') AS "xmlelement"; + xmlview4 | SELECT XMLELEMENT(NAME employee, XMLFOREST(emp.name AS name, emp.age AS age, emp.salary AS pay)) AS "xmlelement"+ + | FROM emp; + xmlview5 | SELECT XMLPARSE(CONTENT 'x'::text STRIP WHITESPACE) AS "xmlparse"; + xmlview6 | SELECT XMLPI(NAME foo, 'bar'::text) AS "xmlpi"; + xmlview7 | SELECT XMLROOT(''::xml, VERSION NO VALUE, STANDALONE YES) AS "xmlroot"; + xmlview8 | SELECT (XMLSERIALIZE(CONTENT 'good'::xml AS character(10)))::character(10) AS "xmlserialize"; + xmlview9 | SELECT XMLSERIALIZE(CONTENT 'good'::xml AS text) AS "xmlserialize"; +(9 rows) + +-- Text XPath expressions evaluation +SELECT xpath('/value', data) FROM xmltest; + xpath +---------------------- + {one} + {two} +(2 rows) + +SELECT xpath(NULL, NULL) IS NULL FROM xmltest; + ?column? +---------- + t + t +(2 rows) + +SELECT xpath('', ''); +ERROR: empty XPath expression +CONTEXT: SQL function "xpath" statement 1 +SELECT xpath('//text()', 'number one'); + xpath +---------------- + {"number one"} +(1 row) + +SELECT xpath('//loc:piece/@id', 'number one', ARRAY[ARRAY['loc', 'http://127.0.0.1']]); + xpath +------- + {1,2} +(1 row) + +SELECT xpath('//loc:piece', 'number one', ARRAY[ARRAY['loc', 'http://127.0.0.1']]); + xpath +------------------------------------------------------------------------------------------------------------------------------------------------ + {"number one",""} +(1 row) + +SELECT xpath('//loc:piece', 'number one', ARRAY[ARRAY['loc', 'http://127.0.0.1']]); + xpath +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ + {"number one",""} +(1 row) + +SELECT xpath('//b', 'one two three etc'); + xpath +------------------------- + {two,etc} +(1 row) + +SELECT xpath('//text()', '<'); + xpath +-------- + {<} +(1 row) + +SELECT xpath('//@value', ''); + xpath +-------- + {<} +(1 row) + +SELECT xpath('''<>''', ''); + xpath +--------------------------- + {<<invalid>>} +(1 row) + +SELECT xpath('count(//*)', ''); + xpath +------- + {3} +(1 row) + +SELECT xpath('count(//*)=0', ''); + xpath +--------- + {false} +(1 row) + +SELECT xpath('count(//*)=3', ''); + xpath +-------- + {true} +(1 row) + +SELECT xpath('name(/*)', ''); + xpath +-------- + {root} +(1 row) + +SELECT xpath('/nosuchtag', ''); + xpath +------- + {} +(1 row) + +SELECT xpath('root', ''); + xpath +----------- + {} +(1 row) + +-- Round-trip non-ASCII data through xpath(). +DO $$ +DECLARE + xml_declaration text := ''; + degree_symbol text; + res xml[]; +BEGIN + -- Per the documentation, except when the server encoding is UTF8, xpath() + -- may not work on non-ASCII data. The untranslatable_character and + -- undefined_function traps below, currently dead code, will become relevant + -- if we remove this limitation. + IF current_setting('server_encoding') <> 'UTF8' THEN + RAISE LOG 'skip: encoding % unsupported for xpath', + current_setting('server_encoding'); + RETURN; + END IF; + + degree_symbol := convert_from('\xc2b0', 'UTF8'); + res := xpath('text()', (xml_declaration || + '' || degree_symbol || '')::xml); + IF degree_symbol <> res[1]::text THEN + RAISE 'expected % (%), got % (%)', + degree_symbol, convert_to(degree_symbol, 'UTF8'), + res[1], convert_to(res[1]::text, 'UTF8'); + END IF; +EXCEPTION + -- character with byte sequence 0xc2 0xb0 in encoding "UTF8" has no equivalent in encoding "LATIN8" + WHEN untranslatable_character + -- default conversion function for encoding "UTF8" to "MULE_INTERNAL" does not exist + OR undefined_function + -- unsupported XML feature + OR feature_not_supported THEN + RAISE LOG 'skip: %', SQLERRM; +END +$$; +-- Test xmlexists and xpath_exists +SELECT xmlexists('//town[text() = ''Toronto'']' PASSING BY REF 'Bidford-on-AvonCwmbranBristol'); + xmlexists +----------- + f +(1 row) + +SELECT xmlexists('//town[text() = ''Cwmbran'']' PASSING BY REF 'Bidford-on-AvonCwmbranBristol'); + xmlexists +----------- + t +(1 row) + +SELECT xmlexists('count(/nosuchtag)' PASSING BY REF ''); + xmlexists +----------- + t +(1 row) + +SELECT xpath_exists('//town[text() = ''Toronto'']','Bidford-on-AvonCwmbranBristol'::xml); + xpath_exists +-------------- + f +(1 row) + +SELECT xpath_exists('//town[text() = ''Cwmbran'']','Bidford-on-AvonCwmbranBristol'::xml); + xpath_exists +-------------- + t +(1 row) + +SELECT xpath_exists('count(/nosuchtag)', ''::xml); + xpath_exists +-------------- + t +(1 row) + +INSERT INTO xmltest VALUES (4, 'BudvarfreeCarlinglots'::xml); +INSERT INTO xmltest VALUES (5, 'MolsonfreeCarlinglots'::xml); +INSERT INTO xmltest VALUES (6, 'BudvarfreeCarlinglots'::xml); +INSERT INTO xmltest VALUES (7, 'MolsonfreeCarlinglots'::xml); +SELECT COUNT(id) FROM xmltest WHERE xmlexists('/menu/beer' PASSING data); + count +------- + 0 +(1 row) + +SELECT COUNT(id) FROM xmltest WHERE xmlexists('/menu/beer' PASSING BY REF data BY REF); + count +------- + 0 +(1 row) + +SELECT COUNT(id) FROM xmltest WHERE xmlexists('/menu/beers' PASSING BY REF data); + count +------- + 2 +(1 row) + +SELECT COUNT(id) FROM xmltest WHERE xmlexists('/menu/beers/name[text() = ''Molson'']' PASSING BY REF data); + count +------- + 1 +(1 row) + +SELECT COUNT(id) FROM xmltest WHERE xpath_exists('/menu/beer',data); + count +------- + 0 +(1 row) + +SELECT COUNT(id) FROM xmltest WHERE xpath_exists('/menu/beers',data); + count +------- + 2 +(1 row) + +SELECT COUNT(id) FROM xmltest WHERE xpath_exists('/menu/beers/name[text() = ''Molson'']',data); + count +------- + 1 +(1 row) + +SELECT COUNT(id) FROM xmltest WHERE xpath_exists('/myns:menu/myns:beer',data,ARRAY[ARRAY['myns','http://myns.com']]); + count +------- + 0 +(1 row) + +SELECT COUNT(id) FROM xmltest WHERE xpath_exists('/myns:menu/myns:beers',data,ARRAY[ARRAY['myns','http://myns.com']]); + count +------- + 2 +(1 row) + +SELECT COUNT(id) FROM xmltest WHERE xpath_exists('/myns:menu/myns:beers/myns:name[text() = ''Molson'']',data,ARRAY[ARRAY['myns','http://myns.com']]); + count +------- + 1 +(1 row) + +CREATE TABLE query ( expr TEXT ); +INSERT INTO query VALUES ('/menu/beers/cost[text() = ''lots'']'); +SELECT COUNT(id) FROM xmltest, query WHERE xmlexists(expr PASSING BY REF data); + count +------- + 2 +(1 row) + +-- Test xml_is_well_formed and variants +SELECT xml_is_well_formed_document('bar'); + xml_is_well_formed_document +----------------------------- + t +(1 row) + +SELECT xml_is_well_formed_document('abc'); + xml_is_well_formed_document +----------------------------- + f +(1 row) + +SELECT xml_is_well_formed_content('bar'); + xml_is_well_formed_content +---------------------------- + t +(1 row) + +SELECT xml_is_well_formed_content('abc'); + xml_is_well_formed_content +---------------------------- + t +(1 row) + +SET xmloption TO DOCUMENT; +SELECT xml_is_well_formed('abc'); + xml_is_well_formed +-------------------- + f +(1 row) + +SELECT xml_is_well_formed('<>'); + xml_is_well_formed +-------------------- + f +(1 row) + +SELECT xml_is_well_formed(''); + xml_is_well_formed +-------------------- + t +(1 row) + +SELECT xml_is_well_formed('bar'); + xml_is_well_formed +-------------------- + t +(1 row) + +SELECT xml_is_well_formed('barbaz'); + xml_is_well_formed +-------------------- + f +(1 row) + +SELECT xml_is_well_formed('number one'); + xml_is_well_formed +-------------------- + t +(1 row) + +SELECT xml_is_well_formed('bar'); + xml_is_well_formed +-------------------- + f +(1 row) + +SELECT xml_is_well_formed('bar'); + xml_is_well_formed +-------------------- + t +(1 row) + +SELECT xml_is_well_formed('&'); + xml_is_well_formed +-------------------- + f +(1 row) + +SELECT xml_is_well_formed('&idontexist;'); + xml_is_well_formed +-------------------- + f +(1 row) + +SELECT xml_is_well_formed(''); + xml_is_well_formed +-------------------- + t +(1 row) + +SELECT xml_is_well_formed(''); + xml_is_well_formed +-------------------- + t +(1 row) + +SELECT xml_is_well_formed('&idontexist;'); + xml_is_well_formed +-------------------- + f +(1 row) + +SET xmloption TO CONTENT; +SELECT xml_is_well_formed('abc'); + xml_is_well_formed +-------------------- + t +(1 row) + +-- Since xpath() deals with namespaces, it's a bit stricter about +-- what's well-formed and what's not. If we don't obey these rules +-- (i.e. ignore namespace-related errors from libxml), xpath() +-- fails in subtle ways. The following would for example produce +-- the xml value +-- +-- which is invalid because '<' may not appear un-escaped in +-- attribute values. +-- Since different libxml versions emit slightly different +-- error messages, we suppress the DETAIL in this test. +\set VERBOSITY terse +SELECT xpath('/*', ''); +ERROR: could not parse XML document +\set VERBOSITY default +-- Again, the XML isn't well-formed for namespace purposes +SELECT xpath('/*', ''); +ERROR: could not parse XML document +DETAIL: line 1: Namespace prefix nosuchprefix on tag is not defined + + ^ +CONTEXT: SQL function "xpath" statement 1 +-- XPath deprecates relative namespaces, but they're not supposed to +-- throw an error, only a warning. +SELECT xpath('/*', ''); +WARNING: line 1: xmlns: URI relative is not absolute + + ^ + xpath +-------------------------------------- + {""} +(1 row) + +-- External entity references should not leak filesystem information. +SELECT XMLPARSE(DOCUMENT ']>&c;'); + xmlparse +----------------------------------------------------------------- + ]>&c; +(1 row) + +SELECT XMLPARSE(DOCUMENT ']>&c;'); + xmlparse +----------------------------------------------------------------------- + ]>&c; +(1 row) + +-- This might or might not load the requested DTD, but it mustn't throw error. +SELECT XMLPARSE(DOCUMENT ' '); + xmlparse +------------------------------------------------------------------------------------------------------------------------------------------------------ +   +(1 row) + +-- XMLPATH tests +CREATE TABLE xmldata(data xml); +INSERT INTO xmldata VALUES(' + + AU + Australia + 3 + + + CN + China + 3 + + + HK + HongKong + 3 + + + IN + India + 3 + + + JP + Japan + 3Sinzo Abe + + + SG + Singapore + 3791 + +'); +-- XMLTABLE with columns +SELECT xmltable.* + FROM (SELECT data FROM xmldata) x, + LATERAL XMLTABLE('/ROWS/ROW' + PASSING data + COLUMNS id int PATH '@id', + _id FOR ORDINALITY, + country_name text PATH 'COUNTRY_NAME/text()' NOT NULL, + country_id text PATH 'COUNTRY_ID', + region_id int PATH 'REGION_ID', + size float PATH 'SIZE', + unit text PATH 'SIZE/@unit', + premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified'); + id | _id | country_name | country_id | region_id | size | unit | premier_name +----+-----+--------------+------------+-----------+------+------+--------------- + 1 | 1 | Australia | AU | 3 | | | not specified + 2 | 2 | China | CN | 3 | | | not specified + 3 | 3 | HongKong | HK | 3 | | | not specified + 4 | 4 | India | IN | 3 | | | not specified + 5 | 5 | Japan | JP | 3 | | | Sinzo Abe + 6 | 6 | Singapore | SG | 3 | 791 | km | not specified +(6 rows) + +CREATE VIEW xmltableview1 AS SELECT xmltable.* + FROM (SELECT data FROM xmldata) x, + LATERAL XMLTABLE('/ROWS/ROW' + PASSING data + COLUMNS id int PATH '@id', + _id FOR ORDINALITY, + country_name text PATH 'COUNTRY_NAME/text()' NOT NULL, + country_id text PATH 'COUNTRY_ID', + region_id int PATH 'REGION_ID', + size float PATH 'SIZE', + unit text PATH 'SIZE/@unit', + premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified'); +SELECT * FROM xmltableview1; + id | _id | country_name | country_id | region_id | size | unit | premier_name +----+-----+--------------+------------+-----------+------+------+--------------- + 1 | 1 | Australia | AU | 3 | | | not specified + 2 | 2 | China | CN | 3 | | | not specified + 3 | 3 | HongKong | HK | 3 | | | not specified + 4 | 4 | India | IN | 3 | | | not specified + 5 | 5 | Japan | JP | 3 | | | Sinzo Abe + 6 | 6 | Singapore | SG | 3 | 791 | km | not specified +(6 rows) + +\sv xmltableview1 +CREATE OR REPLACE VIEW public.xmltableview1 AS + SELECT "xmltable".id, + "xmltable"._id, + "xmltable".country_name, + "xmltable".country_id, + "xmltable".region_id, + "xmltable".size, + "xmltable".unit, + "xmltable".premier_name + FROM ( SELECT xmldata.data + FROM xmldata) x, + LATERAL XMLTABLE(('/ROWS/ROW'::text) PASSING (x.data) COLUMNS id integer PATH ('@id'::text), _id FOR ORDINALITY, country_name text PATH ('COUNTRY_NAME/text()'::text) NOT NULL, country_id text PATH ('COUNTRY_ID'::text), region_id integer PATH ('REGION_ID'::text), size double precision PATH ('SIZE'::text), unit text PATH ('SIZE/@unit'::text), premier_name text DEFAULT ('not specified'::text) PATH ('PREMIER_NAME'::text)) +EXPLAIN (COSTS OFF) SELECT * FROM xmltableview1; + QUERY PLAN +----------------------------------------- + Nested Loop + -> Seq Scan on xmldata + -> Table Function Scan on "xmltable" +(3 rows) + +EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM xmltableview1; + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ + Nested Loop + Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name + -> Seq Scan on public.xmldata + Output: xmldata.data + -> Table Function Scan on "xmltable" + Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name + Table Function Call: XMLTABLE(('/ROWS/ROW'::text) PASSING (xmldata.data) COLUMNS id integer PATH ('@id'::text), _id FOR ORDINALITY, country_name text PATH ('COUNTRY_NAME/text()'::text) NOT NULL, country_id text PATH ('COUNTRY_ID'::text), region_id integer PATH ('REGION_ID'::text), size double precision PATH ('SIZE'::text), unit text PATH ('SIZE/@unit'::text), premier_name text DEFAULT ('not specified'::text) PATH ('PREMIER_NAME'::text)) +(7 rows) + +-- XMLNAMESPACES tests +SELECT * FROM XMLTABLE(XMLNAMESPACES('http://x.y' AS zz), + '/zz:rows/zz:row' + PASSING '10' + COLUMNS a int PATH 'zz:a'); + a +---- + 10 +(1 row) + +CREATE VIEW xmltableview2 AS SELECT * FROM XMLTABLE(XMLNAMESPACES('http://x.y' AS zz), + '/zz:rows/zz:row' + PASSING '10' + COLUMNS a int PATH 'zz:a'); +SELECT * FROM xmltableview2; + a +---- + 10 +(1 row) + +SELECT * FROM XMLTABLE(XMLNAMESPACES(DEFAULT 'http://x.y'), + '/rows/row' + PASSING '10' + COLUMNS a int PATH 'a'); +ERROR: DEFAULT namespace is not supported +SELECT * FROM XMLTABLE('.' + PASSING '' + COLUMNS a text PATH 'foo/namespace::node()'); + a +-------------------------------------- + http://www.w3.org/XML/1998/namespace +(1 row) + +-- used in prepare statements +PREPARE pp AS +SELECT xmltable.* + FROM (SELECT data FROM xmldata) x, + LATERAL XMLTABLE('/ROWS/ROW' + PASSING data + COLUMNS id int PATH '@id', + _id FOR ORDINALITY, + country_name text PATH 'COUNTRY_NAME' NOT NULL, + country_id text PATH 'COUNTRY_ID', + region_id int PATH 'REGION_ID', + size float PATH 'SIZE', + unit text PATH 'SIZE/@unit', + premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified'); +EXECUTE pp; + id | _id | country_name | country_id | region_id | size | unit | premier_name +----+-----+--------------+------------+-----------+------+------+--------------- + 1 | 1 | Australia | AU | 3 | | | not specified + 2 | 2 | China | CN | 3 | | | not specified + 3 | 3 | HongKong | HK | 3 | | | not specified + 4 | 4 | India | IN | 3 | | | not specified + 5 | 5 | Japan | JP | 3 | | | Sinzo Abe + 6 | 6 | Singapore | SG | 3 | 791 | km | not specified +(6 rows) + +SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS "COUNTRY_NAME" text, "REGION_ID" int); + COUNTRY_NAME | REGION_ID +--------------+----------- + India | 3 + Japan | 3 +(2 rows) + +SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS id FOR ORDINALITY, "COUNTRY_NAME" text, "REGION_ID" int); + id | COUNTRY_NAME | REGION_ID +----+--------------+----------- + 1 | India | 3 + 2 | Japan | 3 +(2 rows) + +SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS id int PATH '@id', "COUNTRY_NAME" text, "REGION_ID" int); + id | COUNTRY_NAME | REGION_ID +----+--------------+----------- + 4 | India | 3 + 5 | Japan | 3 +(2 rows) + +SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS id int PATH '@id'); + id +---- + 4 + 5 +(2 rows) + +SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS id FOR ORDINALITY); + id +---- + 1 + 2 +(2 rows) + +SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS id int PATH '@id', "COUNTRY_NAME" text, "REGION_ID" int, rawdata xml PATH '.'); + id | COUNTRY_NAME | REGION_ID | rawdata +----+--------------+-----------+------------------------------------------------------------------ + 4 | India | 3 | + + | | | IN + + | | | India + + | | | 3 + + | | | + 5 | Japan | 3 | + + | | | JP + + | | | Japan + + | | | 3Sinzo Abe+ + | | | +(2 rows) + +SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS id int PATH '@id', "COUNTRY_NAME" text, "REGION_ID" int, rawdata xml PATH './*'); + id | COUNTRY_NAME | REGION_ID | rawdata +----+--------------+-----------+----------------------------------------------------------------------------------------------------------------------------- + 4 | India | 3 | INIndia3 + 5 | Japan | 3 | JPJapan3Sinzo Abe +(2 rows) + +SELECT * FROM xmltable('/root' passing 'a1aa2a bbbbxxxcccc' COLUMNS element text); + element +---------------------- + a1aa2a bbbbxxxcccc +(1 row) + +SELECT * FROM xmltable('/root' passing 'a1aa2a bbbbxxxcccc' COLUMNS element text PATH 'element/text()'); -- should fail +ERROR: more than one value returned by column XPath expression +-- CDATA test +select * from xmltable('d/r' passing ' &"<>!foo]]>2' columns c text); + c +------------------------- + &"<>!foo + 2 +(2 rows) + +-- XML builtin entities +SELECT * FROM xmltable('/x/a' PASSING ''"&<>' COLUMNS ent text); + ent +----- + ' + " + & + < + > +(5 rows) + +SELECT * FROM xmltable('/x/a' PASSING ''"&<>' COLUMNS ent xml); + ent +------------------ + ' + " + & + < + > +(5 rows) + +EXPLAIN (VERBOSE, COSTS OFF) +SELECT xmltable.* + FROM (SELECT data FROM xmldata) x, + LATERAL XMLTABLE('/ROWS/ROW' + PASSING data + COLUMNS id int PATH '@id', + _id FOR ORDINALITY, + country_name text PATH 'COUNTRY_NAME' NOT NULL, + country_id text PATH 'COUNTRY_ID', + region_id int PATH 'REGION_ID', + size float PATH 'SIZE', + unit text PATH 'SIZE/@unit', + premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified'); + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + Nested Loop + Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name + -> Seq Scan on public.xmldata + Output: xmldata.data + -> Table Function Scan on "xmltable" + Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name + Table Function Call: XMLTABLE(('/ROWS/ROW'::text) PASSING (xmldata.data) COLUMNS id integer PATH ('@id'::text), _id FOR ORDINALITY, country_name text PATH ('COUNTRY_NAME'::text) NOT NULL, country_id text PATH ('COUNTRY_ID'::text), region_id integer PATH ('REGION_ID'::text), size double precision PATH ('SIZE'::text), unit text PATH ('SIZE/@unit'::text), premier_name text DEFAULT ('not specified'::text) PATH ('PREMIER_NAME'::text)) +(7 rows) + +-- test qual +SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS "COUNTRY_NAME" text, "REGION_ID" int) WHERE "COUNTRY_NAME" = 'Japan'; + COUNTRY_NAME | REGION_ID +--------------+----------- + Japan | 3 +(1 row) + +EXPLAIN (VERBOSE, COSTS OFF) +SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS "COUNTRY_NAME" text, "REGION_ID" int) WHERE "COUNTRY_NAME" = 'Japan'; + QUERY PLAN +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + Nested Loop + Output: "xmltable"."COUNTRY_NAME", "xmltable"."REGION_ID" + -> Seq Scan on public.xmldata + Output: xmldata.data + -> Table Function Scan on "xmltable" + Output: "xmltable"."COUNTRY_NAME", "xmltable"."REGION_ID" + Table Function Call: XMLTABLE(('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]'::text) PASSING (xmldata.data) COLUMNS "COUNTRY_NAME" text, "REGION_ID" integer) + Filter: ("xmltable"."COUNTRY_NAME" = 'Japan'::text) +(8 rows) + +-- should to work with more data +INSERT INTO xmldata VALUES(' + + CZ + Czech Republic + 2Milos Zeman + + + DE + Germany + 2 + + + FR + France + 2 + +'); +INSERT INTO xmldata VALUES(' + + EG + Egypt + 1 + + + SD + Sudan + 1 + +'); +SELECT xmltable.* + FROM (SELECT data FROM xmldata) x, + LATERAL XMLTABLE('/ROWS/ROW' + PASSING data + COLUMNS id int PATH '@id', + _id FOR ORDINALITY, + country_name text PATH 'COUNTRY_NAME' NOT NULL, + country_id text PATH 'COUNTRY_ID', + region_id int PATH 'REGION_ID', + size float PATH 'SIZE', + unit text PATH 'SIZE/@unit', + premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified'); + id | _id | country_name | country_id | region_id | size | unit | premier_name +----+-----+----------------+------------+-----------+------+------+--------------- + 1 | 1 | Australia | AU | 3 | | | not specified + 2 | 2 | China | CN | 3 | | | not specified + 3 | 3 | HongKong | HK | 3 | | | not specified + 4 | 4 | India | IN | 3 | | | not specified + 5 | 5 | Japan | JP | 3 | | | Sinzo Abe + 6 | 6 | Singapore | SG | 3 | 791 | km | not specified + 10 | 1 | Czech Republic | CZ | 2 | | | Milos Zeman + 11 | 2 | Germany | DE | 2 | | | not specified + 12 | 3 | France | FR | 2 | | | not specified + 20 | 1 | Egypt | EG | 1 | | | not specified + 21 | 2 | Sudan | SD | 1 | | | not specified +(11 rows) + +SELECT xmltable.* + FROM (SELECT data FROM xmldata) x, + LATERAL XMLTABLE('/ROWS/ROW' + PASSING data + COLUMNS id int PATH '@id', + _id FOR ORDINALITY, + country_name text PATH 'COUNTRY_NAME' NOT NULL, + country_id text PATH 'COUNTRY_ID', + region_id int PATH 'REGION_ID', + size float PATH 'SIZE', + unit text PATH 'SIZE/@unit', + premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified') + WHERE region_id = 2; + id | _id | country_name | country_id | region_id | size | unit | premier_name +----+-----+----------------+------------+-----------+------+------+--------------- + 10 | 1 | Czech Republic | CZ | 2 | | | Milos Zeman + 11 | 2 | Germany | DE | 2 | | | not specified + 12 | 3 | France | FR | 2 | | | not specified +(3 rows) + +EXPLAIN (VERBOSE, COSTS OFF) +SELECT xmltable.* + FROM (SELECT data FROM xmldata) x, + LATERAL XMLTABLE('/ROWS/ROW' + PASSING data + COLUMNS id int PATH '@id', + _id FOR ORDINALITY, + country_name text PATH 'COUNTRY_NAME' NOT NULL, + country_id text PATH 'COUNTRY_ID', + region_id int PATH 'REGION_ID', + size float PATH 'SIZE', + unit text PATH 'SIZE/@unit', + premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified') + WHERE region_id = 2; + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + Nested Loop + Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name + -> Seq Scan on public.xmldata + Output: xmldata.data + -> Table Function Scan on "xmltable" + Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name + Table Function Call: XMLTABLE(('/ROWS/ROW'::text) PASSING (xmldata.data) COLUMNS id integer PATH ('@id'::text), _id FOR ORDINALITY, country_name text PATH ('COUNTRY_NAME'::text) NOT NULL, country_id text PATH ('COUNTRY_ID'::text), region_id integer PATH ('REGION_ID'::text), size double precision PATH ('SIZE'::text), unit text PATH ('SIZE/@unit'::text), premier_name text DEFAULT ('not specified'::text) PATH ('PREMIER_NAME'::text)) + Filter: ("xmltable".region_id = 2) +(8 rows) + +-- should fail, NULL value +SELECT xmltable.* + FROM (SELECT data FROM xmldata) x, + LATERAL XMLTABLE('/ROWS/ROW' + PASSING data + COLUMNS id int PATH '@id', + _id FOR ORDINALITY, + country_name text PATH 'COUNTRY_NAME' NOT NULL, + country_id text PATH 'COUNTRY_ID', + region_id int PATH 'REGION_ID', + size float PATH 'SIZE' NOT NULL, + unit text PATH 'SIZE/@unit', + premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified'); +ERROR: null is not allowed in column "size" +-- if all is ok, then result is empty +-- one line xml test +WITH + x AS (SELECT proname, proowner, procost::numeric, pronargs, + array_to_string(proargnames,',') as proargnames, + case when proargtypes <> '' then array_to_string(proargtypes::oid[],',') end as proargtypes + FROM pg_proc WHERE proname = 'f_leak'), + y AS (SELECT xmlelement(name proc, + xmlforest(proname, proowner, + procost, pronargs, + proargnames, proargtypes)) as proc + FROM x), + z AS (SELECT xmltable.* + FROM y, + LATERAL xmltable('/proc' PASSING proc + COLUMNS proname name, + proowner oid, + procost float, + pronargs int, + proargnames text, + proargtypes text)) + SELECT * FROM z + EXCEPT SELECT * FROM x; + proname | proowner | procost | pronargs | proargnames | proargtypes +---------+----------+---------+----------+-------------+------------- +(0 rows) + +-- multi line xml test, result should be empty too +WITH + x AS (SELECT proname, proowner, procost::numeric, pronargs, + array_to_string(proargnames,',') as proargnames, + case when proargtypes <> '' then array_to_string(proargtypes::oid[],',') end as proargtypes + FROM pg_proc), + y AS (SELECT xmlelement(name data, + xmlagg(xmlelement(name proc, + xmlforest(proname, proowner, procost, + pronargs, proargnames, proargtypes)))) as doc + FROM x), + z AS (SELECT xmltable.* + FROM y, + LATERAL xmltable('/data/proc' PASSING doc + COLUMNS proname name, + proowner oid, + procost float, + pronargs int, + proargnames text, + proargtypes text)) + SELECT * FROM z + EXCEPT SELECT * FROM x; + proname | proowner | procost | pronargs | proargnames | proargtypes +---------+----------+---------+----------+-------------+------------- +(0 rows) + +CREATE TABLE xmltest2(x xml, _path text); +INSERT INTO xmltest2 VALUES('1', 'A'); +INSERT INTO xmltest2 VALUES('2', 'B'); +INSERT INTO xmltest2 VALUES('3', 'C'); +INSERT INTO xmltest2 VALUES('2', 'D'); +SELECT xmltable.* FROM xmltest2, LATERAL xmltable('/d/r' PASSING x COLUMNS a int PATH '' || lower(_path) || 'c'); + a +--- + 1 + 2 + 3 + 2 +(4 rows) + +SELECT xmltable.* FROM xmltest2, LATERAL xmltable(('/d/r/' || lower(_path) || 'c') PASSING x COLUMNS a int PATH '.'); + a +--- + 1 + 2 + 3 + 2 +(4 rows) + +SELECT xmltable.* FROM xmltest2, LATERAL xmltable(('/d/r/' || lower(_path) || 'c') PASSING x COLUMNS a int PATH 'x' DEFAULT ascii(_path) - 54); + a +---- + 11 + 12 + 13 + 14 +(4 rows) + +-- XPath result can be boolean or number too +SELECT * FROM XMLTABLE('*' PASSING 'a' COLUMNS a xml PATH '.', b text PATH '.', c text PATH '"hi"', d boolean PATH '. = "a"', e integer PATH 'string-length(.)'); + a | b | c | d | e +----------+---+----+---+--- + a | a | hi | t | 1 +(1 row) + +\x +SELECT * FROM XMLTABLE('*' PASSING 'pre&deeppost' COLUMNS x xml PATH 'node()', y xml PATH '/'); +-[ RECORD 1 ]----------------------------------------------------------- +x | pre&deeppost +y | pre&deeppost+ + | + +\x +SELECT * FROM XMLTABLE('.' PASSING XMLELEMENT(NAME a) columns a varchar(20) PATH '""', b xml PATH '""'); + a | b +--------+-------------- + | <foo/> +(1 row) + -- cgit v1.2.3