> RV>> It simply is the way the application stores the data. For Oracle
> we are storing in XML and JSON format, for postgres, due do
> limitations of XML api, we are storing in VARCHAR.
Why not use JSON in Postgres then?
Postgres' JSON functions are at least as powerful as Oracle's (if not better in a lot of areas).
Would be interesting to see what XML function/feature from Oracle you can't replicate/migrate to Postgres.
Another option might be to upgrade to Postgres 12 and define those columns as generated columns as part of the table,
ratherthan a view.
Then you only pay the performance penalty of the extracValueJS() function when you update the table, not for every
select.
Thomas
