pg.ddx.io pgsql-php@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: listing databases
12+ messages / 7 participants
[nested] [flat]
* Re: listing databases
@ 2000-11-01 14:21 Adam Lang <aalang@rutgersinsurance.com>
parent: magnus <reduct@asa.de>
4 siblings, 0 replies; 12+ messages in thread
From: Adam Lang @ 2000-11-01 14:21 UTC (permalink / raw)
To: pgsql-php
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@asa.de>
To: <pgsql-php@postgresql.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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: listing databases
@ 2000-11-01 14:46 Adam Lang <aalang@rutgersinsurance.com>
parent: Dan Wilson <dan_wilson@geocities.com>
0 siblings, 0 replies; 12+ messages in thread
From: Adam Lang @ 2000-11-01 14:46 UTC (permalink / raw)
To: pgsql-php
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@geocities.com>
To: "magnus" <reduct@asa.de>; <pgsql-php@postgresql.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@asa.de>
> To: <pgsql-php@postgresql.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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: listing databases
@ 2000-11-01 14:54 Louis Bertrand <louis@bertrandtech.on.ca>
parent: magnus <reduct@asa.de>
4 siblings, 0 replies; 12+ messages in thread
From: Louis Bertrand @ 2000-11-01 14:54 UTC (permalink / raw)
To: magnus <reduct@asa.de>; +Cc: pgsql-php
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@bertrandtech.on.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
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* listing databases
@ 2000-11-01 15:02 magnus <reduct@asa.de>
0 siblings, 5 replies; 12+ messages in thread
From: magnus @ 2000-11-01 15:02 UTC (permalink / raw)
To: pgsql-php
Hello,
Can anyone tell me how to list (1.) databases and (2.) tables in a
database using php?
Thanks,
Magnus Lawrie
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: listing databases
@ 2000-11-01 15:07 Dan Wilson <killroyboy@yahoo.com>
0 siblings, 1 reply; 12+ messages in thread
From: Dan Wilson @ 2000-11-01 15:07 UTC (permalink / raw)
To: Adam Lang <aalang@rutgersinsurance.com>; pgsql-php
I connect using the default database which is usually
template1. But you can access the system tables from
a connection into any database.
-Dan
--- Adam Lang <aalang@rutgersinsurance.com> wrote:
> 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@geocities.com>
> To: "magnus" <reduct@asa.de>;
> <pgsql-php@postgresql.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@asa.de>
> > To: <pgsql-php@postgresql.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
>
__________________________________________________
Do You Yahoo!?
From homework help to love advice, Yahoo! Experts has your answer.
http://experts.yahoo.com/
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: listing databases
@ 2000-11-01 15:38 Dan Wilson <dan_wilson@geocities.com>
parent: magnus <reduct@asa.de>
4 siblings, 1 reply; 12+ messages in thread
From: Dan Wilson @ 2000-11-01 15:38 UTC (permalink / raw)
To: magnus <reduct@asa.de>; pgsql-php
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@asa.de>
To: <pgsql-php@postgresql.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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: listing databases
@ 2000-11-01 16:05 Tod McQuillin <devin@spamcop.net>
parent: magnus <reduct@asa.de>
4 siblings, 2 replies; 12+ messages in thread
From: Tod McQuillin @ 2000-11-01 16:05 UTC (permalink / raw)
To: magnus <reduct@asa.de>; +Cc: pgsql-php
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: listing databases
@ 2000-11-01 16:10 Tod McQuillin <devin@spamcop.net>
parent: Tod McQuillin <devin@spamcop.net>
1 sibling, 1 reply; 12+ messages in thread
From: Tod McQuillin @ 2000-11-01 16:10 UTC (permalink / raw)
To: magnus <reduct@asa.de>; +Cc: 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@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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: listing databases
@ 2000-11-01 16:41 Adam Lang <aalang@rutgersinsurance.com>
parent: Tod McQuillin <devin@spamcop.net>
1 sibling, 0 replies; 12+ messages in thread
From: Adam Lang @ 2000-11-01 16:41 UTC (permalink / raw)
To: ; +Cc: pgsql-php
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@spamcop.net>
To: "magnus" <reduct@asa.de>
Cc: <pgsql-php@postgresql.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
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: listing databases
@ 2000-11-01 16:42 Adam Lang <aalang@rutgersinsurance.com>
parent: Dan Wilson <killroyboy@yahoo.com>
0 siblings, 0 replies; 12+ messages in thread
From: Adam Lang @ 2000-11-01 16:42 UTC (permalink / raw)
To: pgsql-php
Ah, gotcha. That's good to know.
Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
----- Original Message -----
From: "Dan Wilson" <killroyboy@yahoo.com>
To: "Adam Lang" <aalang@rutgersinsurance.com>; <pgsql-php@postgresql.org>
Sent: Wednesday, November 01, 2000 10:07 AM
Subject: Re: [PHP] listing databases
> I connect using the default database which is usually
> template1. But you can access the system tables from
> a connection into any database.
>
> -Dan
>
> --- Adam Lang <aalang@rutgersinsurance.com> wrote:
> > 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@geocities.com>
> > To: "magnus" <reduct@asa.de>;
> > <pgsql-php@postgresql.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@asa.de>
> > > To: <pgsql-php@postgresql.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
> >
>
>
> __________________________________________________
> Do You Yahoo!?
> >From homework help to love advice, Yahoo! Experts has your answer.
> http://experts.yahoo.com/
^ permalink raw reply [nested|flat] 12+ messages in thread
* RE: listing databases
@ 2000-11-01 17:50 Tony Dunn <atdunn@bellsouth.net>
parent: magnus <reduct@asa.de>
4 siblings, 0 replies; 12+ messages in thread
From: Tony Dunn @ 2000-11-01 17:50 UTC (permalink / raw)
To: pgsql-php
Checkout http://www.phpwizard.net/projects/phpPgAdmin/
-----Original Message-----
From: pgsql-php-owner@postgresql.org
[mailto:pgsql-php-owner@postgresql.org]On Behalf Of magnus
Sent: Wednesday, November 01, 2000 10:03 AM
To: pgsql-php@postgresql.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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: listing databases
@ 2000-11-01 21:06 magnus <reduct@asa.de>
parent: Tod McQuillin <devin@spamcop.net>
0 siblings, 0 replies; 12+ messages in thread
From: magnus @ 2000-11-01 21:06 UTC (permalink / raw)
To: Tod McQuillin <devin@spamcop.net>; +Cc: pgsql-php
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@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"
> *************************
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2000-11-01 21:06 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-11-01 15:02 listing databases magnus <reduct@asa.de>
2000-11-01 14:21 ` Adam Lang <aalang@rutgersinsurance.com>
2000-11-01 14:54 ` Louis Bertrand <louis@bertrandtech.on.ca>
2000-11-01 15:38 ` Dan Wilson <dan_wilson@geocities.com>
2000-11-01 14:46 ` Adam Lang <aalang@rutgersinsurance.com>
2000-11-01 16:05 ` Tod McQuillin <devin@spamcop.net>
2000-11-01 16:10 ` Tod McQuillin <devin@spamcop.net>
2000-11-01 21:06 ` magnus <reduct@asa.de>
2000-11-01 16:41 ` Adam Lang <aalang@rutgersinsurance.com>
2000-11-01 17:50 ` Tony Dunn <atdunn@bellsouth.net>
2000-11-01 15:07 Re: listing databases Dan Wilson <killroyboy@yahoo.com>
2000-11-01 16:42 ` Adam Lang <aalang@rutgersinsurance.com>
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