From: | Lamar Owen <lamar(dot)owen(at)wgcr(dot)org> |
---|---|
To: | Gary M <postgres-general(at)mwwm(dot)net>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Retrieving database schema |
Date: | 2003-03-23 03:57:52 |
Message-ID: | 200303222257.52775.lamar.owen@wgcr.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Saturday 22 March 2003 22:33, Gary M wrote:
> From embedded SQL - how do I retrieve
> 1) a list of the tables in the datbase?
> 2) a list of the columns in each table?
>
>
> Perhaps #1 is this:
>
> SELECT tablename FROM pg_tables WHERE NOT schemaname = 'pg_catalog';
I use the following, although it uses pre-7.0 semantics and may or may not
work with 7.3
SELECT relname
FROM pg_class
WHERE relkind = 'r' and relname !~ '^Inv'
and relname !~ '^pg_'
ORDER BY relname
I don't remember the reason for the ^Inv regex.
> I'm at a loss for #2
I use the following query, which also gives the type of the column. $table is
the table in question.
SELECT a.attname, t.typname
FROM pg_class c, pg_attribute a, pg_type t
WHERE c.relname = lower($table)
and a.attnum > 0 and a.attrelid = c.oid
and a.atttypid = t.oid
ORDER BY attname
Again, using pre-7.x semantics, as the code that uses these queries might be
used on PostgreSQL as old as 6.4.2.
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2003-03-23 04:39:41 | Re: Retrieving database schema |
Previous Message | Gary M | 2003-03-23 03:33:29 | Retrieving database schema |