Thread: newbie needs help with postgres internal tables
Hi, everybody.
This is my first post on this list and I hope It's the good place for my
question.
I'm developping an Opensource projetc which aims to be a GTK
frontend for Postgresql first ( and other DB later ), whose name will be
GPF ( GPF is not a Postgresql Frontend ).
So I need to use mysef tables like pg_class, pg_attribute, pgdatabase.
By now, I need two things :
1- Find all the fields that belongs to a particular table.
It was difficult to find the good fiels but I found this query, which seems
to be good for me.
select attname
from pg_attribute a
where a.attnum>0
and
a.attrelid= ( select oid
from pg_class c
where c.relname='gpf_db') ;
( 'gpf_db' is the name of the database I'm searching all the fattributes )
( Is ther a better way to do this )
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"
. . .
( In fact I would need a map of all the relations between all the internal
tables in postgres ;- ))
1- Find all the tables that belong to a particular database.
I have not been able to find an ID in pg_table or pg_class which
would help me finding all the tables that belong to a particular database.
the only thing I could do is selecting on the field tableowner
BUT
a user can have more than one database and all the tables of all the
databases owned by this user will have the same tableowner.
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------
My suggestion would be to run psql with the -E option. This will display the commands it uses to get it's info. William WAISSE wrote: > Hi, everybody. > > This is my first post on this list and I hope It's the good place for my > question. > > I'm developping an Opensource projetc which aims to be a GTK > frontend for Postgresql first ( and other DB later ), whose name will be > GPF ( GPF is not a Postgresql Frontend ). > > So I need to use mysef tables like pg_class, pg_attribute, pgdatabase. > > By now, I need two things : > > 1- Find all the fields that belongs to a particular table. > > It was difficult to find the good fiels but I found this query, which seems > to be good for me. > > select attname > from pg_attribute a > where a.attnum>0 > and > a.attrelid= ( select oid > from pg_class c > where c.relname='gpf_db') ; > > ( 'gpf_db' is the name of the database I'm searching all the fattributes ) > ( Is ther a better way to do this ) > > 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" > . . . > > ( In fact I would need a map of all the relations between all the internal > tables in postgres ;- )) > > > 1- Find all the tables that belong to a particular database. > > I have not been able to find an ID in pg_table or pg_class which > would help me finding all the tables that belong to a particular database. > the only thing I could do is selecting on the field tableowner > BUT > a user can have more than one database and all the tables of all the > databases owned by this user will have the same tableowner. > > PS : sorry if my english is not always the best, I'm French ;-) > >
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
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------
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
William WAISSE wrote: > >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 ! > Hi, If you set the ECHO_HIDDEN variable in psql, or start psql with the -E option, you'll see the queries used by commands like \dt and others. You could emulate then in your application. Regards, Antonio Sergio
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------