Re: Retrieving database schema

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