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

Question

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

My result from above query is `"ALTER FUNCTION myschema."pk_audit$sp_get_audit"(OUT "OUT_1" double precision, OUT "OUT_2" double precision)RENAME TO myschema."pk_audit$sp_get_audit"(OUT "OUT_1" double precision, OUT "OUT_2" double precision);"` `ERROR: syntax error at or near "." double precision)RENAME TO myschema."pk_audit... SQL state: 42601` — aan anna philip, Apr 02 '20 at 16:55
ALTER FUNCTION allows to rename the function ie to change the function name but not to change function schema. — pifor, Apr 02 '20 at 17:03
I don't want to change my schema, if you see the output, Schema is same. — aan anna philip, Apr 02 '20 at 17:07
Try to put all names into double quotes: `"myschema.pk_audit$sp_get_audit" .`ALTER FUNCTION does not accept parameters list twice. For example: `ALTER FUNCTION sqrt(integer) RENAME TO square_root;` — pifor, Apr 02 '20 at 17:12
I need to change myshema.`"pk_audit$sp_get_audit" to myschema."pk_audit.sp_get_audit"` . ie need a replacement of $ to . (dot). It is difficult change function manually. I need a single script. — aan anna philip, Apr 02 '20 at 17:13
the above mentioned example I found on postgresql.org. need a script to change all the 1500 functions in my schema. — aan anna philip, Apr 02 '20 at 17:15
`RENAME TO "myschema.pk_audit.sp_get_audit"(OUT "OUT_...` i've tried like as you suggested. getting syntax error. — aan anna philip, Apr 02 '20 at 17:20

score 2 · Accepted Answer · answered Apr 02 '20 at 17:35

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)

Fantastic. Worked !! Muchas Gracias . – aan anna philip Apr 02 '20 at 17:43 — aan anna philip, Apr 02 '20 at 17:43

score 2 · Answer 2 · answered Apr 03 '20 at 06:45

2

The easiest solution is to use the \gexec feature of psql:

SELECT format(
          'ALTER FUNCTION %s RENAME TO %I',
          oid::regprocedure,
          replace(proname, '$', '.')
       )
FROM pg_proc
WHERE pronamespace::regnamespace::text = 'my_schema' \gexec

answered Apr 03 '20 at 06:45

Laurenz Albe

209,280
17
206
263

Thank you Laurenz for your valuable guidance. – aan anna philip Apr 03 '20 at 07:38

score 1 · Answer 3 · answered Apr 02 '20 at 17:34

This will not work. Even if your generated statement has a valid syntax it will fail, unless you have created a Postgres schema for each Oracle package. Oracle packages gather multiple procedures and into a single database object. Unfortunately Postgres has no such concept so each procedure within the package becomes an independent function is Postgres. This results in a naming structure issue beyond the $ in the name. In Oracle the format reference to package_name.procedure_name says to run procedure name that is within the package name, the same line of code in Postgres is interpreted as schema_name.function_name. That is why the conversion routine replaces it with package_name$procedure_name which is still a valid Postgres within name the same schema. (Subject to translated name length; what does the translation routine do when combined length of package_name + procedure_name + 1 is over the name length limit in Postgres (63)? ) That aspect may make it easier on an overall system effort to update the middleware. And that is just the beginnings of your package conversion issues. What about:

Package level types, cursors, variables, collections, etc defined only in the package spec.
The same as above defined only in the package body but NOT within any procedure.
References to either of the above, from within the resulting Poatgres functions.
Other functionality of packages in Oracle not directly translatable to Postgres. All of these must be looked at and may perhaps require modifications to either or both of the Postgres functions and your middleware. So basically running a conversion script is just the first step in a process of many steps for your conversion.

Good Luck!
NOTE: Reference in above to procedure refers to both procedures and functions.

Yes but this question is only about to generate a SQL statement to rename a function without changing function schema. — pifor, Apr 02 '20 at 17:40

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

3 Answers3

Linked