pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Alessandro Ferrarin <ferrarin@tieffesistemi.com>
To: David C. Brown <dbrown@centennialwines.com>
Cc: pgsql-php@postgresql.org
Subject: Re: Retrieving a list of tables
Date: Wed, 17 Oct 2001 11:08:32 +0200
Message-ID: <20011017110832.A1052@tieffesistemi.com> (raw)
In-Reply-To: <3BCC93E9.E3B2F503@centennialwines.com>
References: <3BCC93E9.E3B2F503@centennialwines.com>

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
+-----------------------------------------------------------------------+



view thread (6+ messages)  latest in thread

Message-ID: <20011017110832.A1052@tieffesistemi.com>
Permalink:  ../20011017110832.A1052@tieffesistemi.com/
Also on:    postgresql.org/message-id/20011017110832.A1052@tieffesistemi.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-php@postgresql.org
  Cc: ferrarin@tieffesistemi.com, dbrown@centennialwines.com
  Subject: Re: Retrieving a list of tables
  In-Reply-To: <20011017110832.A1052@tieffesistemi.com>
  References: <3BCC93E9.E3B2F503@centennialwines.com> <20011017110832.A1052@tieffesistemi.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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