Re: psql \d option list overloaded

Alex J. Avriette wrote:

>On Sat, Jan 03, 2004 at 08:25:21PM -0500, Bruce Momjian wrote:
>
>
>>>I finally figure it out, I just end up forgetting again later. I still
>>>have no clue how I'd find the same data without using psql. In MySQL
>>>I can run those queries from PHP, PERL...etc. I know you can find that
>>>data in system tables in PostgreSQL, but I don't wanna muck around with
>>>all that. I just wanna do something as simple as MySQL.
>>>
>>>
>>[ Moved to hackers.]
>>
>>I am starting to agree that our \d* handling is just too overloaded.
>>Look at the option list from \?:
>>
>>
>>I like the idea of adding a new syntax to show that information using
>>simple SQL command syntax, and putting it in the backend so all
>>applications can access it. I know we have information schema, and
>>maybe that can be used to make this simpler.
>>
>>
>Bruce, while I agree with you about \d (and all its children), as well
>as the querying we talked about on irc, I disagree with the notion of a
>"SHOW DATABASES" query. This is one of the things that irritates me
>about mysql is the pseudo-sql that everyone has come to accept ... It doesn't
>make sense to create pseudo-sql, when all you're abstracting is function-macros...
>
Anything other than simple, short commands is a waste, IMHO. I can easily
remember SHOW DATABASES and SHOW TABLES and DESC <table>, because they
reflect
my intensions directly and 'make sense'.

Using the slash commands works if you are familiar with them ... sorta
like 'ls' switches (I type 'ls -alF' without thinking about what those
switches do because it's embedded in my head from years of repetition.
Any other flags to 'ls', and I gotta go hit the man pages.)

What's more important is the ability to use these commands from any
interface not just 'psql' client. I think 'psql' already has the slash
commands. No need to create NEW slash commands there...

>If you want to find out how to show the databases in sql, use psql -E.
>
>
Have you actually done that? OMG!

1) Using System Catalogs ... (from psql -E)

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;

or ...

2) (using information schema ... little better)

SELECT table_name FROM information_schema.tables WHERE table_schema
= 'public';

or ...

3) like MySQL does it...

SHOW TABLES;

Lemme think about which one I prefer ;-) Uh, Ok, I'm done thinking
now. hehe.

There's something to be said about the 'SHOW'and 'DESC' sql-extensions
added into MySQL. Newbies can really 'get' it quickly. It's what really
sold me on MySQL when I first learned it. For me, it's like:

'dir' in DOS,
'ls' in Unix
'SHOW' in MySQL
??? in PostgreSQL ?

Sure, with time as my database needs grew and I matured as a developer,
I eventually gained more respect for PostgreSQL and have made the switch
even without this feature, but to this day, I really think MySQL *did it
right* with those extensions. You can't become a PostgreSQL guru without
being a newbie first. I vote we make it easier for newbies.

Dante