oracle转pgsql的建表函数

这里简单的转换了一下oracle到pgsql的建表转换，主键做了转换，外键，索引，触发器等还没有转。。。。。

create or replace function long_to_char(p_owner varchar2, p_tab  varchar2, cid in number)
  return varchar2 as
  uval varchar2(32767);
begin
  select data_default
    into uval
    from all_tab_columns
   where table_name = upper(p_tab)
     and owner=p_owner
     and column_id = cid;
  uval := substr(uval, 1, 50);
  return uval;
end long_to_char;
/





/**入参:p_oracle_owner oracle属主   
     p_tab:表名
   p_pg_schema:pgsql的属主
   p_force_seq:是否强制生成序列
   p_seq_name:序列名
   
出参:建表语句
余绍阳

--修复  varchar长度更新*2   numeric格式bug修复    增加主键生成
-- 支持自定义序列名
--控制序列仅在一个主键字段的情况下生成,生成后的序列,自已按需取
--update 2019年8月23日19:36:21

--新增: 如果主键长度为32的,用哪个序列替代,否则不生成序列


--修复  varchar长度更新*2   numeric格式bug修复  主键检测
--不在新增id  支持自定义序列名
--序列仅在一个主键字段的情况下生成,生成后的序列,自已按需取
**/

create or replace function get_pg_table_sql(p_oracle_owner varchar2,p_tab varchar2,p_pg_schema varchar2 default 'dxpg',p_force_seq varchar2 default 'N',p_seq_name varchar2 default '')
return clob
as


cursor c_get_col is
select lower(bb.column_name)||(case when bb.DATA_TYPE='varchar' then
                      bb.DATA_TYPE||bb.varType
                                   when bb.DATA_TYPE='numeric' and bb.numType!='(,)' then
                      bb.DATA_TYPE||bb.numType
                                   else
                                   bb.DATA_TYPE end)||bb.is_null||bb.def||','  colName
 from (
select --aa.OWNER,
       aa.COLUMN_NAME||' ' column_name,
       decode(aa.DATA_TYPE,
              'NUMBER',
              'numeric',
              'VARCHAR2',
              'varchar',
              'DATE',
              'timestamp',
              aa.DATA_TYPE) DATA_TYPE,
        '('||(aa.DATA_LENGTH*2)||')' varType,  --
        '('||aa.DATA_PRECISION||','||aa.DATA_SCALE||')' numType,
         (case when aa.NULLABLE='N' then ' not null ' end) is_null,
         (case when long_to_char(aa.OWNER,aa.TABLE_NAME,aa.COLUMN_ID) is not null
          then ' default '||replace(long_to_char(aa.OWNER,aa.TABLE_NAME,aa.COLUMN_ID),'SYSDATE','now()')
         end) def
  from all_tab_columns aa
 where aa.TABLE_NAME = upper(p_tab)
 and aa.OWNER=upper(p_oracle_owner)
 order by aa.COLUMN_ID) bb;


 --注释
 cursor c_get_comm(p_tab_name varchar2) is
 select 'comment on table ' ||p_tab_name || ' is ''' ||
       aa.COMMENTS || ''';' comm
  from all_tab_comments aa
 where aa.TABLE_NAME =upper(p_tab)
 and   aa.OWNER=upper(p_oracle_owner)
/*union all
select 'comment on column '||p_tab_name||'.id is ''主键id'';' from dual*/
union all
select * from (
--列的注释,按照创建列的顺序
select 'comment on column '|| p_tab_name||'.'|| aa.COLUMN_NAME || '  is  ''' ||
       aa.COMMENTS||''';'
  from all_col_comments aa, all_tab_columns bb
 where aa.TABLE_NAME = upper(p_tab)
   and aa.OWNER=upper(p_oracle_owner)
   and aa.OWNER = bb.OWNER
   and aa.TABLE_NAME = bb.TABLE_NAME
   and aa.COLUMN_NAME = bb.COLUMN_NAME
 order by bb.COLUMN_ID);


--获取主键
cursor c_get_key
 is
 select 'PRIMARY KEY(' || listagg(aa.COLUMN_NAME, ',') within group(order by aa.POSITION)||')'  key_name,
         max(aa.POSITION) key_num,
         max(aa.COLUMN_NAME) col_name
   from all_cons_columns aa
  where (aa.owner,constraint_name) = (select bb.OWNER,bb.constraint_name
                             from all_constraints bb
                            where bb.TABLE_NAME = upper(p_tab)
                              and bb.OWNER=upper(p_oracle_owner)
                              and bb.constraint_type = 'P');  
v_get_key c_get_key%rowType;

--获取主键长度
cursor c_get_key_length(p_col varchar2)
is
select aa.DATA_LENGTH
  from all_tab_columns aa
 where aa.TABLE_NAME = upper(p_tab)
   and aa.OWNER = upper(p_oracle_owner)
   and aa.COLUMN_NAME = upper(p_col)
   and aa.DATA_TYPE = 'VARCHAR2';
 
v_key_length integer; 
v_key_flag boolean :=false;

--序列
--序列创建sql
v_seq varchar2(4000);
--序列全名
v_seq_name varchar2(100);
--表全名
v_table_name varchar2(300);

--授权
v_grant varchar2(4000);


--删除表语句
v_title varchar2(200);
--第一列
v_first_col varchar2(100);
--其他列
v_other_col varchar2(32767);

--换行
v_n  varchar2(10):=chr(10);
--建表语句
v_create_table_sql clob;

--注释语句
v_comm varchar2(20000);

begin
  --表全名
  v_table_name:=p_pg_schema||'.' || lower(p_tab) ;
  v_seq_name:=v_table_name||'_seq';
  --支持自定义序列名
  if p_seq_name is not null then
     v_seq_name:= p_pg_schema||'.'||p_seq_name;
  end if;

  v_grant:='grant all privileges on '||v_table_name|| ' to r_egisdspdata_dml;';
  --v_first_col:='id  bigint primary key,';
  v_title:='drop table if exists '||v_table_name||';'||v_n||
            'create table '||v_table_name||v_n||'(';

  --获取主键列
  open c_get_key;
  fetch c_get_key into v_get_key;
  close c_get_key;  
  

  --生成序列
  --如果只有一个主键
  if  v_get_key.key_num=1 then  
     --获取主键varchar2长度
     open c_get_key_length(v_get_key.col_name);
     fetch c_get_key_length  into  v_key_length;
     close c_get_key_length;
     if v_key_length=32 or p_force_seq='Y' then
        v_seq:=
       'drop sequence if exists '||v_seq_name||';'||v_n||
         'create sequence '||v_seq_name||';'||v_n||
         'grant usage,update,select on '||v_seq_name||' to r_egisdspdata_dml;'||v_n||
         'alter table '||v_table_name||' alter column '||v_get_key.col_name||' set default nextval('''||v_seq_name||''');';
        v_seq:=v_seq||v_n||v_n;     
	  v_key_flag:=true;
    end if;  
 end if;
                 
  --拼接列定义
  for v_row in c_get_col loop 
      --如果存在32长度的唯一主键
      if v_key_flag and instr(upper(v_row.colName),v_get_key.col_name)>0 then
         v_row.colName:=v_get_key.col_name||' bigint,'; 
         v_key_flag:=false;
      end if;
      v_other_col:=v_other_col||v_row.colName||v_n;
  end loop;

  --如果存在主键
  if v_get_key.col_name is not null then
     v_other_col:=v_other_col||v_get_key.key_name||v_n;
  end if;
  --去掉最后的,
  v_other_col:=rtrim(v_other_col,','||v_n)||v_n||');'||v_n; 
  
  

  for v_row in c_get_comm(v_table_name) loop
      v_comm:=v_comm||v_row.comm||v_n;
  end loop;


  --拼接
  v_create_table_sql:=v_title||v_n
                    --||v_first_col||v_n
                    ||v_other_col||v_n
                    ||v_seq
                    ||v_comm||v_n
                    ||v_grant;
  --dbms_output.put_line(v_create_table_sql);
  return v_create_table_sql;
end;
/