Postgres equivalent to SHOW TABLES
Does Postgres have an equivalent to MySql's SHOW TABLES? In particular "SHOW TABLES LIKE 'abc\_%' Nancy E. Wichmann, PMP Injustice anywhere is a threat to justice everywhere. -- Dr. Martin L. King, Jr.
On Fri, Sep 04, 2009 at 12:36:32PM -0400, Nancy Wichmann wrote:
Does Postgres have an equivalent to MySql's SHOW TABLES? In particular "SHOW TABLES LIKE 'abc\_%'
\dt abc_* -- Jan Dittberner - Debian Developer GPG-key: 4096R/558FB8DD 2009-05-10 B2FF 1D95 CE8F 7A22 DF4C F09B A73E 0055 558F B8DD http://www.dittberner.info/
Quoting Nancy Wichmann <nan_wich@bellsouth.net>:
Does Postgres have an equivalent to MySql's SHOW TABLES? In particular "SHOW TABLES LIKE 'abc\_%'
Nancy E. Wichmann, PMP Injustice anywhere is a threat to justice everywhere. -- Dr. Martin L. King, Jr.
I found http://www.tech-recipes.com/rx/279/display-or-show-tables-in-a-postgresqlpgs... HTH, -- Earnie -- http://r-feed.com/ -- http://for-my-kids.com/ -- http://www.4offer.biz/ -- http://give-me-an-offer.com/
Earnie Boyd wrote:
I found http://www.tech-recipes.com/rx/279/....
Thanks, but this has to be in the module. Nancy E. Wichmann, PMP Injustice anywhere is a threat to justice everywhere. -- Dr. Martin L. King, Jr.
Hello, We did that in the Copix project : http://svn.copix.org/browser/trunk/utils/copix/db/drivers/pdo_pgsql/CopixDbC... Brice 2009/9/4 Nancy Wichmann <nan_wich@bellsouth.net>:
Does Postgres have an equivalent to MySql's SHOW TABLES? In particular "SHOW TABLES LIKE 'abc\_%'
Nancy E. Wichmann, PMP Injustice anywhere is a threat to justice everywhere. -- Dr. Martin L. King, Jr.
Brice wrote:
We did that in the Copix project... Ah, that's pretty straight-forward. Does pg_tables need to be prefixed (i.e. {pg_tables}) in Drupal?
Nancy E. Wichmann, PMP Injustice anywhere is a threat to justice everywhere. -- Dr. Martin L. King, Jr.
On Fri, 4 Sep 2009 12:36:32 -0400 "Nancy Wichmann" <nan_wich@bellsouth.net> wrote:
Does Postgres have an equivalent to MySql's SHOW TABLES? In particular "SHOW TABLES LIKE 'abc\_%'
Nancy E. Wichmann, PMP Injustice anywhere is a threat to justice everywhere. -- Dr. Martin L. King, Jr.
For future reference... since you may need the output in a record set: - start psql with -E param and then use \d to see how pg actually retrieve those info - consider to consult information_schema. schema. I think it should be part of some SQL standard so it may even work on MySQL recent versions -- Ivan Sergio Borgonovo http://www.webthatworks.it
Ivan Sergio Borgonovo wrote:
start psql with -E param ...
This is in a module, so I cannot demand that the user start it any way other than how it would be started for Drupal. Nancy E. Wichmann, PMP Injustice anywhere is a threat to justice everywhere. -- Dr. Martin L. King, Jr.
On Fri, 4 Sep 2009 15:56:41 -0400 "Nancy Wichmann" <nan_wich@bellsouth.net> wrote:
Ivan Sergio Borgonovo wrote:
start psql with -E param ...
This is in a module, so I cannot demand that the user start it any way other than how it would be started for Drupal.
That's just to learn how to get the information with SQL. So you connect to postgresql through psql -E use \d stuff get the SQL used by psql to get the info and copy and paste it in your drupal module. Actually this is not going to be portable. A better way would be to learn how to query information_schema that *should* be portable. information_schema should be in the sql-92 standard. http://dev.mysql.com/doc/refman/5.0/en/information-schema.html http://www.postgresql.org/docs/8.1/interactive/information-schema.html http://www.ocelot.ca/is.htm I think that in postgresql information_schema is just a view on the internal format that should reside in pg_catalog. schema. -- Ivan Sergio Borgonovo http://www.webthatworks.it
Ivan Sergio Borgonovo wrote:
So you connect to postgresql through psql -E
I guess I haven't been clear. I am writing a module and want it to be able to support both MySql and Postgres. This will be in the code. I don't have Postgres. Nancy E. Wichmann, PMP Injustice anywhere is a threat to justice everywhere. -- Dr. Martin L. King, Jr. -----Original Message----- From: development-bounces@drupal.org [mailto:development-bounces@drupal.org]On Behalf Of Ivan Sergio Borgonovo Sent: Friday, September 04, 2009 4:42 PM To: development@drupal.org Subject: Re: [development] Postgres equivalent to SHOW TABLES On Fri, 4 Sep 2009 15:56:41 -0400 "Nancy Wichmann" <nan_wich@bellsouth.net> wrote:
Ivan Sergio Borgonovo wrote:
start psql with -E param ...
This is in a module, so I cannot demand that the user start it any way other than how it would be started for Drupal.
That's just to learn how to get the information with SQL. So you connect to postgresql through psql -E use \d stuff get the SQL used by psql to get the info and copy and paste it in your drupal module. Actually this is not going to be portable. A better way would be to learn how to query information_schema that *should* be portable. information_schema should be in the sql-92 standard. http://dev.mysql.com/doc/refman/5.0/en/information-schema.html http://www.postgresql.org/docs/8.1/interactive/information-schema.html http://www.ocelot.ca/is.htm I think that in postgresql information_schema is just a view on the internal format that should reside in pg_catalog. schema. -- Ivan Sergio Borgonovo http://www.webthatworks.it No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.409 / Virus Database: 270.13.73/2338 - Release Date: 09/04/09 05:51:00
information_schema is supported by both PostgreSQL and Mysql, with some subtle differences. Drupal 6 doesn't have any schema querying features, so I suggest you open a feature request to the schema module, and rely on it for your needs. Damien Tournoud On Sat, Sep 5, 2009 at 12:50 AM, Nancy Wichmann<nan_wich@bellsouth.net> wrote:
Ivan Sergio Borgonovo wrote:
So you connect to postgresql through psql -E
I guess I haven't been clear. I am writing a module and want it to be able to support both MySql and Postgres. This will be in the code. I don't have Postgres.
Nancy E. Wichmann, PMP Injustice anywhere is a threat to justice everywhere. -- Dr. Martin L. King, Jr.
-----Original Message----- From: development-bounces@drupal.org [mailto:development-bounces@drupal.org]On Behalf Of Ivan Sergio Borgonovo Sent: Friday, September 04, 2009 4:42 PM To: development@drupal.org Subject: Re: [development] Postgres equivalent to SHOW TABLES
On Fri, 4 Sep 2009 15:56:41 -0400 "Nancy Wichmann" <nan_wich@bellsouth.net> wrote:
Ivan Sergio Borgonovo wrote:
start psql with -E param ...
This is in a module, so I cannot demand that the user start it any way other than how it would be started for Drupal.
That's just to learn how to get the information with SQL.
So you connect to postgresql through psql -E use \d stuff get the SQL used by psql to get the info and copy and paste it in your drupal module.
Actually this is not going to be portable. A better way would be to learn how to query information_schema that *should* be portable.
information_schema should be in the sql-92 standard.
http://dev.mysql.com/doc/refman/5.0/en/information-schema.html http://www.postgresql.org/docs/8.1/interactive/information-schema.html http://www.ocelot.ca/is.htm
I think that in postgresql information_schema is just a view on the internal format that should reside in pg_catalog. schema.
-- Ivan Sergio Borgonovo http://www.webthatworks.it
No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.409 / Virus Database: 270.13.73/2338 - Release Date: 09/04/09 05:51:00
Ivan Sergio Borgonovo wrote:
A better way would be to learn how to query information_schema that *should* be portable.
Well, I don't know about portable, but the MySql manual is wrong in its comparison. $list = array(); $result = db_query("SHOW TABLES LIKE 'abc\_%'"); while ($table = db_result($result)) { $list[] = $table; } Worked just fine. However, to make it work with information_schema required: $list = array(); $result = db_query("SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_name LIKE 'abc\_%'"); while ($table = db_fetch_array($result)) { $list[] = $table['table_name']; } $list = array_unique($list); sort($list); Note that the list had MANY duplicates in it and did not come out in order of table name. Nancy E. Wichmann, PMP Injustice anywhere is a threat to justice everywhere. -- Dr. Martin L. King, Jr.
On Fri, Sep 4, 2009 at 7:19 PM, Nancy Wichmann <nan_wich@bellsouth.net>wrote:
Ivan Sergio Borgonovo wrote:
A better way would be to learn how to query information_schema that *should* be portable.
Well, I don't know about portable, but the MySql manual is wrong in its comparison. $list = array(); $result = db_query("SHOW TABLES LIKE 'abc\_%'"); while ($table = db_result($result)) { $list[] = $table; }
Worked just fine. However, to make it work with information_schema required: $list = array(); $result = db_query("SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_name LIKE 'abc\_%'"); while ($table = db_fetch_array($result)) { $list[] = $table['table_name']; } $list = array_unique($list); sort($list);
Note that the list had MANY duplicates in it and did not come out in order of table name.
This is plain SQL, and hence there is nothing stopping you from using DISTINCT as well as ORDER BY, like any other SQL. It should be portable between MySQL and PostgreSQL. -- Khalid M. Baheyeldin 2bits.com, Inc. http://2bits.com Drupal optimization, development, customization and consulting. Simplicity is prerequisite for reliability. -- Edsger W.Dijkstra Simplicity is the ultimate sophistication. -- Leonardo da Vinci
Khalid Baheyeldin wrote:
This is plain SQL, and hence there is nothing stopping you from using DISTINCT as well as ORDER BY, like any other SQL. It should be portable between MySQL and PostgreSQL.
Thank you, Khalid. It looked like it, but this is the first time in this territory, so I wasn't sure. I did that and it worked great. Nancy E. Wichmann, PMP Injustice anywhere is a threat to justice everywhere. -- Dr. Martin L. King, Jr.
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
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@drupal.org [mailto:development-bounces@drupal.org]On Behalf Of Daniel Vérité Sent: Sunday, September 06, 2009 10:31 AM To: development@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
participants (8)
-
Brice -
Damien Tournoud -
Daniel Vérité -
Earnie Boyd -
Ivan Sergio Borgonovo -
Jan Dittberner -
Khalid Baheyeldin -
Nancy Wichmann