Re: Need SQL of create TABLE including INDEX and SEQUENCE
От | Vibhor Kumar |
---|---|
Тема | Re: Need SQL of create TABLE including INDEX and SEQUENCE |
Дата | |
Msg-id | EB0C0C1C-C510-4BD2-9CA8-A68BAEBB45D7@enterprisedb.com обсуждение исходный текст |
Ответ на | Re: Need SQL of create TABLE including INDEX and SEQUENCE (Francisco Leovey <fleovey@yahoo.com>) |
Список | 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 asshown 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 wrapin 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 ENDAS 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.nspnameAS 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 contypeWHEN '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 contypeWHEN '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 contypeWHEN '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.kumar@enterprisedb.com Blog:http://vibhork.blogspot.com
В списке pgsql-novice по дате отправления: