Received: from sysadmin.chi.wdr.com (sysadmin.chi.ubswarburg.com [146.180.1.66]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eA1GAul69935 for ; Wed, 1 Nov 2000 11:10:56 -0500 (EST) (envelope-from devin@sysadmin.chi.wdr.com) Received: from devin by sysadmin.chi.wdr.com with local (Exim 3.12 #2) id 13r0TH-0001hu-00; Wed, 01 Nov 2000 10:10:39 -0600 Date: Wed, 1 Nov 2000 10:10:38 -0600 (CST) From: Tod McQuillin X-Sender: devin@sysadmin To: magnus cc: "pgsql-php@postgresql.org" Subject: Re: listing databases In-Reply-To: Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200011/8 X-Sequence-Number: 16 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