Postgres Pro
Downloads
Home   >   mailing lists

patch: xmltable - proof concept - Mailing list pgsql-hackers

From Pavel Stehule
Subject patch: xmltable - proof concept
Date
Msg-id CAFj8pRAZkVbihXNCgvf9n2QZ=bHNPSa3WH1Ayv-N5LLu1cRN6Q@mail.gmail.com
Whole thread Raw
Responses Re: patch: xmltable - proof concept  (Pavel Stehule <pavel.stehule@gmail.com>)
Re: patch: xmltable - proof concept  (Alvaro Herrera <alvherre@2ndquadrant.com>)
List pgsql-hackers
Tree view
Hi

I am sending a initial implementation of xmltable function:

The code is not clean now, but it does almost of expected work. The usage is simple. It is fast - 16K entries in 400ms.

I invite any help with documentation and testing.

The full ANSI/SQL, or Oracle compatible implementation is not possible due limits of libxml2, but for typical usage it should to work well. It doesn't need any new reserved keyword, so there should not be hard barriers for accepting (when this work will be complete).

Example:

postgres=# SELECT * FROM xmldata;
┌──────────────────────────────────────────────────────────────────┐
│                               data                               │
╞══════════════════════════════════════════════════════════════════╡
│ <ROWS>                                                          ↵│
│ <ROW id="1">                                                    ↵│
│   <COUNTRY_ID>AU</COUNTRY_ID>                                   ↵│
│   <COUNTRY_NAME>Australia</COUNTRY_NAME>                        ↵│
│   <REGION_ID>3</REGION_ID>                                      ↵│
│ </ROW>                                                          ↵│
│ <ROW id="2">                                                    ↵│
│   <COUNTRY_ID>CN</COUNTRY_ID>                                   ↵│
│   <COUNTRY_NAME>China</COUNTRY_NAME>                            ↵│
│   <REGION_ID>3</REGION_ID>                                      ↵│
│ </ROW>                                                          ↵│
│ <ROW id="3">                                                    ↵│
│   <COUNTRY_ID>HK</COUNTRY_ID>                                   ↵│
│   <COUNTRY_NAME>HongKong</COUNTRY_NAME>                         ↵│
│   <REGION_ID>3</REGION_ID>                                      ↵│
│ </ROW>                                                          ↵│
│ <ROW id="4">                                                    ↵│
│   <COUNTRY_ID>IN</COUNTRY_ID>                                   ↵│
│   <COUNTRY_NAME>India</COUNTRY_NAME>                            ↵│
│   <REGION_ID>3</REGION_ID>                                      ↵│
│ </ROW>                                                          ↵│
│ <ROW id="5">                                                    ↵│
│   <COUNTRY_ID>JP</COUNTRY_ID>                                   ↵│
│   <COUNTRY_NAME>Japan</COUNTRY_NAME>                            ↵│
│   <REGION_ID>3</REGION_ID><PREMIER_NAME>Sinzo Abe</PREMIER_NAME>↵│
│ </ROW>                                                          ↵│
│ <ROW id="6">                                                    ↵│
│   <COUNTRY_ID>SG</COUNTRY_ID>                                   ↵│
│   <COUNTRY_NAME>Singapore</COUNTRY_NAME>                        ↵│
│   <REGION_ID>3</REGION_ID><SIZE unit="km">791</SIZE>            ↵│
│ </ROW>                                                          ↵│
│ </ROWS>                                                          │
└──────────────────────────────────────────────────────────────────┘
(1 row)
postgres=# SELECT  xmltable.*
postgres-#    FROM (SELECT data FROM xmldata) x,
postgres-#         LATERAL xmltable('/ROWS/ROW'
postgres(#                          PASSING data
postgres(#                          COLUMNS id int PATH '@id',
postgres(#                                   country_name text PATH 'COUNTRY_NAME',
postgres(#                                   country_id text PATH 'COUNTRY_ID',
postgres(#                                   region_id int PATH 'REGION_ID',
postgres(#                                   size float PATH 'SIZE',
postgres(#                                   unit text PATH 'SIZE/@unit',
postgres(#                                   premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');
┌────┬──────────────┬────────────┬───────────┬──────┬──────┬───────────────┐
│ id │ country_name │ country_id │ region_id │ size │ unit │ premier_name  │
╞════╪══════════════╪════════════╪═══════════╪══════╪══════╪═══════════════╡
│  1 │ Australia    │ AU         │         3 │    ¤ │ ¤    │ not specified │
│  2 │ China        │ CN         │         3 │    ¤ │ ¤    │ not specified │
│  3 │ HongKong     │ HK         │         3 │    ¤ │ ¤    │ not specified │
│  4 │ India        │ IN         │         3 │    ¤ │ ¤    │ not specified │
│  5 │ Japan        │ JP         │         3 │    ¤ │ ¤    │ Sinzo Abe     │
│  6 │ Singapore    │ SG         │         3 │  791 │ km   │ not specified │
└────┴──────────────┴────────────┴───────────┴──────┴──────┴───────────────┘
(6 rows)

Regards

Pavel
Attachment

pgsql-hackers by date:

Previous
From: Andreas Joseph Krogh
Date:
Subject: Re: Bogus ANALYZE results for an otherwise-unique column with many nulls
Next
From: Ilya Kosmodemiansky
Date:
Subject: Wait events monitoring future development