使用Aws SCT工具将Oracle软件包转换为PostgreSQL时的命名问题

Question

我已经在AWS SCT工具的帮助下将Oracle数据库迁移到Aurora postgreSQL。所有包和触发器在PostgreSQL中都转换为函数。我在这里的问题是，所有的名字都用$(美元(符号转换。

例如，Oraclepk_audit.sp_get_audit中的一个包和相关的存储过程转换为postgreSQL，作为带有$符号的pk_audit$sp_get_audit。但是，在中间件中，数据库对象的名称是pk_audit.sp_get_audit。为了最大限度地减少中间件上的工作量，我需要将所有函数从pk_audit$sp_get_audit转换为pk_audit.sp_get_audit。

我已经用$符号转换了1500多个函数。需要一个脚本来更改所有用户定义函数的名称。我已经创建了一个脚本来构建alter脚本。

`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;`

但结果是抛出错误。请帮忙，谢谢

Answer 1

试试这个：

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;

示例：

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)

Answer 2

最简单的解决方案是使用psql:的gexec功能

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

Answer 3

这将不起作用。即使您生成的语句具有有效的语法，它也会失败，除非您为每个Oracle包创建了Postgres模式。Oracle软件包将多个过程收集到一个数据库对象中。遗憾的是，Postgres没有这样的概念，所以包中的每个过程都成为一个独立的函数。这会导致名称中$以外的命名结构问题。在Oracle中，对package_name.proccure_name的格式引用表示要运行包名称中的过程名称，Postgres中的同一行代码被解释为schema_name.function_name。这就是为什么转换例程将其替换为package_name$procedure_name，后者仍然是名称相同架构中的有效Postgres。(根据翻译后的名称长度；当package_name+procedure_name+1的组合长度超过Postgres(63(中的名称长度限制时，翻译例程会做什么？(这一方面可以使整个系统更容易地更新中间件。这仅仅是包转换问题的开始。关于：

• 仅在包规范中定义的包级别类型、游标、变量、集合等
• 与上述定义相同，仅在包装体中定义，但不在任何程序中定义
• 从生成的Poatgres函数中引用上述任一项
• Oracle中包的其他功能无法直接翻译为Postgres。所有这些都必须考虑，并且可能需要对Postgres函数和中间件中的一个或两个进行修改。因此，基本上，运行转换脚本只是转换过程中的第一步
  
  祝你好运
  注意：上文中对过程的引用指的是过程和功能