Re: SHOW TABLES

On Mon, 2010-08-09 at 17:42 -0400, Bruce Momjian wrote:
> Robert Haas wrote:
> > On Sat, Jul 17, 2010 at 11:14 PM, Bruce Momjian <bruce@momjian.us> wrote:
> > > Robert Haas wrote:
> > >> I'd like to be able to list comments on objects of a particular type.
> > >> And, yeah, I'd like to be able to list all the aggregates that take a
> > >> numeric argument, or all the functions that take, say, an argument of
> > >> type internal. ?Right now, this is an ENORMOUS pain in the neck. ?I
> > >> usually end up running psql -c '<some backslash command>' | ?grep |
> > >> awk ... or something like that. ?I have no idea what Windows users do.
> > >> ?I'm sure it's possible to write a query to do it, but it's not
> > >> anything approaching easy. ?All of this talk about backslash commands
> > >> being powerful rings totally hollow for me. ?For ordinary, day to day
> > >> tasks like listing all my tables, or looking at the details of a
> > >> particular table, they're great. ?I use them all the time and would
> > >> still use them even if some other syntax were available. ?But there is
> > >> no reasonable way to pass options to them, and that to me is a pretty
> > >> major drawback.
> > >
> > > I am concerned that implementing a command syntax to show complex output
> > > like above effectively means re-implementing a subset of SQL, and that
> > > subset will never be as flexible.
> > 
> > That's a reasonable concern, but I don't have a better idea.  Do you?
> 
> Sorry for the late reply.  If we are going to end up recreating SQL, we
> might as well just keep the backslash mess we have, or tell them to use
> SQL for the complex queries.  My point was that we might find that what
> we cook up is as complex as what we have now.

My proposal is that SHOW TABLES returns exactly the same output as \d
yet works the same from every interface.

I have no intention of designing or writing what Robert proposes above
and he is welcome to do that, but its clearly a different requirement.

SHOW TABLES is a simple command with a simple purpose: helping newbies
by putting obvious commands in their way that do useful things. The
simple goal I've outlined for SHOW TABLES has nothing whatsoever to do
with inventing what appears to be a new flexible catalog metadata
language that is hard to implement, non-standard and poorly understood
except by only a few people.

Currently, \d runs this SQL

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", 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','')     AND n.nspname <> 'pg_catalog'     AND n.nspname <> 'information_schema'     AND
n.nspname!~ '^pg_toast' AND pg_catalog.pg_table_is_visible(c.oid)
 
ORDER BY 1,2;

I would make this into a server view, and then make SHOW TABLES a
synonym for SELECT * FROM pg_show_backslash_d. I'd probably work out a
better name for the view also. It's mostly just refactoring.

-- Simon Riggs           www.2ndQuadrant.comPostgreSQL Development, 24x7 Support, Training and Services