Re: SQL Command - To List Tables ?

On Wed, 2003-08-27 at 21:03, Heath Tanner wrote:
> On Wednesday, August 27, 2003, at 09:17 AM, Carmen Gloria Sepulveda
> Dedes wrote:
>
> > Hello. I type \dt as user postgres, but it show only the tables within
> > public schema. I need to know all tables (like v$_ or dba_ or user_
> > in oracle). How I do that?
>
> Start psql with the echo flag:
>
> [inligo:~] heath% psql -E dpe
> ********* QUERY **********
> BEGIN; SELECT usesuper FROM pg_catalog.pg_user WHERE usename = 'heath';
> COMMIT
> **************************
>
> Welcome to psql 7.3.4, the PostgreSQL interactive terminal.
>
> Type: \copyright for distribution terms
> \h for help with SQL commands
> \? for help on internal slash commands
> \g or terminate with semicolon to execute query
> \q to quit
>
> dpe=# \dt
> ********* QUERY **********
> 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' END as
> "Type",
> u.usename as "Owner"
> FROM pg_catalog.pg_class c
> LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
> LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
> WHERE c.relkind IN ('r','')
> AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
> AND pg_catalog.pg_table_is_visible(c.oid)
> ORDER BY 1,2;
> **************************

Isn't that big CASE statement unnecessary, since you also have
c.relkind IN ('r','')

--
-----------------------------------------------------------------
Ron Johnson, Jr. ron(dot)l(dot)johnson(at)cox(dot)net
Jefferson, LA USA

484,246 sq mi are needed for 6 billion people to live, 4 persons
per lot, in lots that are 60'x150'.
That is ~ California, Texas and Missouri.
Alternatively, France, Spain and The United Kingdom.