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:
Show quoted text
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