oracle - 试图将 ORACLE 代码转换为 POSTGRESQL

Question

    ls_sql := (CONCAT_WS('', '
          SELECT
             INITCAP(
                    CASE
                        WHEN (TRIM(STREET_NUMBER) IS NOT NULL) THEN
                            TRIM(STREET_NUMBER)||'' ''
                    END)||
                    CASE
                        WHEN (STREET_DIRECTION IS NOT NULL) AND (STREET_DIRECTION <> ''', aws_oracle_ext.getglobalvariable(proutinename => 'PROD.PROPERTY_FUNCTIONS', pvariable => 'c_blank_street_direction', ptp => NULL::CHARACTER(2)), ''') THEN
                            TRIM(STREET_DIRECTION)||'' ''
                    END||
             INITCAP(
                TRIM(STREET_NAME)
             ),
                CASE WHEN (UNIT_NUMBER IS NOT NULL) THEN
                LTRIM(INITCAP(
                    CASE
                        WHEN (UNIT_TYPE IS NULL) THEN ''Unit''
                            WHEN (UNIT_TYPE = ''', aws_oracle_ext.getglobalvariable(proutinename => 'PROD.PROPERTY_FUNCTIONS', pvariable => 'c_blank_unit_type_code', ptp => NULL::CHARACTER VARYING(10)), ''') THEN ''''
                        ELSE INITCAP(UNIT_TYPE)
                    END
                    )||'' '')||UNIT_NUMBER
                END,
             INITCAP(CITIES.CITY_NAME)||'', ''||STATE||''  ''||ZIP_CODE||
                CASE
                    WHEN (ZIP_PLUS_4 IS NOT NULL) THEN ''-''||ZIP_PLUS_4
                END
                ,
       '));
    
        IF (ls_address_type = aws_oracle_ext.getglobalvariable(proutinename => 'PROD.PROPERTY_FUNCTIONS', pvariable => 'c_fa_addrtype_property', ptp => NULL::CHARACTER VARYING(50))) THEN
            /* Property */
            ls_sql := CONCAT_WS('', ls_sql, '
                PROPERTY.STATE
             FROM
                prod.PROPERTY,
                prod.CITIES
             WHERE
                ( PROPERTY.CITY_CODE = CITIES.CITY_CODE ) AND
                ( PROPERTY.PROPERTY_ID = :n_property_id_in ) AND
                (limit < 2)
          ');
        ELSE
            /* Commercial Property */
            ls_sql := CONCAT_WS('', ls_sql, '
                COMMERCIAL_PROPERTY.STATE
             FROM
                prod.COMMERCIAL_PROPERTY,
                prod.CITIES
             WHERE
                ( COMMERCIAL_PROPERTY.CITY_CODE = CITIES.CITY_CODE ) AND
                ( COMMERCIAL_PROPERTY.COMMERCIAL_PROPERTY_ID = :n_property_id_in ) AND
                (limit  < 2)
          ');
        END IF;
        /* Execute the SQL */
        raise notice 'ls_sql %',ls_sql;
        

这是一小段代码，我在其中打印 ls_sql 以检查它给出的值！但是当我调用该函数时，它会返回一个异常值！有人可以帮我确定我应该在哪里进行更改吗？以下是我运行此功能时得到的结果。我无法确定代码的哪一部分不起作用。

    NOTICE:  lb_statesingleline f
    NOTICE:  lb_street_unit_line f
    NOTICE:  lb_citystatezip_line  f
    NOTICE:  ls_spacer , 
    NOTICE:  ls_sql 
          SELECT
             INITCAP(
                    CASE
                        WHEN (TRIM(STREET_NUMBER) IS NOT NULL) THEN
                            TRIM(STREET_NUMBER)||' '
                    END)||
                    CASE
                        WHEN (STREET_DIRECTION IS NOT NULL) AND (STREET_DIRECTION <> 'ZZ') THEN
                            TRIM(STREET_DIRECTION)||' '
                    END||
             INITCAP(
                TRIM(STREET_NAME)
             ),
                CASE WHEN (UNIT_NUMBER IS NOT NULL) THEN
                LTRIM(INITCAP(
                    CASE
                        WHEN (UNIT_TYPE IS NULL) THEN 'Unit'
                            WHEN (UNIT_TYPE = 'ZZ') THEN ''
                        ELSE INITCAP(UNIT_TYPE)
                    END
                    )||' ')||UNIT_NUMBER
                END,
             INITCAP(CITIES.CITY_NAME)||', '||STATE||'  '||ZIP_CODE||
                CASE
                    WHEN (ZIP_PLUS_4 IS NOT NULL) THEN '-'||ZIP_PLUS_4
                END
                ,
       
                PROPERTY.STATE
             FROM
                prod.PROPERTY,
                prod.CITIES
             WHERE
                ( PROPERTY.CITY_CODE = CITIES.CITY_CODE ) AND
                ( PROPERTY.PROPERTY_ID = :n_property_id_in ) AND
                (limit < 2)
          
    NOTICE:  EXCEPTION P0001
    NOTICE:  RESULT1 <NULL>
    DO.
    
     

标签： oraclepostgresqlplpgsqlschema-migrationaws-sct