Skip site navigation (1) Skip section navigation (2)

Re: Need SQL of create TABLE including INDEX and SEQUENCE

From: Vibhor Kumar <vibhor(dot)kumar(at)enterprisedb(dot)com>
To: Francisco Leovey <fleovey(at)yahoo(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Need SQL of create TABLE including INDEX and SEQUENCE
Date: 2011-04-11 20:55:23
Message-ID: EB0C0C1C-C510-4BD2-9CA8-A68BAEBB45D7@enterprisedb.com (view raw or flat)
Thread:
Lists: pgsql-novice
On Apr 11, 2011, at 11:54 PM, Francisco Leovey wrote:

> I am not sure I will understand the code inside PG_DUMP
>  
> I could call PG_DUMP and store the info on a text file and then read it into my program and then issue an SQL command
> But I was hoping to get the info directly from the Postgres tables using some fancy query.
>  
> I am using some queries that give me a list of tables and a list of fields in a table but now I need the actual SQL as shown on PgAdmin
>  
> Maybe somebody knows the way PgAdmin generates it

pgAdmin runs multiple commands to get the definition of Table. Some of those commands are given below, which you can wrap in function to give you the definition of table:

SELECT att.*, def.*, pg_catalog.pg_get_expr(def.adbin, def.adrelid) AS defval, CASE WHEN att.attndims > 0 THEN 1 ELSE 0 END AS isarray, format_type(ty.oid,NULL) AS typname, format_type(ty.oid,att.atttypmod) AS displaytypname, tn.nspname as typnspname, et.typname as elemtypname,
	  ty.typstorage AS defaultstorage, cl.relname, na.nspname, att.attstattarget, description, cs.relname AS sername, ns.nspname AS serschema,
	  (SELECT count(1) FROM pg_type t2 WHERE t2.typname=ty.typname) > 1 AS isdup, indkey,
	  CASE 
	       WHEN inh.inhparent IS NOT NULL AND att.attinhcount>0
	            THEN inh.inhparent::regclass
	       ELSE NULL
	  END AS inhrelname,
	  attoptions,
	  EXISTS(SELECT 1 FROM  pg_constraint WHERE conrelid=att.attrelid AND contype='f' AND att.attnum=ANY(conkey)) As isfk
	  FROM pg_attribute att
	  JOIN pg_type ty ON ty.oid=atttypid
	  JOIN pg_namespace tn ON tn.oid=ty.typnamespace
	  JOIN pg_class cl ON cl.oid=att.attrelid
	  JOIN pg_namespace na ON na.oid=cl.relnamespace
	  LEFT OUTER JOIN pg_inherits inh ON inh.inhrelid=att.attrelid
	  LEFT OUTER JOIN pg_type et ON et.oid=ty.typelem
	  LEFT OUTER JOIN pg_attrdef def ON adrelid=att.attrelid AND adnum=att.attnum
	  LEFT OUTER JOIN pg_description des ON des.objoid=att.attrelid AND des.objsubid=att.attnum
	  LEFT OUTER JOIN (pg_depend JOIN pg_class cs ON objid=cs.oid AND cs.relkind='S') ON refobjid=att.attrelid AND refobjsubid=att.attnum
	  LEFT OUTER JOIN pg_namespace ns ON ns.oid=cs.relnamespace
	  LEFT OUTER JOIN pg_index pi ON pi.indrelid=att.attrelid AND indisprimary
	 WHERE att.attrelid = 53395::oid
	   AND att.attnum > 0
	   AND att.attisdropped IS FALSE
	 ORDER BY att.attnum;
SELECT DISTINCT ON(cls.relname) cls.oid, cls.relname as idxname, indrelid, indkey, indisclustered, indisunique, indisprimary, n.nspname,
	       indnatts, cls.reltablespace AS spcoid, spcname, tab.relname as tabname, indclass, con.oid AS conoid, CASE contype WHEN 'p' THEN desp.description WHEN 'u' THEN desp.description ELSE des.description END AS description,
	       pg_get_expr(indpred, indrelid, true) as indconstraint, contype, condeferrable, condeferred, amname
	, substring(array_to_string(cls.reloptions, ',') from 'fillfactor=([0-9]*)') AS fillfactor 
	  FROM pg_index idx
	  JOIN pg_class cls ON cls.oid=indexrelid
	  JOIN pg_class tab ON tab.oid=indrelid
	  LEFT OUTER JOIN pg_tablespace ta on ta.oid=cls.reltablespace
	  JOIN pg_namespace n ON n.oid=tab.relnamespace
	  JOIN pg_am am ON am.oid=cls.relam
	  LEFT JOIN pg_depend dep ON (dep.classid = cls.tableoid AND dep.objid = cls.oid AND dep.refobjsubid = '0')
	  LEFT OUTER JOIN pg_constraint con ON (con.tableoid = dep.refclassid AND con.oid = dep.refobjid)
	  LEFT OUTER JOIN pg_description des ON des.objoid=cls.oid
	  LEFT OUTER JOIN pg_description desp ON (desp.objoid=con.oid AND desp.objsubid = 0)
	 WHERE indrelid = 53395::oid   AND contype='p'
	
	 ORDER BY cls.relname;
SELECT ct.oid, conname, condeferrable, condeferred, confupdtype, confdeltype, confmatchtype, conkey, confkey, confrelid, nl.nspname as fknsp, cl.relname as fktab, nr.nspname as refnsp, cr.relname as reftab, description
	  FROM pg_constraint ct
	  JOIN pg_class cl ON cl.oid=conrelid
	  JOIN pg_namespace nl ON nl.oid=cl.relnamespace
	  JOIN pg_class cr ON cr.oid=confrelid
	  JOIN pg_namespace nr ON nr.oid=cr.relnamespace
	  LEFT OUTER JOIN pg_description des ON des.objoid=ct.oid
	 WHERE contype='f' AND conrelid = 53395::oid
	 ORDER BY conname;

SELECT DISTINCT ON(cls.relname) cls.oid, cls.relname as idxname, indrelid, indkey, indisclustered, indisunique, indisprimary, n.nspname,
	       indnatts, cls.reltablespace AS spcoid, spcname, tab.relname as tabname, indclass, con.oid AS conoid, CASE contype WHEN 'p' THEN desp.description WHEN 'u' THEN desp.description ELSE des.description END AS description,
	       pg_get_expr(indpred, indrelid, true) as indconstraint, contype, condeferrable, condeferred, amname
	, substring(array_to_string(cls.reloptions, ',') from 'fillfactor=([0-9]*)') AS fillfactor 
	  FROM pg_index idx
	  JOIN pg_class cls ON cls.oid=indexrelid
	  JOIN pg_class tab ON tab.oid=indrelid
	  LEFT OUTER JOIN pg_tablespace ta on ta.oid=cls.reltablespace
	  JOIN pg_namespace n ON n.oid=tab.relnamespace
	  JOIN pg_am am ON am.oid=cls.relam
	  LEFT JOIN pg_depend dep ON (dep.classid = cls.tableoid AND dep.objid = cls.oid AND dep.refobjsubid = '0')
	  LEFT OUTER JOIN pg_constraint con ON (con.tableoid = dep.refclassid AND con.oid = dep.refobjid)
	  LEFT OUTER JOIN pg_description des ON des.objoid=cls.oid
	  LEFT OUTER JOIN pg_description desp ON (desp.objoid=con.oid AND desp.objsubid = 0)
	 WHERE indrelid = 53395::oid   AND contype='u'
	
	 ORDER BY cls.relname;
SELECT c.oid, conname, relname, nspname, description,
	       pg_get_expr(conbin, conrelid, true) as consrc
	  FROM pg_constraint c
	  JOIN pg_class cl ON cl.oid=conrelid
	  JOIN pg_namespace nl ON nl.oid=relnamespace
	  LEFT OUTER JOIN pg_description des ON des.objoid=c.oid
	 WHERE contype = 'c' AND conrelid =  53395::oid
	 ORDER BY conname;
SELECT DISTINCT ON(cls.relname) cls.oid, cls.relname as idxname, indrelid, indkey, indisclustered, indisunique, indisprimary, n.nspname,
	       indnatts, cls.reltablespace AS spcoid, spcname, tab.relname as tabname, indclass, con.oid AS conoid, CASE contype WHEN 'p' THEN desp.description WHEN 'u' THEN desp.description ELSE des.description END AS description,
	       pg_get_expr(indpred, indrelid, true) as indconstraint, contype, condeferrable, condeferred, amname
	, substring(array_to_string(cls.reloptions, ',') from 'fillfactor=([0-9]*)') AS fillfactor 
	  FROM pg_index idx
	  JOIN pg_class cls ON cls.oid=indexrelid
	  JOIN pg_class tab ON tab.oid=indrelid
	  LEFT OUTER JOIN pg_tablespace ta on ta.oid=cls.reltablespace
	  JOIN pg_namespace n ON n.oid=tab.relnamespace
	  JOIN pg_am am ON am.oid=cls.relam
	  LEFT JOIN pg_depend dep ON (dep.classid = cls.tableoid AND dep.objid = cls.oid AND dep.refobjsubid = '0')
	  LEFT OUTER JOIN pg_constraint con ON (con.tableoid = dep.refclassid AND con.oid = dep.refobjid)
	  LEFT OUTER JOIN pg_description des ON des.objoid=cls.oid
	  LEFT OUTER JOIN pg_description desp ON (desp.objoid=con.oid AND desp.objsubid = 0)
	 WHERE indrelid = 53395::oid
	   AND conname IS NULL
	 ORDER BY cls.relname;
SELECT rw.oid, rw.*, relname, CASE WHEN relkind = 'r' THEN TRUE ELSE FALSE END AS parentistable, nspname, description,
	       pg_get_ruledef(rw.oid, true) AS definition
	  FROM pg_rewrite rw
	  JOIN pg_class cl ON cl.oid=rw.ev_class
	  JOIN pg_namespace nsp ON nsp.oid=cl.relnamespace
	  LEFT OUTER JOIN pg_description des ON des.objoid=rw.oid
	 WHERE ev_class = 53395
	 ORDER BY rw.rulename;
SELECT t.oid, t.xmin, t.*, relname, nspname, des.description, l.lanname, p.prosrc, 
	  trim(substring(pg_get_triggerdef(t.oid), 'WHEN (.*) EXECUTE PROCEDURE'), '()') AS whenclause
	  FROM pg_trigger t
	  JOIN pg_class cl ON cl.oid=tgrelid
	  JOIN pg_namespace na ON na.oid=relnamespace
	  LEFT OUTER JOIN pg_description des ON des.objoid=t.oid
	  LEFT OUTER JOIN pg_proc p ON p.oid=t.tgfoid
	  LEFT OUTER JOIN pg_language l ON l.oid=p.prolang
	 WHERE NOT tgisinternal
	 AND tgrelid = 53395::oid
	 ORDER BY tgname;


Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
vibhor(dot)kumar(at)enterprisedb(dot)com
Blog:http://vibhork.blogspot.com


In response to

pgsql-novice by date

Next:From: Christian BDate: 2011-04-12 07:12:57
Subject: Postgres extension for recurring Events
Previous:From: Vibhor KumarDate: 2011-04-11 20:42:37
Subject: Re: 'Range partitioning in PGSQL'

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group