Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitb7f0be9

Browse files
committed
Accept TEXT and CDATA nodes in XMLTABLE's column_expression.
Column expressions that match TEXT or CDATA nodes must return thecontents of the nodes themselves, not the content of non-existingchildren (i.e. the empty string).Author: Markus WinandReported-by: Markus WinandReviewed-by: Álvaro HerreraDiscussion:https://postgr.es/m/0684A598-002C-42A2-AE12-F024A324EAE4@winand.at
1 parent3adcad4 commitb7f0be9

File tree

4 files changed

+28
-24
lines changed

4 files changed

+28
-24
lines changed

‎src/backend/utils/adt/xml.c

Lines changed: 14 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -4508,11 +4508,21 @@ XmlTableGetValue(TableFuncScanState *state, int colnum,
45084508
elseif (count==1)
45094509
{
45104510
xmlChar*str;
4511+
xmlNodePtrnode;
45114512

4512-
str=xmlNodeListGetString(xtCxt->doc,
4513-
xpathobj->nodesetval->nodeTab[0]->xmlChildrenNode,
4514-
1);
4513+
/*
4514+
* Most nodes (elements and even attributes) store their data
4515+
* in children nodes. If they don't have children nodes, it
4516+
* means that they are empty (e.g. <element/>). Text nodes and
4517+
* CDATA sections are an exception: they don't have children
4518+
* but have content in the Text/CDATA node itself.
4519+
*/
4520+
node=xpathobj->nodesetval->nodeTab[0];
4521+
if (node->type!=XML_CDATA_SECTION_NODE&&
4522+
node->type!=XML_TEXT_NODE)
4523+
node=node->xmlChildrenNode;
45154524

4525+
str=xmlNodeListGetString(xtCxt->doc,node,1);
45164526
if (str!=NULL)
45174527
{
45184528
PG_TRY();
@@ -4529,13 +4539,7 @@ XmlTableGetValue(TableFuncScanState *state, int colnum,
45294539
}
45304540
else
45314541
{
4532-
/*
4533-
* This line ensure mapping of empty tags to PostgreSQL
4534-
* value. Usually we would to map a empty tag to empty
4535-
* string. But this mapping can create empty string when
4536-
* user doesn't expect it - when empty tag is enforced by
4537-
* libxml2 - when user uses a text() function for example.
4538-
*/
4542+
/* Ensure mapping of empty tags to PostgreSQL values. */
45394543
cstr="";
45404544
}
45414545
}

‎src/test/regress/expected/xml.out

Lines changed: 6 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1024,7 +1024,7 @@ SELECT xmltable.*
10241024
PASSING data
10251025
COLUMNS id int PATH '@id',
10261026
_id FOR ORDINALITY,
1027-
country_name text PATH 'COUNTRY_NAME' NOT NULL,
1027+
country_name text PATH 'COUNTRY_NAME/text()' NOT NULL,
10281028
country_id text PATH 'COUNTRY_ID',
10291029
region_id int PATH 'REGION_ID',
10301030
size float PATH 'SIZE',
@@ -1046,7 +1046,7 @@ CREATE VIEW xmltableview1 AS SELECT xmltable.*
10461046
PASSING data
10471047
COLUMNS id int PATH '@id',
10481048
_id FOR ORDINALITY,
1049-
country_name text PATH 'COUNTRY_NAME' NOT NULL,
1049+
country_name text PATH 'COUNTRY_NAME/text()' NOT NULL,
10501050
country_id text PATH 'COUNTRY_ID',
10511051
region_id int PATH 'REGION_ID',
10521052
size float PATH 'SIZE',
@@ -1075,7 +1075,7 @@ CREATE OR REPLACE VIEW public.xmltableview1 AS
10751075
"xmltable".premier_name
10761076
FROM ( SELECT xmldata.data
10771077
FROM xmldata) x,
1078-
LATERAL XMLTABLE(('/ROWS/ROW'::text) PASSING (x.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))
1078+
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))
10791079
EXPLAIN (COSTS OFF) SELECT * FROM xmltableview1;
10801080
QUERY PLAN
10811081
-----------------------------------------
@@ -1085,15 +1085,15 @@ EXPLAIN (COSTS OFF) SELECT * FROM xmltableview1;
10851085
(3 rows)
10861086

10871087
EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM xmltableview1;
1088-
QUERY PLAN
1089-
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1088+
QUERY PLAN
1089+
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
10901090
Nested Loop
10911091
Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name
10921092
-> Seq Scan on public.xmldata
10931093
Output: xmldata.data
10941094
-> Table Function Scan on "xmltable"
10951095
Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name
1096-
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))
1096+
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))
10971097
(7 rows)
10981098

10991099
-- XMLNAMESPACES tests

‎src/test/regress/expected/xml_2.out

Lines changed: 6 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1004,7 +1004,7 @@ SELECT xmltable.*
10041004
PASSING data
10051005
COLUMNS id int PATH '@id',
10061006
_id FOR ORDINALITY,
1007-
country_name text PATH 'COUNTRY_NAME' NOT NULL,
1007+
country_name text PATH 'COUNTRY_NAME/text()' NOT NULL,
10081008
country_id text PATH 'COUNTRY_ID',
10091009
region_id int PATH 'REGION_ID',
10101010
size float PATH 'SIZE',
@@ -1026,7 +1026,7 @@ CREATE VIEW xmltableview1 AS SELECT xmltable.*
10261026
PASSING data
10271027
COLUMNS id int PATH '@id',
10281028
_id FOR ORDINALITY,
1029-
country_name text PATH 'COUNTRY_NAME' NOT NULL,
1029+
country_name text PATH 'COUNTRY_NAME/text()' NOT NULL,
10301030
country_id text PATH 'COUNTRY_ID',
10311031
region_id int PATH 'REGION_ID',
10321032
size float PATH 'SIZE',
@@ -1055,7 +1055,7 @@ CREATE OR REPLACE VIEW public.xmltableview1 AS
10551055
"xmltable".premier_name
10561056
FROM ( SELECT xmldata.data
10571057
FROM xmldata) x,
1058-
LATERAL XMLTABLE(('/ROWS/ROW'::text) PASSING (x.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))
1058+
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))
10591059
EXPLAIN (COSTS OFF) SELECT * FROM xmltableview1;
10601060
QUERY PLAN
10611061
-----------------------------------------
@@ -1065,15 +1065,15 @@ EXPLAIN (COSTS OFF) SELECT * FROM xmltableview1;
10651065
(3 rows)
10661066

10671067
EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM xmltableview1;
1068-
QUERY PLAN
1069-
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1068+
QUERY PLAN
1069+
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
10701070
Nested Loop
10711071
Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name
10721072
-> Seq Scan on public.xmldata
10731073
Output: xmldata.data
10741074
-> Table Function Scan on "xmltable"
10751075
Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name
1076-
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))
1076+
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))
10771077
(7 rows)
10781078

10791079
-- XMLNAMESPACES tests

‎src/test/regress/sql/xml.sql

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -349,7 +349,7 @@ SELECT xmltable.*
349349
PASSING data
350350
COLUMNS idintPATH'@id',
351351
_id FOR ORDINALITY,
352-
country_nametextPATH'COUNTRY_NAME'NOT NULL,
352+
country_nametextPATH'COUNTRY_NAME/text()'NOT NULL,
353353
country_idtextPATH'COUNTRY_ID',
354354
region_idintPATH'REGION_ID',
355355
size floatPATH'SIZE',
@@ -362,7 +362,7 @@ CREATE VIEW xmltableview1 AS SELECT xmltable.*
362362
PASSING data
363363
COLUMNS idintPATH'@id',
364364
_id FOR ORDINALITY,
365-
country_nametextPATH'COUNTRY_NAME'NOT NULL,
365+
country_nametextPATH'COUNTRY_NAME/text()'NOT NULL,
366366
country_idtextPATH'COUNTRY_ID',
367367
region_idintPATH'REGION_ID',
368368
size floatPATH'SIZE',

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp