From: scott.marlowe <scott.marlowe@ihs.com>
To: Mark Nelson <MCN@cc.usu.edu>
Cc: PGSQL-PHP@postgresql.org
Subject: Re: getting table names
Date: Wed, 18 Dec 2002 15:54:55 -0700 (MST)
Message-ID: <Pine.LNX.4.33.0212181551590.4488-100000@css120.ihs.com> (raw)
In-Reply-To: <01KQ6KLMZ9J68YARR9@cc.usu.edu>
On Wed, 18 Dec 2002, Mark Nelson wrote:
> Hi,
>
> I can't seem to find how to do this anywhere. I'm trying to query
> the db and have it return the name of each of the tables in the
> db. Something like \dt does in psql, but from PHP. Any ideas?
Well, the quick and dirty trick is to run psql with the -E switch which
will rEveal the sql psql is using to do it's dirty work. In this case,
7.2 and 7.3 are pretty similar. For postgresql 7.2 it's:
SELECT c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN
'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
u.usename as "Owner"
FROM pg_class c LEFT JOIN pg_user u ON c.relowner = u.usesysid
WHERE c.relkind IN ('r','v','S','')
AND c.relname !~ '^pg_'
ORDER BY 1;
For 7.3 it's:
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN
'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
u.usename as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','S','')
AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
note that you can probably trim a fair bit off of these queries to get
just the table names.
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-php@postgresql.org
Cc: scott.marlowe@ihs.com, MCN@cc.usu.edu
Subject: Re: getting table names
In-Reply-To: <Pine.LNX.4.33.0212181551590.4488-100000@css120.ihs.com>
References: <01KQ6KLMZ9J68YARR9@cc.usu.edu> <Pine.LNX.4.33.0212181551590.4488-100000@css120.ihs.com>
* 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