pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery to return schema/table/columname/columntype
5+ messages / 4 participants
[nested] [flat]
* Query to return schema/table/columname/columntype
@ 2007-01-19 12:41 paallen@attglobal.net
0 siblings, 3 replies; 5+ messages in thread
From: paallen@attglobal.net @ 2007-01-19 12:41 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Query to return schema/table/columname/columntype
@ 2007-01-19 14:23 paallen@attglobal.net
0 siblings, 0 replies; 5+ messages in thread
From: paallen@attglobal.net @ 2007-01-19 14:23 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Query to return schema/table/columname/columntype
@ 2007-01-19 15:45 Bruno Wolff III <bruno@wolff.to>
parent: paallen@attglobal.net
2 siblings, 0 replies; 5+ messages in thread
From: Bruno Wolff III @ 2007-01-19 15:45 UTC (permalink / raw)
To: paallen@attglobal.net; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Query to return schema/table/columname/columntype
@ 2007-01-19 16:28 chester c young <chestercyoung@yahoo.com>
parent: paallen@attglobal.net
2 siblings, 0 replies; 5+ messages in thread
From: chester c young @ 2007-01-19 16:28 UTC (permalink / raw)
To: paallen@attglobal.net; pgsql-sql
> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Query to return schema/table/columname/columntype
@ 2007-01-19 16:39 codeWarrior <gpatnude@hotmail.com>
parent: paallen@attglobal.net
2 siblings, 0 replies; 5+ messages in thread
From: codeWarrior @ 2007-01-19 16:39 UTC (permalink / raw)
To: pgsql-sql
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;
<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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-01-19 16:39 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-01-19 12:41 Query to return schema/table/columname/columntype paallen@attglobal.net
2007-01-19 15:45 ` Bruno Wolff III <bruno@wolff.to>
2007-01-19 16:28 ` chester c young <chestercyoung@yahoo.com>
2007-01-19 16:39 ` codeWarrior <gpatnude@hotmail.com>
2007-01-19 14:23 Re: Query to return schema/table/columname/columntype paallen@attglobal.net
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox