Thread: Retrieving database schema

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'm at a loss for #2
The following doesn't seem very useful:

 SELECT * FROM pg_class WHERE relname = 'tablename';

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

Lamar Owen <lamar.owen@wgcr.org> writes:
> 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 believe that's a hangover from back when each large object was its own
relation.  [digs in archives...]  OK, that's dead code since 7.1, and it
was wrong before that ... the special relation names were xin* ...

In 7.3, suppressing relnames starting with 'pg_' isn't really quite the
kosher way to hide system tables, either.  It works but it might
suppress legitimate user tables --- there's not a prohibition against
user tables named 'pg_*' anymore.  The best way moving forward will be
to ignore tables that are in system schemas.

            regards, tom lane

Thanks, the syntax works fine on 7.3.2.

Note that "lower($table)" may not be desirable as you CAN create
mixed-case table and column names, for example...

 CREATE TABLE "MixedCase" ( "MixedCase" serial );

On Sat, 22 Mar 2003, Lamar Owen wrote:

> 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

On Saturday 22 March 2003 23:43, Gary M wrote:
> Thanks, the syntax works fine on 7.3.2.

> Note that "lower($table)" may not be desirable as you CAN create
> mixed-case table and column names, for example...

Like I said, it's a fairly old library query I use in the nspostgres AOLserver
database driver.  It hasn't been updated in ages -- in fact, it's not been
changed since PostgreSQL 6.2.1 days.  Methinks I need to do some PM on the
bit-rot, no? :-)

But how to move forward, without breaking backwards compatibility... will
require some thought.  I can't force the use of PostgreSQL 7.3.x; but I can
check for it at compile time.  (There are multiple OpenACS installations that
use this driver with 7.1.x and 7.2.x backends.)
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11