pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tod McQuillin <devin@spamcop.net>
To: magnus <reduct@asa.de>
Cc: pgsql-php@postgresql.org <pgsql-php@postgresql.org>
Subject: Re: listing databases
Date: Wed, 1 Nov 2000 10:10:38 -0600 (CST)
Message-ID: <Pine.GSO.4.21.0011011008290.6485-100000@sysadmin> (raw)
In-Reply-To: <Pine.GSO.4.21.0011011004460.6485-100000@sysadmin>

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@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





view thread (12+ messages)  latest in thread

Message-ID: <Pine.GSO.4.21.0011011008290.6485-100000@sysadmin>
Permalink:  ../Pine.GSO.4.21.0011011008290.6485-100000@sysadmin/
Also on:    postgresql.org/message-id/Pine.GSO.4.21.0011011008290.6485-100000@sysadmin

 · 

reply

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: devin@spamcop.net, reduct@asa.de
  Subject: Re: listing databases
  In-Reply-To: <Pine.GSO.4.21.0011011008290.6485-100000@sysadmin>
  References: <Pine.GSO.4.21.0011011004460.6485-100000@sysadmin> <Pine.GSO.4.21.0011011008290.6485-100000@sysadmin>

* 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