[development] Postgres equivalent to SHOW TABLES

[Nancy Wichmann]

Thanks, Daniel.

I added a DISTINCT as was suggested and it took care of that.

Nancy E. Wichmann, PMP
Injustice anywhere is a threat to justice everywhere. -- Dr. Martin L. King,
Jr.


-----Original Message-----
From: development-bounces at drupal.org
[mailto:development-bounces at drupal.org]On Behalf Of Daniel Vérité
Sent: Sunday, September 06, 2009 10:31 AM
To: development at drupal.org
Subject: Re: [development] Postgres equivalent to SHOW TABLES


	 Nancy Wichmann writes


>   $result = db_query("SELECT table_name FROM INFORMATION_SCHEMA.TABLES
WHERE
> table_name LIKE 'abc\_%'");

[...]

> Note that the list had MANY duplicates in it and did not come out in order
> of table name.

In mysql, INFORMATION_SCHEMA.TABLES has all the tables of all databases, not
just the database you're connected to.
This is probably why you get duplicates. To select only the tables of the
current database, which is what SHOW TABLES does, you could add this clause:

...AND table_schema=database()

In postgresql, the schema does not equate the database; it's a namespace
inside a database, so the clause above has to be different. Assuming the db
doesn't use multiple schemas, it could be:

... AND table_schema='public'

Best regards,
--
 Daniel Vérité
 PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org
No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 8.5.409 / Virus Database: 270.13.79/2348 - Release Date: 09/06/09
05:51:00