Обсуждение: Need SQL of create TABLE including INDEX and SEQUENCE

On Apr 11, 2011, at 7:55, Francisco Leovey wrote:

> Hello
>
> For a new project where I am running 2 versions of Postgres (8.4 and 9.0) simultaneously on an Ubuntu server:
> I need the syntax of an SQL statement in order to create tables and all index from one database to the other via a
utilityprogram I have in Perl. 
> I see PgAdmin shows on the lower pane all the SQL needed to create a table and all its index, but I do not know how
toobtain that info. 
> It would be optimal if I can get all the info in a single query (the way pgdump does).
> Also I need the SQL to create all the SEQUENCES.

Can you call pg_dump?

pg_dump -s -t your_table

Otherwise, I recommend taking a look at the pg_dump source code.

Michael Glaesemann
grzm seespotcode net

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