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.

Good presentation about differences between PostgreSQL and Oracle from Oracle expert perspective

see http://www.p2d2.cz/files/postgres-for-oracle-dbas.pdf

Sunday, August 7, 2016

Prototype of XMLTABLE function is done

The parsing of XML is not simple in Postgres. For more complex data I prefer external procedures in PLPerlu or PLPythonu based on usage of Xmlreader. I hope so this this time will be history. With ANSI SQL XMLTABLE function a transformation of any XML document to table is simple and fast:
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.*
   FROM (SELECT data FROM xmldata) x,
        LATERAL xmltable('/ROWS/ROW'
                         PASSING data
                         COLUMNS id int PATH '@id',
                                  country_name text PATH 'COUNTRY_NAME',
                                  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 │ 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)

link to patch

Wednesday, May 25, 2016

Orafce 3.3.0 was released

Tuesday, May 24, 2016

plpgsql_check 1.0.5 released

New version of plpgsql_check is available. The most important change is support for future PostgreSQL 9.6

https://manager.pgxn.org/distributions/plpgsql_check/1.0.5
https://github.com/okbob/plpgsql_check/releases/tag/v1.0.5

Friday, January 8, 2016

new release Orafce and plpgsql_check

Hi

I released new updates of Orafce and plpgsql_check

Details in release notes

https://github.com/orafce/orafce/releases/tag/VERSION_3_2_1
https://github.com/okbob/plpgsql_check/releases/tag/v1.0.3

PostgreSQL 9.5 are supported by by both extensions

Monday, October 19, 2015

Fedora 23 on Toshiba PORTÉGÉ - few unresolved issues

After years I have new notebook. The installation of Fedora 23 was without harder issues, and I am satisfied with this notebook. I found few issues, that I cannot to solve:

* sound - this issue is really strange - maybe it is PulseAudio issue. I am listening low noise from internal speakers. It is ok, when I am listing any sound. It is ok, when I disable sound. It is ok, if I use a external speakers. Working well in MS Win.

* a fan runs ever. This is known issue of Toshiba's notebooks. The usual control tools of fans doesn't work, and default configuration is not good (although it is safe). This behave can be changed in BIOS, but then the notebook is pretty slow. The noise is not too loud - but it is in higher tone than it is usual. Because it is working well in MS Win, I hope, this issue will be solvable.

* small artifacts in played video. Sometimes the video rendering is few miliseconds late. It isn't often - few times in ten minutes. I didn't test native Intel drivers yet.

These issues are small. All is working well with default configuration and default drivers (graphics, sound, wifi). This notebook has perfect display (with native resolution the Gnome 3 looks perfect), good keyboard, is really light.

Any hints how to fix these issues?