pg_get_tabledef

테이블 생성 쿼리 생성 함수

CREATE OR REPLACE Function pg_get_tabledef(text) RETURNS text  
AS  
$$  
  DECLARE  
     tabledef TEXT;  
     dotpos integer;  
     tablename text;  
     schemaname text;  
     prevcol text;  
     coltype text;  
     notnull1 boolean;  
     rec record;  
     oidcheck boolean;  
  BEGIN  
   dotpos:=strpos($1,'.');  
   if dotpos = 0 then  
     schemaname:='public';  
     tablename:=substr($1,dotpos+1);  
   else  
     schemaname:=substr($1,1,dotpos-1);  
     tablename:=substr($1,dotpos+1);  
  end if;  
  select relhasoids into oidcheck from pg_class,pg_namespace where pg_class.relnamespace=pg_namespace.oid and pg_namespace.nspname=schemaname and pg_class.relname=tablename and pg_class.relkind='r';  
   if not found then  
     tabledef:='Table Does not exists!';  
     return tabledef;  
   end if;  
  tabledef:= 'CREATE TABLE '|| schemaname||'.'||tablename;  
   for rec in SELECT a.attname as columnname ,pg_catalog.format_type(a.atttypid, a.atttypmod) as coltype, (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128) FROM pg_catalog.pg_attrdef d WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),a.attnotnull as notnull, a.attnum FROM pg_catalog.pg_attribute a WHERE a.attrelid = (select pg_class.oid from pg_class,pg_namespace where relname=tablename and pg_class.relnamespace=pg_namespace.oid and pg_namespace.nspname=schemaname) AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum    
  loop  
      if prevcol is null  then  
 tabledef:=tabledef||' (';  
        prevcol:=rec.columnname;  
        coltype:=rec.coltype;  
        notnull1:=rec.notnull;  
      elsif notnull1 then  
        tabledef:=tabledef||' '||E'\n'||prevcol||' '||coltype||' NOT NULL ,';  
        prevcol:=rec.columnname;  
        coltype:=rec.coltype;  
        notnull1:=rec.notnull;  
     else  
        tabledef:=tabledef||' '||E'\n'||prevcol||' '||coltype||' ,';  
        prevcol:=rec.columnname;  
        coltype:=rec.coltype;  
        notnull1:=rec.notnull;  
     end if;  
   end loop;  
      if oidcheck = true and notnull1 = true then  
        tabledef:=tabledef||E'\n'||prevcol||' '||coltype||' NOT NULL ) WITH OIDS;';  
      elsif oidcheck = true and notnull1 = false then  
        tabledef:=tabledef||E'\n'||prevcol||' '||coltype||' NOT NULL ) WITH OIDS;';  
      elsif oidcheck=false and notnull1=true then  
        tabledef:=tabledef||E'\n'|| prevcol||' '||coltype||' NOT NULL ) WITHOUT OIDS;';  
      else  
        tabledef:=tabledef||E'\n'||prevcol||' '||coltype||' ) WITHOUT OIDS;';  
      end if;  
     
   return tabledef;  
   end;  
$$ language plpgsql;