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(at)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