Thread: Query to return schema/table/columname/columntype
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
Hi all, I think I have fixed my own problem. At: http://developer.postgresql.org/~momjian/upgrade_tips_7.3 I found the answer which was: SELECT a.attrelid as oid, a.attname, t.typname FROM pg_attribute a inner join pg_type t on a.atttypid = t.oid WHERE a.attrelid = 'co.hole_test'::regclass AND a.attnum > 0ORDER BY a.attnum; thanks anyway! Phil 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
On Fri, Jan 19, 2007 at 12:41:19 +0000, paallen@attglobal.net wrote: > For background I am selecting table & schema by > the query: > SELECT schemaname || '.' || tablename AS tablename > FROM pg_tables ORDER BY tablename; Are you guaranteed that all of the names are lower case? If not you may want to include quoting in your query. I didn't have a good answer for the base question you asked; this was more under the suggestions category.
> 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. create view pg_cols as select s.nspname as schema_nm, r.relname as table_nm, a.attnum as column_sq, a.attname as column_nm, t.typname as datatype, a.atttypmod as datalen from pg_attribute a join pg_type t on( t.oid = a.atttypid ) join pg_class r on( r.oid = a.attrelid ) join pg_namespace s on( s.oid = r.relnamespace ) where a.attnum > 0 and r.relkind = 'r' and s.nspname = :schemaq; ____________________________________________________________________________________ Finding fabulous fares is fun. Let Yahoo! FareChase search your favorite travel sites to find flight and hotel bargains. http://farechase.yahoo.com/promo-generic-14795097
You mean like this: CREATE OR REPLACE VIEW sys_tabledef ASSELECT 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; <paallen@attglobal.net> 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 >