Re: Retrieving a list of tables

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(at)tieffesistemi(dot)com
Tieffe Sistemi s.r.l. www.tieffesistemi.com
V.le Piceno 21 - 20129 Milano - Italia tel/fax +39 02 76115215
+-----------------------------------------------------------------------+