Search Postgresql Archives

Re: when i logged in mydb,any sql command used to list all the tables in this mydb?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



hi,Kenichiro,
  Thanks for your answer!
  Then another question comes: According to 8.4 document 44.1(Most system catalogs are copied from the template database during database creation and are thereafter database-specific.),we know  that each created database has their own pg_... tables and thus if a superuser administrator wants to list all the tables in all the databases,how do the postgresql interval implement it? will the postgresql interval load all the pg_.... tables  in all the databases to get the final answer?
 Thanks!
peng

2010/4/8 Kenichiro Tanaka <ketanaka@xxxxxxxxxxxxxx>
Hello sunpeng

First,I answer this question.

>another question:how postgresql internal knows which
>relations belongs to which database?

When we use "psql" command ,it means "psql -d postgres".
#you can change default parameter to set PGDATABASE (OS parameter)

"-d" indicate your connecting database and this is the ANSWER.
So, it is natural that pg_class does not have "database" field.
We can not access other database with psql without using dblink.

I think we can  display some database's pg_class with following command,
============================================================
#DISPLAY  "test" and "postgres"'s tables

#Your postgresql have to be installed dblink
#1)create dblink
select dblink_connect('test','host=postgres01 port=1843 dbname=test user=p843');
select dblink_connect('postgres','host=postgres01 port=1843 dbname=postgres user=p843');

#2) display
select 'test',* from dblink('test','select oid, relname from pg_class') t1(oid oid, relname text)
union
select 'postgres',* from dblink('postgres','select oid, relname from pg_class') t1(oid oid, relname text);

============================================================

Thank you


once i have created mydb and several relations in it,are there any sql commands used to list all the tables in this mydb?
i noticed there are no database( pg_database.oid) field in pg_class table,so i can not use
select relname from pg_class,pg_database where pg_database.datname like 'mydb' and pg_class.database = pg_database.oid;
anybody knows how to do it?
another question:how postgresql internal knows which relations belongs to which database?

thanks



--
================================================
Kenichiro Tanaka
K.K.Ashisuto
http://www.ashisuto.co.jp/english/index.html
================================================


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux