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

Hi peng

> 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
I believe it is right.

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

I think postgresql never interval load all the pg_.... tables in all
the databases.
If administrator want to list all the tables,we heve to create application.

ex.
oid2name -q | awk '{print$2}'|grep -v template0 |xargs -l1 oid2name -d

* oid2name is contrib module.

Thank you

> 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(at)ashisuto(dot)co(dot)jp
> <mailto:ketanaka(at)ashisuto(dot)co(dot)jp>>
>
> 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(at)postgresql(dot)org
> <mailto:pgsql-general(at)postgresql(dot)org>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>

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