Обсуждение: when i logged in mydb,any sql command used to list all the tables in this mydb?

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

On Thu, Apr 8, 2010 at 6:34 PM, sunpeng <bluevaley@gmail.com> wrote:
> 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
>
>

hi,

You can use the -E option for psql, so it will output all querys
executed behind the scenes when you use meta-commands like \dt.
You can then copy and modify those querys to better suit your needs.

Example

$ psql -E mydb
psql (8.4.3)
Type "help" for help.

mydb=# \dt
.. (the query that gets executed is appears here)..

                 List of relations
 Schema |           Name           | Type  | Owner
--------+--------------------------+-------+--------
 public | sometable         | table | myname



HTH,

diego

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

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

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@ashisuto.co.jp
> <mailto:ketanaka@ashisuto.co.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@postgresql.org
>     <mailto:pgsql-general@postgresql.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
================================================