将视图从Oracle迁移到PostgreSQL (带有提取值()函数)

Question

我正在将一个数据库从Oracle迁移到PostgreSQL，并且有几个视图有问题。

我找不到extractvalue()和xmltype()的等效函数.

NVL(extractvalue(xmltype(METADATA), '/slb/structMap/div/mptr/@OTHERLOCTYPE', 'xmlns="http://www.nb.admin.ch/standards/slb" xmlns:marc="http://www.loc.gov/MARC21/slim"'), '')  

以下是完整的视图代码：

  CREATE OR REPLACE FORCE VIEW "INGEST_TEST"."AIP_TYPE_INFO" ("AIP_ID", "AIP_VERSION", "TYPE_VALUE") AS 
select
 aip_id,
 aip_version,
case 
    when METADATA is not null then
        NVL(extractvalue(xmltype(METADATA), '/slb/structMap/div/mptr/@OTHERLOCTYPE', 'xmlns="http://www.nb.admin.ch/standards/slb" xmlns:marc="http://www.loc.gov/MARC21/slim"'), '')  
        || '/' ||
        NVL(extractvalue(xmltype(METADATA), '/slb/structMap/div/div[position() = 1]/mptr/@OTHERLOCTYPE', 'xmlns="http://www.nb.admin.ch/standards/slb" xmlns:marc="http://www.loc.gov/MARC21/slim"'), '') 
    else '/'
 end as type_value
from aip ;

Oracle的功能如下：

extractvalue()：https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions052.htm#i1131042

xmltype()：https://docs.oracle.com/cd/B10500_01/appdev.920/a96616/arxml24.htm

我要么从未使用过这些函数，也从未使用过我要迁移的数据库。我只有将其迁移到PostgreSQL的任务。

NVL()函数在PostgreSQL：COALESCE()中有其等价的

有人知道如何处理这件事吗？

下面是XML的一个示例:这个示例的https://www.dropbox.com/s/59mosi9ewcx67ga/XML_EXEMPLE.xml?dl=0视图应该给我以下内容：

zeitschrift/zeitschriftennummer

Answer 1

Postgres的XML处理在XMNL名称空间方面要严格一些，因此xpath表达式必须在每个元素前面加上正确的命名空间。

基于示例XML，只需要http://www.nb.admin.ch/standards/slb命名空间，因此视图应该如下所示：

CREATE OR REPLACE FORCE VIEW AIP_TYPE_INFO
AS 
select aip_id,
       aip_version,
       case 
         when METADATA is not null then
            concat_ws('/', (xpath('/slb:slb/slb:structMap/slb:div/slb:mptr/@OTHERLOCTYPE', METADATA::xml, t.nsp))[1],
                           (xpath('/slb:slb/slb:structMap/slb:div/slb:div[position() = 1]/slb:mptr/@OTHERLOCTYPE', metadata::xml, t.nsp))[1])
         else '/'
       end as type_value
from aip, 
     (values (array[array['slb', 'http://www.nb.admin.ch/standards/slb']])) as t(nsp);

部分：

, (values (array[array['slb', 'http://www.nb.admin.ch/standards/slb']]))

只需创建带有所需命名空间定义数组的单个行。它是为了缩短实际的xpath()调用。如果不喜欢，可以将函数中的两个t.nsp替换为array[array['slb', 'http://www.nb.admin.ch/standards/slb']]。

注意，xpath()返回一个匹配数组。这就是为什么需要(xpath(...))[1]的原因:它只是从数组中获得第一个匹配。

concat_ws()将自动处理空值--这与Oracle略有不同。如果两个xpath()表达式都计算为null，那么Oracle将返回'/'，但concat_ws()将返回一个空字符串。如果您不想这样做，您需要使用与Oracle相同的方法。

在线示例：http://rextester.com/FANG87147