Friday, August 26, 2016

Orafce package for PostgreSQL 9.3, 9.4, 9.5, 9.6 for WIN32, WIN64 is available

Please, download from link.

Thursday, August 25, 2016

plpgsql_check for PostgreSQL 9.4, 9.5, 9.6 compiled for WIN32, WIN64 is available

please download from link

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