Pages

Sunday, August 21, 2016

XMLTABLE implementation is done

I finished the patch for XMLTABLE in PostgreSQL. It is working well. Examples what I found on net works:

postgres=#   SELECT t.id, x.*
     FROM employees t,
          XMLTABLE ('/Employees/Employee[age>40]'
                    PASSING t.data
                    COLUMNS firstname VARCHAR(30) PATH 'firstname', 
                            lastname VARCHAR(30) PATH 'lastname',
                            age VARCHAR(30) PATH 'age') x
    WHERE t.id = 1;
┌────┬───────────┬──────────┬─────┐
│ id │ firstname │ lastname │ age │
╞════╪═══════════╪══════════╪═════╡
│  1 │ Jim       │ Moriarty │ 52  │
│  1 │ Mycroft   │ Holmes   │ 41  │
└────┴───────────┴──────────┴─────┘
(2 rows)

Time: 1.619 ms
postgres=# SELECT t.id, x.*
     FROM employees t,
          XMLTABLE ('/Employees/Employee[@emplid=2222]'
                    PASSING t.data
                    COLUMNS firstname VARCHAR(30) PATH 'firstname', 
                            lastname VARCHAR(30) PATH 'lastname') x
    WHERE t.id = 1;
┌────┬───────────┬──────────┐
│ id │ firstname │ lastname │
╞════╪═══════════╪══════════╡
│  1 │ Sherlock  │ Homes    │
└────┴───────────┴──────────┘
(1 row)

Time: 1.606 ms
postgres=#   SELECT emp.id, x.*
     FROM employees emp,
          XMLTABLE ('/Employees/Employee'
                    PASSING emp.data
                    COLUMNS firstname VARCHAR(30) PATH 'firstname',
                            type VARCHAR(30) PATH '@type') x;
┌────┬───────────┬───────┐
│ id │ firstname │ type  │
╞════╪═══════════╪═══════╡
│  1 │ John      │ admin │
│  1 │ Sherlock  │ admin │
│  1 │ Jim       │ user  │
│  1 │ Mycroft   │ user  │
└────┴───────────┴───────┘
(4 rows)

Time: 1.556 ms

Please, test it, check it.

3 comments: