Till Advanced Server 8.3, there was a function pg_get_tabledef, which some people were using to get the definition of table.

This function is no more part of new versions of Advanced Server 8.3 (8.3R2AS and 8.4AS). So, they would be missing this function by default.

However, they can get the same functionality using following function, which provides similar result as of pg_get_tabledef.

Function code is given below:

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;     

Sample Output is given below:

postgres=# select pg_get_tabledef('public.test_def');                                                                                                                            pg_get_tabledef           
---------------------------------  
 CREATE TABLE public.test_def ( +  
 id numeric(9,0) NOT NULL ,     +  
 a text ) WITHOUT OIDS;  
(1 row)  
  
postgres=# select pg_get_tabledef('public.test_des');  
    pg_get_tabledef       
------------------------  
 Table Does not exists!  
(1 row)  

Note:: Function would not give the information of any constraint defined on table and index definitions. However, if some body wants then user can keep modify this to get the result.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s