[GENERAL] when i logged in mydb,any sql command used to list all the tables in this mydb?

2010-04-08 Thread sunpeng 
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

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

2010-04-08 Thread Diego Schulz 
On Thu, Apr 8, 2010 at 6:34 PM, sunpeng blueva...@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

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

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

2010-04-08 Thread Kenichiro Tanaka 

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)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

2010-04-08 Thread sunpeng 
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 ketan...@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)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general

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

2010-04-08 Thread Kenichiro Tanaka 

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 ketan...@ashisuto.co.jp 
mailto:ketan...@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



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