On Wed, Jul 17, 2013 at 5:29 PM, Carla Goncalves <cgourofino@hotmail.com> wrote:
> Hi
> I would like to list the definition of all user tables by only one command.
> Is there a way to *not* show pg_catalog tables when using "\d ." in
> PostgreSQL 9.1.9?
>
What do you mean by "user tables"? The execution of \d without any
argument provides the definition of all reachable tables (by mean of
search_path) that are not belonging to the information schema or toast
space, that is:
SELECT n.nspname as "Schema", c.relname as "Name", CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i'
THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f'
THEN 'foreign table' END as "Type", pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','S','f','') AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema'
ANDn.nspname !~ '^pg_toast' AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
This kind of queries are hard-coded into the psql program, and
therefore cannot be altered on the fly as far as I know.
One trick could be to define a custom query as a psql variable, let's say:
\set my_d '* from pg_class left join ....';
and then do something like
select :my_d;
It's shorter, but it is not the same as a builtin command.
Luca