pg_get_tabledef function in Postgres Plus

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.