Re: [gnome-db]Not all tables in PostgreSQL

[Adam Tauno Williams <awilliam whitemice org>]

>>Is there a known problem with table enumeration?  I create a new
>>PostgreSQL database with one table and one sequence.  I attached with
>>gnomedb-fe and gASQL to see if it worked and it did!  Then I added a few
>>more tables and sequences and .... they don't show up in either
>>gnomedb-fe or gASQL.  I can see them in psql using "\d".  I've restarted
>>the applications,  logged out and back in, etc... but can't get the
>>tables.
>I have never seen this problem before.
>
>	" SELECT a.realname AS \"Name\" "
>	" FROM pg_class a, pg_user b "
>        "WHERE ( relkind = 'r') and relname !~ '^pg_' "
>        "AND relname !~ '^xin[vx][0-9]+' AND "
>        "b.usesysid = a.relowner AND "
>        "NOT (EXISTS (SELECT viewname FROM pg_views "
>        "WHERE viewname=a.relname)) ");
>That's the query the postgres provider does to get the list of tables
>from the postgres server. Maybe the names of your tables don't match the
>"AND relname !~ '^xin[vx][0-9]+'" condition?

~ $ cat test.sql
        SELECT a.relname AS Name 
         FROM pg_class a, pg_user b 
        WHERE ( relkind = 'r') and relname !~ '^pg_' 
        AND relname !~ '^xin[vx][0-9]+' AND 
        b.usesysid = a.relowner AND 
        NOT (EXISTS (SELECT viewname FROM pg_views 
        WHERE viewname=a.relname));
~ $ psql maindb < test.sql 
       name       
------------------
 applications
 data_tags
 application_tags
 data_values
(4 rows)

Only the "applications" table shows up through gnome-db.  Notice that I had to 
change a.realname to a.relname,  as the realname column doesn't seem to exist
in pg_class (don't know if that is just a type, or not).  These tables can be
enumerated via unixODBC.