Oracle到Postgres转换Regexp的概念

Question

请有人告诉我如何将存储过程中的oracle源代码转换为Postgresql。

CURSOR DATA_PKG (iPKG varchar2)IS
SELECT REGEXP_SUBSTR(iPKG, '[^,]+', 1, LEVEL) Data_PKG FROM dual
connect BY REGEXP_SUBSTR(iPKG, '[^,]+', 1, LEVEL) IS NOT NULL;
CURSOR LEC_PKG (iPKG varchar2) IS
SELECT REGEXP_SUBSTR(iPKG, '[^,]+', 1, LEVEL) LEC_Pkg FROM dual
connect BY REGEXP_SUBSTR(iPKG, '[^,]+', 1, LEVEL) IS NOT NULL;

我的Postgres代码如下

c3 CURSOR (ipkg TEXT) FOR
    WITH RECURSIVE t (pkg)
    AS (SELECT
        aws_oracle_ext.regexp_substr(ipkg, '[^,]+', 1, 1) AS data_pkg
    UNION ALL
    SELECT
        aws_oracle_ext.regexp_substr(ipkg, '[^,]+', 1, t.level + 1)
        FROM t
        WHERE aws_oracle_ext.regexp_substr(ipkg, '[^,]+', 1, level + 1) IS NOT NULL)
    SELECT
        data_pkg
        FROM t;
    c4 CURSOR (ipkg TEXT) FOR
    WITH RECURSIVE t2 (pkg)
    AS (SELECT
        aws_oracle_ext.regexp_substr(ipkg, '[^,]+', 1, 1) AS lec_pkg
    UNION ALL
    SELECT
        aws_oracle_ext.regexp_substr(ipkg, '[^,]+', 1, t2.level + 1)
        FROM t2
        WHERE aws_oracle_ext.regexp_substr(ipkg, '[^,]+', 1, level + 1) IS NOT NULL)
    SELECT
        lec_pkg
        FROM t2;

但是当我在Postgresql中执行这个函数时，它抛出一个错误，如下所示，

ERROR:  column t.level does not exist LINE 6: ...   aws_oracle_ext.regexp_substr(ipkg, '[^,]+', 1, t.level + ...
                                                             ^ QUERY:  WITH RECURSIVE t (pkg)
    AS (SELECT
        aws_oracle_ext.regexp_substr(ipkg, '[^,]+', 1, 1) AS data_pkg
    UNION ALL
    SELECT
        aws_oracle_ext.regexp_substr(ipkg, '[^,]+', 1, t.level + 1)
        FROM t
        WHERE aws_oracle_ext.regexp_substr(ipkg, '[^,]+', 1, level + 1) IS NOT NULL)
    SELECT
        data_pkg
        FROM t CONTEXT:  PL/pgSQL function "pop_bus_bundle$populate_bundle_names"() line 60 at FOR over cursor SQL state: 42703

Answer 1

aws_oracle_ext.regexp_substr是什么？是某种包装API在进行转换吗？

为什么不直接使用将逗号分隔成行的Postgres等效项呢？似乎最终这就是源代码中select语句所做的事情。

select  regexp_split_to_table(iPKG,',');

Demo