From: paallen@attglobal.net
To: pgsql-sql@postgresql.org,
Subject: Re: Query to return schema/table/columname/columntype
Date: Fri, 19 Jan 2007 14:23:32 +0000
MIME-Version: 1.0
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Message-Id: <20070119142333.73A389FB2F8@postgresql.org>

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 > 0
	ORDER 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