Naming issues on Oracle Packages conversion to PostgreSQL using Aws SCT tool

I had migrated Oracle db to Aurora postgreSQL with the help of AWS SCT tool. All packages and triggers are converted as functions in PostgreSQL. My issue here is all the names are converted with a $ (dollar) symbol.

for example, A package and associated stored proc in Oracle pk_audit.sp_get_audit converted to postgreSQL as pk_audit$sp_get_audit with a $ symbol. but, In the middleware db object name is pk_audit.sp_get_audit . In order to minimise the effort on the middleware, I need to convert all the functions from pk_audit$sp_get_audit to pk_audit.sp_get_audit.

I've more than 1500 functions converted with $ symbol. Need a script to alter all the User Defined Functions names . I've created a script to build the alter scripts.

`select a.alter_statement|| replace(a.rename_statement,'$','.')
From
(
SELECT format('ALTER %s %I.%I(%s)'
            , 'FUNCTION'
              ,CONCAT('"',n.nspname,'"') 
            , p.proname
            , pg_catalog.pg_get_function_identity_arguments(p.oid)
             ) AS alter_statement,
             format('RENAME %s %I.%I(%s);'
            , 'TO' 
            , CONCAT('"',n.nspname,'"') 
            , p.proname
            , pg_catalog.pg_get_function_identity_arguments(p.oid)
             ) AS rename_statement
FROM   pg_catalog.pg_proc p
JOIN   pg_catalog.pg_namespace n ON n.oid = p.pronamespace
and     n.nspname = 'my_schema' ORDER  BY 1
) a;`

But the result is throwing error. Kindly help thanks

Try this:

select a.alter_statement|| replace(a.rename_statement,'$','.')
From
(
SELECT format('ALTER FUNCTION %s.%s(%s) '
              ,CONCAT(n.nspname) 
              , p.proname
              , pg_catalog.pg_get_function_identity_arguments(p.oid)
             ) AS alter_statement,
             format('RENAME TO %I'
             ,p.proname
             ) AS rename_statement
FROM   pg_catalog.pg_proc p
JOIN   pg_catalog.pg_namespace n ON n.oid = p.pronamespace
and     n.nspname = 'newschema' ORDER  BY 1
) a;

Example:

select a.alter_statement|| replace(a.rename_statement,'$','.')
From
(
SELECT format('ALTER FUNCTION %s.%s(%s) '
              ,CONCAT(n.nspname) 
              , p.proname
              , pg_catalog.pg_get_function_identity_arguments(p.oid)
             ) AS alter_statement,
             format('RENAME TO %I'
             ,p.proname
             ) AS rename_statement
FROM   pg_catalog.pg_proc p
JOIN   pg_catalog.pg_namespace n ON n.oid = p.pronamespace
and     n.nspname = 'newschema' ORDER  BY 1
) a;
                                    ?column?                                     
---------------------------------------------------------------------------------
 ALTER FUNCTION newschema.package$function(integer) RENAME TO "package.function"
(1 row)