Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 689099FB3E0 for ; Fri, 19 Jan 2007 12:35:44 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 16666-04 for ; Fri, 19 Jan 2007 12:35:38 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.116.145.2]) by postgresql.org (Postfix) with ESMTP id 899FF9FB3DF for ; Fri, 19 Jan 2007 12:35:37 -0400 (AST) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 110B830E18; Fri, 19 Jan 2007 17:35:35 +0100 (MET) From: "codeWarrior" X-Newsgroups: pgsql.sql Subject: Re: Query to return schema/table/columname/columntype Date: Fri, 19 Jan 2007 08:39:16 -0800 Organization: iDynaTECH, Inc. Lines: 65 Message-ID: References: <20070119124622.0DE929FB2A3@postgresql.org> Reply-To: "codeWarrior" X-Complaints-To: usenet@news.hub.org X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2900.3028 X-RFC2646: Format=Flowed; Original X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028 To: pgsql-sql@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.163 tagged_above=0 required=5 tests=BAYES_50, FORGED_HOTMAIL_RCVD2 X-Spam-Level: * X-Archive-Number: 200701/83 X-Sequence-Number: 27235 You mean like this: CREATE OR REPLACE VIEW sys_tabledef AS SELECT columns.table_catalog, columns.table_schema, columns.table_name, columns.column_name, columns.ordinal_position, columns.column_default, columns.is_nullable, columns.data_type, columns.character_maximum_length, columns.character_octet_length, columns.numeric_precision, columns.numeric_precision_radix, columns.numeric_scale, columns.datetime_precision, columns.interval_type, columns.interval_precision, columns.character_set_catalog, columns.character_set_schema, columns.character_set_name, columns.collation_catalog, columns.collation_schema, columns.collation_name, columns.domain_catalog, columns.domain_schema, columns.domain_name, columns.udt_catalog, columns.udt_schema, columns.udt_name, columns.scope_catalog, columns.scope_schema, columns.scope_name, columns.maximum_cardinality, columns.dtd_identifier, columns.is_self_referencing FROM information_schema.columns WHERE columns.table_schema::text = 'public'::text ORDER BY columns.table_name, columns.ordinal_position; wrote in message news:20070119124622.0DE929FB2A3@postgresql.org... > Hi all, > > I am trying to modify the dabo (a python wxpython > ide for database forms creation) code to allow the > selection of tables in any schema. I need a query > that will return records with schema, table, > columname and columne type. > > For background I am selecting table & schema by > the query: > SELECT schemaname || '.' || tablename AS tablename > FROM pg_tables ORDER BY tablename; > > Then I need to obtain the columns within that > table/schema. The original query was: > select c.oid,a.attname, t.typname > from pg_class c inner join pg_attribute a on > a.attrelid = c.oid inner join pg_type t on > a.atttypid = t.oid where c.relname = 'thetablename > and a.attnum > 0; > > Now my problem is how to create a new column to > replace c.relname so I can query on > "theschemaname.thetablename". > > Any suggestions will be welcomed. Thanks, > > Phil > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org >