From: codeWarrior <gpatnude@hotmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Query to return schema/table/columname/columntype
Date: Fri, 19 Jan 2007 08:39:16 -0800
Message-ID: <eoqs0l$10i2$1@news.hub.org> (raw)
References: <20070119124622.0DE929FB2A3@postgresql.org>
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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gpatnude@hotmail.com
Subject: Re: Query to return schema/table/columname/columntype
In-Reply-To: <eoqs0l$10i2$1@news.hub.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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