Friday, December 23, 2016

OpenERP configuration

I had a customer with strange issues of OpenERP - the main problem was long life of OpenERP/PostgreSQL sessions. The fix was not hard - using pgBouncer in transaction mode with short server life time (ten minutes).

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

Wednesday, May 25, 2016

Orafce 3.3.0 was released