Re: Strange "missing tables" problem

Hi Denis,

Le lundi 24 août 2009 à 10:21:33, Denis BUCHER a écrit :
> > [...]
> > I suppose you have something like 'rma, import, ...' for search_path, so
> > it only displays rma.customers and not impor.customers.
>
> Yes that's correct. Therefore my "problem" is the "normal" behavior of
> \dt. But "normal" means "expected". But I don't find it very
> secure/handy, because you expect to see all your tables. Is there a way
> to change the behavior of \dt so that it lists ALL tables present in
> search path ?
>

No. But you can always take a look a this:

  http://radek.cc/2009/08/15/psqlrc-tricks-table-sizes/

You can probably do a \set dt your_query, and then :dt; instead of \dt. ... a
few moments later... I tried and it works.

Put this line in your .psqlrc file:
\set dt '(SELECT n.nspname as \"Schéma\", c.relname as \"Nom\", CASE c.relkind
WHEN \'r\' THEN \'table\' WHEN \'v\' THEN \'vue\' WHEN \'i\' THEN \'index\'
WHEN \'S\' THEN \'séq    uence\' WHEN \'s\' THEN \'spécial\' END as \"Type\",
r.rolname as \"Propriétaire\" FROM pg_catalog.pg_class c JOIN
pg_catalog.pg_roles r ON r.oid = c.relowner LEFT JOIN pg_cata
log.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN (\'r\',\'\')
AND n.nspname NOT IN (\'pg_catalog\', \'pg_toast\') ORDER BY 1,2)'

It should be one line only, and you can add more schemas on the n.nspname
filter (information_schema for example).

Now, I get this with this .psqlrc trick:

guillaume@laptop:~$ psql -q a
a=# set search_path to public, toto;
a=# \dt
         Liste des relations
 Schéma | Nom | Type  | Propriétaire
--------+-----+-------+--------------
 public | t1  | table | ab1
 public | t2  | table | a2
 public | t3  | table | postgres
 toto   | t4  | table | guillaume
(4 lignes)

a=# :dt;
       Schéma       |           Nom           | Type  | Propriétaire
--------------------+-------------------------+-------+--------------
 information_schema | sql_features            | table | guillaume
 information_schema | sql_implementation_info | table | guillaume
 information_schema | sql_languages           | table | guillaume
 information_schema | sql_packages            | table | guillaume
 information_schema | sql_sizing              | table | guillaume
 information_schema | sql_sizing_profiles     | table | guillaume
 pgagent            | pga_exception           | table | guillaume
 pgagent            | pga_job                 | table | guillaume
 pgagent            | pga_jobagent            | table | guillaume
 pgagent            | pga_jobclass            | table | guillaume
 pgagent            | pga_joblog              | table | guillaume
 pgagent            | pga_jobstep             | table | guillaume
 pgagent            | pga_jobsteplog          | table | guillaume
 pgagent            | pga_schedule            | table | guillaume
 public             | t1                      | table | ab1
 public             | t2                      | table | a2
 public             | t3                      | table | postgres
 toto               | t1                      | table | guillaume
 toto               | t4                      | table | guillaume
(19 lignes)


Hope it helps.


--
Guillaume.
 http://www.postgresqlfr.org
 http://dalibo.com