Results 1 to 5 of 5

Thread: Query schema

  1. #1
    Join Date
    Apr 2011
    Posts
    10

    Unanswered: Query schema

    Hi,

    One of my application requirements is to create a view to display:

    table_id, tablename, column,col type,size and description.

    how do i do that?

    thank you,
    OcaVid

  2. #2
    Join Date
    Apr 2011
    Posts
    10
    Quote Originally Posted by ocavid View Post
    Hi,

    One of my application requirements is to create a view to display:

    table_id, tablename, column,col type,size and description.

    how do i do that?

    thank you,
    OcaVid
    Sorry for this one... this should be in Postgres.

  3. #3
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Take a look at the information_schema.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  4. #4
    Join Date
    Apr 2011
    Posts
    10

    This is the best i could, it is working

    CREATE OR REPLACE VIEW x_field_descriptions AS
    SELECT pg_description.objoid AS tableid, pg_description.objsubid AS seq, pg_description.description
    FROM pg_description;

    CREATE OR REPLACE VIEW x_fields AS
    SELECT pg_attribute.attrelid AS tableid, pg_attribute.attname AS fieldname, pg_attribute.attnum AS seq
    FROM pg_attribute;

    CREATE OR REPLACE VIEW x_table_fields AS
    SELECT x_fields.tableid, x_tables.tablename, x_fields.fieldname, x_fields.seq
    FROM x_tables x_tables
    JOIN x_fields x_fields ON x_fields.tableid = x_tables.tableid
    WHERE x_fields.fieldname !~~ '%...%'::text AND x_fields.seq > 0;

    CREATE OR REPLACE VIEW x_tables AS
    SELECT c.oid AS tableid, c.relname AS tablename, n.nspname AS schemaname
    FROM pg_class c
    LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
    LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
    WHERE n.nspname = 'public'::name AND c.relkind = 'r'::"char";

    CREATE OR REPLACE VIEW x_table_field_descriptions AS
    SELECT x_table_fields.tableid, x_table_fields.tablename, x_table_fields.fieldname, x_table_fields.seq, x_field_descriptions.description
    FROM x_table_fields x_table_fields
    JOIN x_field_descriptions x_field_descriptions ON x_field_descriptions.tableid = x_table_fields.tableid AND x_field_descriptions.seq = x_table_fields.seq
    ORDER BY x_table_fields.tablename, x_table_fields.seq;

    x_table_field_descriptions this where the information i needed.

    any one might have a better way than this i am sure there is, but i don't know.

  5. #5
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    You NEED the oid?

    If not, Information_schema.columns has everything that else... And, it's portable to many other database types...
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •