Thread: newbie needs help with postgres internal tables

newbie needs help with postgres internal tables

From
William WAISSE
Date:
 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------

Re: newbie needs help with postgres internal tables

From
Dave Smith
Date:
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 ;-)
>
>



Re: newbie needs help with postgres internal tables

From
Tom Lane
Date:
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

Re: newbie needs help with postgres internal tables

From
William WAISSE
Date:
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------

Re: newbie needs help with postgres internal tables

From
William WAISSE
Date:
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------

Re: newbie needs help with postgres internal tables

From
Tom Lane
Date:
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

Re: newbie needs help with postgres internal tables

From
Antonio Sergio de Mello e Souza
Date:
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


Re: newbie needs help with postgres internal tables

From
William WAISSE
Date:
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------