pg.ddx.io pgsql-php@postgresql.org mailing list archive
help / color / mirror / Atom feedRetrieving a list of tables
6+ messages / 5 participants
[nested] [flat]
* Retrieving a list of tables
@ 2001-10-16 20:09 David C. Brown <dbrown@centennialwines.com>
2001-10-17 09:08 ` Re: Retrieving a list of tables Alessandro Ferrarin <ferrarin@tieffesistemi.com>
2001-10-17 20:59 ` Re: Retrieving a list of tables erle@hindang.msuiit.edu.ph
0 siblings, 2 replies; 6+ messages in thread
From: David C. Brown @ 2001-10-16 20:09 UTC (permalink / raw)
To: pgsql-php
Heyas,
I need to be able to get a list of the tables from a postgresql from
php. I'm
porting a php-mysql website over to postgresql. I have a form that
populates
a <SELECT> statment with the tables in the db.
In Mysql I just used the sql query "SHOW TABLES;" postgresql doesn't
support that sql statment, and pg-php doesn't like me sending a "\d" as
a
query.
Anyone have any help, or ideas how I may be able to work around this?
or is there a query for pg-sql that I don't know about?
Any and all help would be great... Thanx
Dave
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Retrieving a list of tables
2001-10-16 20:09 Retrieving a list of tables David C. Brown <dbrown@centennialwines.com>
@ 2001-10-17 09:08 ` Alessandro Ferrarin <ferrarin@tieffesistemi.com>
1 sibling, 0 replies; 6+ messages in thread
From: Alessandro Ferrarin @ 2001-10-17 09:08 UTC (permalink / raw)
To: David C. Brown <dbrown@centennialwines.com>; +Cc: pgsql-php
On Tue, Oct 16, 2001 at 03:09:13PM -0500, David C. Brown wrote:
> Heyas,
>
> I need to be able to get a list of the tables from a postgresql from
> php. I'm
> porting a php-mysql website over to postgresql. I have a form that
> populates
> a <SELECT> statment with the tables in the db.
>
> In Mysql I just used the sql query "SHOW TABLES;" postgresql doesn't
> support that sql statment, and pg-php doesn't like me sending a "\d" as
> a
> query.
>
> Anyone have any help, or ideas how I may be able to work around this?
> or is there a query for pg-sql that I don't know about?
>
> Any and all help would be great... Thanx
>
> Dave
The right way to get that (with Owner and Encoding type), is querying:
SELECT pg_database.datname as "Database",
pg_user.usename as "Owner",
pg_encoding_to_char(pg_database.encoding) as "Encoding"FROM pg_database, pg_user
WHERE pg_database.datdba = pg_user.usesysid
UNION
SELECT pg_database.datname as "Database",
NULL as "Owner",
pg_encoding_to_char(pg_database.encoding) as "Encoding"FROM pg_database
WHERE pg_database.datdba NOT IN (SELECT usesysid FROM pg_user)
ORDER BY "Database";
Postgres uses some "internal" tables to store data. You can query them as
you want just for reading (I suppose).
If you start the terminal-based front-end psql like this
psql -E
you obtain: (from man page):
-E, --echo-hidden
Echoes the actual queries generated by \d and other backslash com-
mands. You can use this if you wish to include similar function-
ality into your own programs. This is equivalent to setting the
variable ECHO_HIDDEN from within psql.
Try it.
Bye - Alessandro
P.S. I encountered the problem you ask making a php-pgsql-browser...
--
+-----------------------------------------------------------------------+
Alessandro Ferrarin ferrarin@tieffesistemi.com
Tieffe Sistemi s.r.l. www.tieffesistemi.com
V.le Piceno 21 - 20129 Milano - Italia tel/fax +39 02 76115215
+-----------------------------------------------------------------------+
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Retrieving a list of tables
2001-10-16 20:09 Retrieving a list of tables David C. Brown <dbrown@centennialwines.com>
@ 2001-10-17 20:59 ` erle@hindang.msuiit.edu.ph
2001-10-18 10:43 ` Re: Retrieving a list of tables Papp Gyozo <pgerzson@freestart.hu>
1 sibling, 1 reply; 6+ messages in thread
From: erle@hindang.msuiit.edu.ph @ 2001-10-17 20:59 UTC (permalink / raw)
To: David C. Brown <dbrown@centennialwines.com>; +Cc: pgsql-php
On Tue, 16 Oct 2001, David C. Brown wrote:
> Heyas,
>
> I need to be able to get a list of the tables from a postgresql from
> php. I'm
> porting a php-mysql website over to postgresql. I have a form that
> populates
> a <SELECT> statment with the tables in the db.
>
> In Mysql I just used the sql query "SHOW TABLES;" postgresql doesn't
> support that sql statment, and pg-php doesn't like me sending a "\d" as
> a
> query.
>
> Anyone have any help, or ideas how I may be able to work around this?
> or is there a query for pg-sql that I don't know about?
>
> Any and all help would be great... Thanx
>
> Dave
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
Hey ,....
If you want to know the list of tables in a certain database,
you can query the pg_table table.
select * from pg_tables where tableowner = 'ME';
Its not really a table but a view, it contains all the tables in a
database where you connected including the system tables of postgres so be
sure to filter your query.
Let me know if this was what you need ....
Erle Czar S. Mantos
erle@hindang.msuiit.edu.ph
---------- MARTIANS LAIR ---------
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Retrieving a list of tables
2001-10-16 20:09 Retrieving a list of tables David C. Brown <dbrown@centennialwines.com>
2001-10-17 20:59 ` Re: Retrieving a list of tables erle@hindang.msuiit.edu.ph
@ 2001-10-18 10:43 ` Papp Gyozo <pgerzson@freestart.hu>
0 siblings, 0 replies; 6+ messages in thread
From: Papp Gyozo @ 2001-10-18 10:43 UTC (permalink / raw)
To: David C. Brown <dbrown@centennialwines.com>; +Cc: pgsql-php
If you runs psql with -E option, then psql reveals the SQL equivalent of its
magical metacommands starting with a backslash ie.: \dv \d and so on. Use
those queries !
<manual src="app-psql.html">
-E, --echo-hidden
Echoes the actual queries generated by \d and other backslash commands. You
can use this if you wish to include similar functionality into your own
programs. This is equivalent to setting the variable ECHO_HIDDEN from within
psql.
</manual>
----- Original Message -----
From: <erle@hindang.msuiit.edu.ph>
To: "David C. Brown" <dbrown@centennialwines.com>
Cc: <pgsql-php@postgresql.org>
Sent: Wednesday, October 17, 2001 10:59 PM
Subject: Re: [PHP] Retrieving a list of tables
> On Tue, 16 Oct 2001, David C. Brown wrote:
>
> > Heyas,
> >
> > I need to be able to get a list of the tables from a postgresql from
> > php. I'm
> > porting a php-mysql website over to postgresql. I have a form that
> > populates
> > a <SELECT> statment with the tables in the db.
> >
> > In Mysql I just used the sql query "SHOW TABLES;" postgresql doesn't
> > support that sql statment, and pg-php doesn't like me sending a "\d" as
> > a
> > query.
> >
> > Anyone have any help, or ideas how I may be able to work around this?
> > or is there a query for pg-sql that I don't know about?
> >
> > Any and all help would be great... Thanx
> >
> > Dave
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> >
>
> Hey ,....
>
>
> If you want to know the list of tables in a certain database,
> you can query the pg_table table.
>
> select * from pg_tables where tableowner = 'ME';
>
> Its not really a table but a view, it contains all the tables in a
> database where you connected including the system tables of postgres so be
> sure to filter your query.
>
> Let me know if this was what you need ....
>
>
> Erle Czar S. Mantos
> erle@hindang.msuiit.edu.ph
>
> ---------- MARTIANS LAIR ---------
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Retrieving a list of tables
@ 2001-10-17 08:58 Duncan Adams (DNS) <duncan.adams@vcontractor.co.za>
0 siblings, 0 replies; 6+ messages in thread
From: Duncan Adams (DNS) @ 2001-10-17 08:58 UTC (permalink / raw)
To: pgsql-php
select * from pg_tables where tablename not like 'pg_%'
-----Original Message-----
From: David C. Brown [mailto:dbrown@centennialwines.com]
Sent: Tuesday, October 16, 2001 10:09 PM
To: pgsql-php@postgresql.org
Subject: [PHP] Retrieving a list of tables
Heyas,
I need to be able to get a list of the tables from a postgresql from
php. I'm
porting a php-mysql website over to postgresql. I have a form that
populates
a <SELECT> statment with the tables in the db.
In Mysql I just used the sql query "SHOW TABLES;" postgresql doesn't
support that sql statment, and pg-php doesn't like me sending a "\d" as
a
query.
Anyone have any help, or ideas how I may be able to work around this?
or is there a query for pg-sql that I don't know about?
Any and all help would be great... Thanx
Dave
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Retrieving a list of tables
@ 2001-10-24 09:33 Duncan Adams (DNS) <duncan.adams@vcontractor.co.za>
0 siblings, 0 replies; 6+ messages in thread
From: Duncan Adams (DNS) @ 2001-10-24 09:33 UTC (permalink / raw)
To: pgsql-php
select * from pg_tables where name not like 'pg_%';
\dS gives you a list of system tables.
with psql -E
\dt gives 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"
-----Original Message-----
From: David C. Brown [mailto:dbrown@centennialwines.com]
Sent: Tuesday, October 16, 2001 10:09 PM
To: pgsql-php@postgresql.org
Subject: [PHP] Retrieving a list of tables
Heyas,
I need to be able to get a list of the tables from a postgresql from
php. I'm
porting a php-mysql website over to postgresql. I have a form that
populates
a <SELECT> statment with the tables in the db.
In Mysql I just used the sql query "SHOW TABLES;" postgresql doesn't
support that sql statment, and pg-php doesn't like me sending a "\d" as
a
query.
Anyone have any help, or ideas how I may be able to work around this?
or is there a query for pg-sql that I don't know about?
Any and all help would be great... Thanx
Dave
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2001-10-24 09:33 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-10-16 20:09 Retrieving a list of tables David C. Brown <dbrown@centennialwines.com>
2001-10-17 09:08 ` Alessandro Ferrarin <ferrarin@tieffesistemi.com>
2001-10-17 20:59 ` erle@hindang.msuiit.edu.ph
2001-10-18 10:43 ` Papp Gyozo <pgerzson@freestart.hu>
2001-10-17 08:58 Re: Retrieving a list of tables Duncan Adams (DNS) <duncan.adams@vcontractor.co.za>
2001-10-24 09:33 Re: Retrieving a list of tables Duncan Adams (DNS) <duncan.adams@vcontractor.co.za>
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