Re: listing databases

I don't know of a way. To connect to postgresql I think it is mandatory to
include the dbname. Even if you didn't, there is no function that lists the
DBs on the system.

Unfortunately that is something MySQL in PHP has. You can connect to jsut
the Server, and it has a function called mysql_list_dbs which returns a
result set of databases on the server.

Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
----- Original Message -----
From: "magnus" <reduct(at)asa(dot)de>
To: <pgsql-php(at)postgresql(dot)org>
Sent: Wednesday, November 01, 2000 10:02 AM
Subject: [PHP] listing databases

> Hello,
> Can anyone tell me how to list (1.) databases and (2.) tables in a
> database using php?
> Thanks,
> Magnus Lawrie

So you just connect to the database using username, password, host, and
port? Don't use a dbname in the connection string?

Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
----- Original Message -----
From: "Dan Wilson" <dan_wilson(at)geocities(dot)com>
To: "magnus" <reduct(at)asa(dot)de>; <pgsql-php(at)postgresql(dot)org>
Sent: Wednesday, November 01, 2000 10:38 AM
Subject: Re: [PHP] listing databases

> Databases:
>
> SELECT datname FROM pg_databases
>
> Tables:
>
> SELECT tablename FROM pg_tables WHERE tablename NOT LIKE 'pg%'
>
> The "NOT LIKE 'pg%'" filters out the system tables.
>
> If you are interested in learning more about the system tables, you can
> download my phpPgAdmin (http://www.phpwizard.net/projects/phpPgAdmin) and
> view the source code. I have to use the system tables for almost
> everything. If you do not want to download it, you can check out the
source
> code through the snapshot page (http://pgdemo.acucore.com/snapshot)
>
> -Dan Wilson
>
>
> ----- Original Message -----
> From: "magnus" <reduct(at)asa(dot)de>
> To: <pgsql-php(at)postgresql(dot)org>
> Sent: Wednesday, November 01, 2000 7:02 AM
> Subject: [PHP] listing databases
>
>
> > Hello,
> > Can anyone tell me how to list (1.) databases and (2.) tables in a
> > database using php?
> > Thanks,
> > Magnus Lawrie

Just a quick answer without doing much research.

Have a look at the system tables. There is a switch in psql (can't
remember which one) to monitor the actual sql sent to the back-end.

Start with \dS to list the system tables.

Try SELECT * FROM pg_database; to get the databases

I think template1 always exists, so you can try connecting to that if
you're not sure where to start.

I'm not sure how to get the tables, but I'm sure it's the same process.

Ciao
--Louis <louis(at)bertrandtech(dot)on(dot)ca>

Louis Bertrand http://www.bertrandtech.on.ca/
Bertrand Technical Services, Bowmanville, ON, Canada
Tel: +1.905.623.1500 Fax: +1.905.623.3852

OpenBSD: Secure by default. http://www.openbsd.org/

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?
> Thanks,
> Magnus Lawrie
>

Hello,
Can anyone tell me how to list (1.) databases and (2.) tables in a
database using php?
Thanks,
Magnus Lawrie

Databases:

SELECT datname FROM pg_databases

Tables:

SELECT tablename FROM pg_tables WHERE tablename NOT LIKE 'pg%'

The "NOT LIKE 'pg%'" filters out the system tables.

If you are interested in learning more about the system tables, you can
download my phpPgAdmin (http://www.phpwizard.net/projects/phpPgAdmin) and
view the source code. I have to use the system tables for almost
everything. If you do not want to download it, you can check out the source
code through the snapshot page (http://pgdemo.acucore.com/snapshot)

-Dan Wilson

----- Original Message -----
From: "magnus" <reduct(at)asa(dot)de>
To: <pgsql-php(at)postgresql(dot)org>
Sent: Wednesday, November 01, 2000 7:02 AM
Subject: [PHP] listing databases

> Hello,
> Can anyone tell me how to list (1.) databases and (2.) tables in a
> database using php?
> Thanks,
> Magnus Lawrie

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

from your unix shell prompt.

Once connected, enter \dt to list the tables.
--
Tod McQuillin

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

How would you use that with PHP though? Can you get the info back as
something you can parse or interpret?

Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
----- Original Message -----
From: "Tod McQuillin" <devin(at)spamcop(dot)net>
To: "magnus" <reduct(at)asa(dot)de>
Cc: <pgsql-php(at)postgresql(dot)org>
Sent: Wednesday, November 01, 2000 11:05 AM
Subject: Re: [PHP] listing databases

> 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
>
> from your unix shell prompt.
>
> Once connected, enter \dt to list the tables.
> --
> Tod McQuillin
>

Checkout http://www.phpwizard.net/projects/phpPgAdmin/

-----Original Message-----
From: pgsql-php-owner(at)postgresql(dot)org
[mailto:pgsql-php-owner(at)postgresql(dot)org]On Behalf Of magnus
Sent: Wednesday, November 01, 2000 10:03 AM
To: pgsql-php(at)postgresql(dot)org
Subject: [PHP] listing databases

Hello,
Can anyone tell me how to list (1.) databases and (2.) tables in a
database using php?
Thanks,
Magnus Lawrie

Thankyou, that seems to work fine, although I found I had to uncomment
everything inside my sql query quotes:

$sql= "SELECT pg_database.datname as Database,
pg_user.usename as Owner FROM pg_database, pg_user
WHERE pg_database.datdba = pg_user.usesysid";

Magnus Lawrie

Tod McQuillin wrote:

> 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"
> *************************