Re: newbie needs help with postgres internal tables

William WAISSE <wwaisse@esprit-equipe.fr> writes:

does a documentation exists concerning postgres internals, which I
havren't found, and which would say something like
"the relation between pg_class and pg_attribute is the field
oid in in pg_class which is the same as the field attrelid in pg_attribute"
"attnum in pg_attribute is >0 for 'user' fields and <0 for internal fields"
. . .

See the "system catalogs" chapter of the developer's guide. It's a bit
terse but I believe the information you mention above is all stated at,
eg,
http://candle.pha.pa.us/main/writings/pgsql/sgml/catalog-pg-attribute.html

regards, tom lane

William WAISSE <wwaisse@esprit-equipe.fr> writes:

It's necessary cause I want to display in a treeview ( GtkCtree ) all the
the tables in a particular database, and for each table, all the fields in
this table ).

You can only see tables of the database you are connected to. To do the
above, you'd need to open a connection to each database in turn
(assuming that the installation is configured to let you get into all of
them...)

pg_database is visible from all databases, and so are pg_shadow and
pg_group, but AFAIR everything else is database-local.

regards, tom lane

Le Friday 23 November 2001 12:23, Tom Lane a �crit :

does a documentation exists concerning postgres internals, which I
havren't found, and which would say something like
"the relation between pg_class and pg_attribute is the field
oid in in pg_class which is the same as the field attrelid in
pg_attribute" "attnum in pg_attribute is >0 for 'user' fields and <0 for
internal fields" . . .

See the "system catalogs" chapter of the developer's guide. It's a bit
terse but I believe the information you mention above is all stated at,
eg,
http://candle.pha.pa.us/main/writings/pgsql/sgml/catalog-pg-attribute.html

Yes,;-)) thank you very much, it seems like this is exactly what I needed.

I searched for this data for a very long time.

thank you very much.

--
cordialement, William WAISSE fpr = 9CCD 7DA2 7050 8805 F471 03D1 DF76 B78C
690B 4E07
--
Computers are like air conditionners. They work better when you close windows.
--
Visitez donc mon site perso (version 0.4 du 29/05/2001) :
http://www.neofutur.net
--
Vous habitez dans l'Essonne et vous aimez les Pingouins ???
visitez donc la GAULE(=LUG91) : http://www.gaule.org
-----BEGIN GEEK CODE BLOCK-----
Version: 3.12
GCS/E/MU d- s: a- C++++ UL+++ P+ L++++ E--- W++ N o-- K- w---
O- M- V- PS+ PE-- Y++ PGP++ t+ 5 X++ R++ tv- b+++ DI- D G e++ h* r++ y+++
------END GEEK CODE BLOCK------

Le Friday 23 November 2001 12:23, Tom Lane a �crit :

See the "system catalogs" chapter of the developer's guide. It's a bit
terse but I believe the information you mention above is all stated at,
eg,
http://candle.pha.pa.us/main/writings/pgsql/sgml/catalog-pg-attribute.html

hum, now I looked at those catalogs, I'm nearly sure there is no id in
pg_database that would allow me to select all the tables ( class ) that
belong to a particular database.

Whereas it's possible to find all the fields in a table ).

This seems to me impossible !

It's necessary cause I want to display in a treeview ( GtkCtree ) all the
the tables in a particular database, and for each table, all the fields in
this table ).

I will have to maintain myself a table containing a record for each table
created in a database by my ( future ) application.

can someone tell me this is the only solution ?

--
cordialement, William WAISSE fpr = 9CCD 7DA2 7050 8805 F471 03D1 DF76 B78C
690B 4E07
--
Computers are like air conditionners. They work better when you close windows.
--
Visitez donc mon site perso (version 0.4 du 29/05/2001) :
http://www.neofutur.net
--
Vous habitez dans l'Essonne et vous aimez les Pingouins ???
visitez donc la GAULE(=LUG91) : http://www.gaule.org
-----BEGIN GEEK CODE BLOCK-----
Version: 3.12
GCS/E/MU d- s: a- C++++ UL+++ P+ L++++ E--- W++ N o-- K- w---
O- M- V- PS+ PE-- Y++ PGP++ t+ 5 X++ R++ tv- b+++ DI- D G e++ h* r++ y+++
------END GEEK CODE BLOCK------

Le Friday 23 November 2001 14:14, Tom Lane a �crit :

It's necessary cause I want to display in a treeview ( GtkCtree ) all
the the tables in a particular database, and for each table, all the
fields in this table ).

You can only see tables of the database you are connected to. To do the
above, you'd need to open a connection to each database in turn
(assuming that the installation is configured to let you get into all of
them...)

Yes, and that's what I already do, but I always see the tables of this
database AND postgresql ( pg_* ) tables.

pg_database is visible from all databases, and so are pg_shadow and
pg_group, but AFAIR everything else is database-local.

See here the result of select in psql while connected to gpf database:

gpf=> select * from pg_tables;
tablename | tableowner | hasindexes | hasrules | hastriggers
----------------+------------+------------+----------+-------------
pg_type | postgres | t | f | f
pg_attribute | postgres | t | f | f
pg_proc | postgres | t | f | f
pg_class | postgres | t | f | f
pg_group | postgres | t | f | f
pg_database | postgres | f | f | f
pg_variable | postgres | f | f | f
pg_log | postgres | f | f | f
pg_xactlock | postgres | f | f | f
pg_attrdef | postgres | t | f | f
pg_relcheck | postgres | t | f | f
pg_trigger | postgres | t | f | f
pg_inherits | postgres | t | f | f
pg_index | postgres | t | f | f
pg_statistic | postgres | t | f | f
pg_operator | postgres | t | f | f
pg_opclass | postgres | t | f | f
pg_am | postgres | t | f | f
pg_amop | postgres | t | f | f
pg_amproc | postgres | f | f | f
pg_language | postgres | t | f | f
pg_aggregate | postgres | t | f | f
pg_ipl | postgres | f | f | f
pg_inheritproc | postgres | f | f | f
pg_rewrite | postgres | t | f | f
pg_listener | postgres | t | f | f
pg_description | postgres | t | f | f
pg_shadow | postgres | f | f | t
gpf_db | gpf | t | f | f
(29 rows)

For information I finally found two solutions :

1- Maintain myself a table containing the tables of each database.

2- Automatically naming all the tables of a database using the same rules
( ie : gpf database, all the tables are named gpf_*), so that a
"select * from pg_tables where tablename like 'gpf%' " will return what I
need.
I just have to keep the naming convetion of each database, which is not a
problem since I already have one internal table containing a row row for
each database created and ruled by GPF ( my future application ).

I chose the second solution ( probably faster and easier ).

Thanks for your answers.

PS: Sorry if my english is not always the best, I'm French ;-)

--
cordialement, William WAISSE fpr = 9CCD 7DA2 7050 8805 F471 03D1 DF76 B78C
690B 4E07
--
Computers are like air conditionners. They work better when you close windows.
--
Visitez donc mon site perso (version 0.4 du 29/05/2001) :
http://www.neofutur.net
--
Vous habitez dans l'Essonne et vous aimez les Pingouins ???
visitez donc la GAULE(=LUG91) : http://www.gaule.org
-----BEGIN GEEK CODE BLOCK-----
Version: 3.12
GCS/E/MU d- s: a- C++++ UL+++ P+ L++++ E--- W++ N o-- K- w---
O- M- V- PS+ PE-- Y++ PGP++ t+ 5 X++ R++ tv- b+++ DI- D G e++ h* r++ y+++
------END GEEK CODE BLOCK------