From: | Tod McQuillin <devin(at)spamcop(dot)net> |
---|---|
To: | magnus <reduct(at)asa(dot)de> |
Cc: | "pgsql-php(at)postgresql(dot)org" <pgsql-php(at)postgresql(dot)org> |
Subject: | Re: listing databases |
Date: | 2000-11-01 16:10:38 |
Message-ID: | Pine.GSO.4.21.0011011008290.6485-100000@sysadmin |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-php |
On Wed, 1 Nov 2000, Tod McQuillin wrote:
> On Wed, 1 Nov 2000, magnus wrote:
>
> > Hello,
> > Can anyone tell me how to list (1.) databases and (2.) tables in a
> > database using php?
>
> To list databases, run
>
> psql -l
Sorry, I didn't notice that you wanted to do it from php. Even so, the
answer is almost the same.
If you run "psql -E -l" you get this:
devin(at)glass ~% psql -E -l
********* QUERY *********
SELECT pg_database.datname as "Database",
pg_user.usename as "Owner"FROM pg_database, pg_user
WHERE pg_database.datdba = pg_user.usesysid
UNION
SELECT pg_database.datname as "Database",
NULL as "Owner"FROM pg_database
WHERE pg_database.datdba NOT IN (SELECT usesysid FROM pg_user)
ORDER BY "Database"
*************************
That gives you the query that psql runs.
after you connect with psql -E, \dt tells you:
SELECT c.relname as "Name", 'table'::text as "Type", u.usename as "Owner"
FROM pg_class c, pg_user u
WHERE c.relowner = u.usesysid AND c.relkind = 'r'
AND not exists (select 1 from pg_views where viewname = c.relname)
AND c.relname !~ '^pg_'
UNION
SELECT c.relname as "Name", 'table'::text as "Type", NULL as "Owner"
FROM pg_class c
WHERE c.relkind = 'r'
AND not exists (select 1 from pg_views where viewname = c.relname)
AND not exists (select 1 from pg_user where usesysid = c.relowner)
AND c.relname !~ '^pg_'
ORDER BY "Name"
Those are the queries you should run from php.
--
Tod McQuillin
From | Date | Subject | |
---|---|---|---|
Next Message | Adam Lang | 2000-11-01 16:41:43 | Re: listing databases |
Previous Message | Tod McQuillin | 2000-11-01 16:05:32 | Re: listing databases |