Re: [HACKERS] patch: function xmltable - Mailing list pgsql-hackers
From | Pavel Stehule |
---|---|
Subject | Re: [HACKERS] patch: function xmltable |
Date | |
Msg-id | CAFj8pRABdOReqEYxgeDPy80BRfy3kv3xT9D1DpqJvfdx-DizkA@mail.gmail.com Whole thread Raw |
In response to | Re: [HACKERS] patch: function xmltable (Alvaro Herrera <alvherre@2ndquadrant.com>) |
Responses |
Re: [HACKERS] patch: function xmltable
(Alvaro Herrera <alvherre@2ndquadrant.com>)
|
List | pgsql-hackers |
2017-03-02 19:32 GMT+01:00 Alvaro Herrera <alvherre@2ndquadrant.com>:
So in the old (non-executor-node) implementation, you could attach WITH
ORDINALITY to the xmltable expression and it would count the output
rows, regardless of which XML document it comes from. With the new
implementation, the grammar no longer accepts it. To count output rows,
you still need to use row_number(). Maybe this is okay. This is the
example from the docs, and I add another XML document with two more rows
for xmltable. Look at the three numbering columns ...
It is expected - now tablefunc are not special case of SRF, so it lost all SRF functionality. It is not critical lost - it supports internally FOR ORDINALITY column, and classic ROW_NUMBER can be used. It can be enhanced to support WITH ORDINALITY in future, but I have not any use case for it.
Regards
Pavel
CREATE TABLE xmldata AS SELECT
xml $$
<ROWS>
<ROW id="1">
<COUNTRY_ID>AU</COUNTRY_ID>
<COUNTRY_NAME>Australia</COUNTRY_NAME>
</ROW>
<ROW id="5">
<COUNTRY_ID>JP</COUNTRY_ID>
<COUNTRY_NAME>Japan</COUNTRY_NAME>
<PREMIER_NAME>Shinzo Abe</PREMIER_NAME>
<SIZE unit="sq_mi">145935</SIZE>
</ROW>
<ROW id="6">
<COUNTRY_ID>SG</COUNTRY_ID>
<COUNTRY_NAME>Singapore</COUNTRY_NAME>
<SIZE unit="sq_km">697</SIZE>
</ROW>
</ROWS>
$$ AS data;
insert into xmldata values ($$
<ROWS><ROW id="2"><COUNTRY_ID>CL</COUNTRY_ID><COUNTRY_NAME> Chile</COUNTRY_NAME></ROW>
<ROW id="3"><COUNTRY_ID>AR</COUNTRY_ID><COUNTRY_NAME> Argentina</COUNTRY_NAME></ROW> </ROWS>$$);
SELECT ROW_NUMBER() OVER (), xmltable.*
FROM xmldata,
XMLTABLE('//ROWS/ROW'
PASSING data
COLUMNS id int PATH '@id',
ordinality FOR ORDINALITY,
"COUNTRY_NAME" text,
country_id text PATH 'COUNTRY_ID',
size_sq_km float PATH 'SIZE[@unit = "sq_km"]',
size_other text PATH
'concat(SIZE[@unit!="sq_km"], " ", SIZE[@unit!="sq_km"]/@unit)',
premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified')
;
row_number │ id │ ordinality │ COUNTRY_NAME │ country_id │ size_sq_km │ size_other │ premier_name
────────────┼────┼────────────┼──────────────┼────────────┼─ ───────────┼──────────────┼─── ────────────
1 │ 1 │ 1 │ Australia │ AU │ │ │ not specified
2 │ 5 │ 2 │ Japan │ JP │ │ 145935 sq_mi │ Shinzo Abe
3 │ 6 │ 3 │ Singapore │ SG │ 697 │ │ not specified
4 │ 2 │ 1 │ Chile │ CL │ │ │ not specified
5 │ 3 │ 2 │ Argentina │ AR │ │ │ not specified
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
pgsql-hackers by date: