path: root/doc/TODO.detail/psql

From pgsql-hackers-owner+M48909@postgresql.org Thu Jan  8 21:54:03 2004
Return-path: <pgsql-hackers-owner+M48909@postgresql.org>
Received: from noon.pghoster.com (noon.pghoster.com [64.246.0.64])
	by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id i092s2X02439
	for <pgman@candle.pha.pa.us>; Thu, 8 Jan 2004 21:54:02 -0500 (EST)
Received: from svr1.postgresql.org ([200.46.204.71] helo=postgresql.org)
	by noon.pghoster.com with esmtp (Exim 4.24)
	id 1AemmG-0002Wx-5B; Thu, 08 Jan 2004 20:53:36 -0600
X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 88703D1B46E
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>; Mon,  5 Jan 2004 02:00:10 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 72572-01
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>;
	Sun,  4 Jan 2004 21:59:23 -0400 (AST)
Received: from rwcrmhc13.comcast.net (rwcrmhc13.comcast.net [204.127.198.39])
	by svr1.postgresql.org (Postfix) with ESMTP id DD336D1B454
	for <pgsql-hackers@postgresql.org>; Sun,  4 Jan 2004 21:59:04 -0400 (AST)
Received: from lorenso.com (c-24-1-26-144.client.comcast.net[24.1.26.144])
          by comcast.net (rwcrmhc13) with ESMTP
          id <20040105015908015005cvvee>; Mon, 5 Jan 2004 01:59:08 +0000
Message-ID: <3FF8C4E6.9090008@lorenso.com>
Date: Sun, 04 Jan 2004 19:59:02 -0600
From: "D. Dante Lorenso" <dante@lorenso.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.6b) Gecko/20031205 Thunderbird/0.4
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: "Alex J. Avriette" <alex@posixnap.net>
cc: Bruce Momjian <pgman@candle.pha.pa.us>,
   PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] psql \d option list overloaded
References: <3FEE6DFB.9040408@lorenso.com> <200401040125.i041PLR14687@candle.pha.pa.us> <20040104191322.GD8524@posixnap.net>
In-Reply-To: <20040104191322.GD8524@posixnap.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Mailing-List: pgsql-hackers
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
X-AntiAbuse: This header was added to track abuse, please include it with any abuse report
X-AntiAbuse: Primary Hostname - noon.pghoster.com
X-AntiAbuse: Original Domain - candle.pha.pa.us
X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12]
X-AntiAbuse: Sender Address Domain - postgresql.org
X-Spam-Checker-Version: SpamAssassin 2.61 (1.212.2.1-2003-12-09-exp) on 
	candle.pha.pa.us
X-Spam-Status: No, hits=-4.8 required=5.0 tests=BAYES_00,HTML_MESSAGE 
	autolearn=no version=2.61
Status: OR

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



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faqs/FAQ.html

From pgsql-hackers-owner+M48908@postgresql.org Thu Jan  8 21:50:03 2004
Return-path: <pgsql-hackers-owner+M48908@postgresql.org>
Received: from noon.pghoster.com (noon.pghoster.com [64.246.0.64])
	by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id i092ntX01459
	for <pgman@candle.pha.pa.us>; Thu, 8 Jan 2004 21:50:03 -0500 (EST)
Received: from svr1.postgresql.org ([200.46.204.71] helo=postgresql.org)
	by noon.pghoster.com with esmtp (Exim 4.24)
	id 1AemiO-0002D1-Di; Thu, 08 Jan 2004 20:49:36 -0600
X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 7FD9BD1B473
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>; Mon,  5 Jan 2004 02:05:14 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 70484-09
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>;
	Sun,  4 Jan 2004 22:04:28 -0400 (AST)
Received: from news.hub.org (news.hub.org [200.46.204.72])
	by svr1.postgresql.org (Postfix) with ESMTP id 8F8CAD1BB73
	for <pgsql-hackers@postgresql.org>; Sun,  4 Jan 2004 22:04:16 -0400 (AST)
Received: from news.hub.org (news.hub.org [200.46.204.72])
	by news.hub.org (8.12.9/8.12.9) with ESMTP id i0524DU6041774
	for <pgsql-hackers@postgresql.org>; Mon, 5 Jan 2004 02:04:13 GMT
	(envelope-from news@news.hub.org)
Received: (from news@localhost)
	by news.hub.org (8.12.9/8.12.9/Submit) id i0521bd7040362
	for pgsql-hackers@postgresql.org; Mon, 5 Jan 2004 02:01:37 GMT
From: "William ZHANG" <uniware_at_zedware_dot_org@antispam.com>
X-Newsgroups: comp.databases.postgresql.hackers
Subject: Re: [HACKERS] psql \d option list overloaded
Date: Mon, 5 Jan 2004 10:00:55 +0800
Organization: N/A
Lines: 6
Message-ID: <btagi0$17bq$1@news.hub.org>
References: <3FEE6DFB.9040408@lorenso.com> <200401040125.i041PLR14687@candle.pha.pa.us>
X-Complaints-To: usenet@news.hub.org
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
To: pgsql-hackers@postgresql.org
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Mailing-List: pgsql-hackers
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
X-AntiAbuse: This header was added to track abuse, please include it with any abuse report
X-AntiAbuse: Primary Hostname - noon.pghoster.com
X-AntiAbuse: Original Domain - candle.pha.pa.us
X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12]
X-AntiAbuse: Sender Address Domain - postgresql.org
X-Spam-Checker-Version: SpamAssassin 2.61 (1.212.2.1-2003-12-09-exp) on 
	candle.pha.pa.us
X-Spam-Status: No, hits=-3.7 required=5.0 tests=BAYES_00,PRIORITY_NO_NAME 
	autolearn=no version=2.61
Status: OR

I think moving the \d and simliar features in psql
to SQL is a good idea. That will make the features
available in any client library. As for the syntax,
maybe a investigation is needed.



---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org

From pgsql-hackers-owner+M48835=pgman=candle.pha.pa.us@postgresql.org Tue Jan  6 03:08:59 2004
Return-path: <pgsql-hackers-owner+M48835=pgman=candle.pha.pa.us@postgresql.org>
Received: from noon.pghoster.com (noon.pghoster.com [64.246.0.64])
	by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id i0688wX03365
	for <pgman@candle.pha.pa.us>; Tue, 6 Jan 2004 03:08:59 -0500 (EST)
Received: from svr1.postgresql.org ([200.46.204.71] helo=postgresql.org)
	by noon.pghoster.com with esmtp (Exim 4.24)
	id 1AdmGn-0006qB-5D
	for pgman@candle.pha.pa.us; Tue, 06 Jan 2004 02:08:57 -0600
X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 6D470D1B446
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>; Tue,  6 Jan 2004 08:02:13 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 19966-06
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>;
	Tue,  6 Jan 2004 04:01:30 -0400 (AST)
Received: from mail.eckpart.de (unknown [62.206.85.106])
	by svr1.postgresql.org (Postfix) with SMTP id C278CD1B46E
	for <pgsql-hackers@postgresql.org>; Tue,  6 Jan 2004 04:01:06 -0400 (AST)
Received: (qmail 349 invoked from network); 6 Jan 2004 08:01:11 -0000
Received: from unknown (HELO at13.eckpart.de) (192.168.41.70)
  by cserv.eckpart.de with SMTP; 6 Jan 2004 08:01:11 -0000
From: Tommi Maekitalo <t.maekitalo@epgmbh.de>
Organization: Dr. Eckhardt + Partner GmbH
To: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] psql \d option list overloaded
Date: Tue, 6 Jan 2004 09:01:10 +0100
User-Agent: KMail/1.5.4
References: <3FEE6DFB.9040408@lorenso.com> <200401040125.i041PLR14687@candle.pha.pa.us> <20040104191322.GD8524@posixnap.net>
In-Reply-To: <20040104191322.GD8524@posixnap.net>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-ID: <200401060901.10830.t.maekitalo@epgmbh.de>
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Mailing-List: pgsql-hackers
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
X-AntiAbuse: This header was added to track abuse, please include it with any abuse report
X-AntiAbuse: Primary Hostname - noon.pghoster.com
X-AntiAbuse: Original Domain - candle.pha.pa.us
X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12]
X-AntiAbuse: Sender Address Domain - postgresql.org
X-Spam-Checker-Version: SpamAssassin 2.61 (1.212.2.1-2003-12-09-exp) on 
	candle.pha.pa.us
X-Spam-Status: No, hits=-4.9 required=5.0 tests=BAYES_00 autolearn=ham 
	version=2.61
Status: OR

Am Sonntag, 4. Januar 2004 20:13 schrieb Alex J. Avriette:
> 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
...
>
> /functions
> /databases
>
...

Long options sounds really good. It is like GNU-tools. A single - for single 
character options and a double -- for long options.

Ah - a single \ for short options in postgresql and a double \\ for long? What 
do you think?


-- 
Dr. Eckhardt + Partner GmbH
http://www.epgmbh.de


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

From pgsql-hackers-owner+M48912@postgresql.org Thu Jan  8 22:37:54 2004
Return-path: <pgsql-hackers-owner+M48912@postgresql.org>
Received: from hosting.commandprompt.com (216.commandprompt.com [207.173.200.216])
	by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id i093bpX02244
	for <pgman@candle.pha.pa.us>; Thu, 8 Jan 2004 22:37:52 -0500 (EST)
Received: from postgresql.org (svr1.postgresql.org [200.46.204.71])
	by hosting.commandprompt.com (8.11.6/8.11.6) with ESMTP id i093U0k32213;
	Thu, 8 Jan 2004 19:30:33 -0800
X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 95E70D1B43E
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>; Fri,  9 Jan 2004 03:29:43 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 28908-10
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>;
	Thu,  8 Jan 2004 23:28:57 -0400 (AST)
Received: from mail.hive.nj2.inquent.com (mc.carriermail.com [205.178.180.9])
	by svr1.postgresql.org (Postfix) with SMTP id E42E8D1B48A
	for <pgsql-hackers@postgresql.org>; Thu,  8 Jan 2004 23:27:26 -0400 (AST)
Received: (qmail 28537 invoked from network); 9 Jan 2004 03:27:33 -0000
Received: from unknown (HELO ?192.168.1.199?) (134.22.68.14)
  by 205.178.180.9 with SMTP; 9 Jan 2004 03:27:33 -0000
Subject: Re: [HACKERS] psql \d option list overloaded
From: Rod Taylor <pg@rbt.ca>
To: "D. Dante Lorenso" <dante@lorenso.com>
cc: "Alex J. Avriette" <alex@posixnap.net>,
   Bruce Momjian <pgman@candle.pha.pa.us>,
   PostgreSQL Development <pgsql-hackers@postgresql.org>
In-Reply-To: <3FF8C4E6.9090008@lorenso.com>
References: <3FEE6DFB.9040408@lorenso.com>
  <200401040125.i041PLR14687@candle.pha.pa.us>
  <20040104191322.GD8524@posixnap.net>  <3FF8C4E6.9090008@lorenso.com>
Content-Type: text/plain
Message-ID: <1073618847.322.29.camel@jester>
MIME-Version: 1.0
X-Mailer: Ximian Evolution 1.4.5 
Date: Thu, 08 Jan 2004 22:27:28 -0500
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Mailing-List: pgsql-hackers
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
X-Spam-Checker-Version: SpamAssassin 2.61 (1.212.2.1-2003-12-09-exp) on 
	candle.pha.pa.us
X-Spam-Status: No, hits=-4.8 required=5.0 tests=BAYES_00,HTML_MESSAGE 
	autolearn=no version=2.61
Status: OR

> 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'.

Can you remember how to get a list of indexes on a particular table? How
about a specific indexes build? I ask, because I constantly forgot both
of those (don't like FROM).

> 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.

I actually prefer #2 myself. It works on a number of databases aside
from just PostgreSQL. So, as a user who worked in a mixed environment it
was easier to remember.

But I get your point.

> 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

I agree with the simple SHOW TABLES command but disagree with:

SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [LIKE wild]

I much prefer:

SELECT * FROM COLUMNS WHERE table LIKE '%tab%' AND database = 'billing';

It's not much longer, certainly more natural to those that know SQL, and
infinitely more useful since you can create result sets that the
programmer of SHOW hadn't considered. A perfect example is the addition
of the FULL clause in SHOW. The above select does not need additional
keywords for different formatting options as it can simply use "natural"
SQL styling.


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

From tswan@idigx.com Fri Jan  9 02:07:40 2004
Return-path: <tswan@idigx.com>
Received: from stubee.d2hosting.net (d2hosting.net [66.70.41.160])
	by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id i0977cX28507
	for <pgman@candle.pha.pa.us>; Fri, 9 Jan 2004 02:07:40 -0500 (EST)
Received: from idigx.com (adsl-159-238-227.mob.bellsouth.net [68.159.238.227])
	by stubee.d2hosting.net (8.11.6/linuxconf) with ESMTP id i0977Qn08421;
	Fri, 9 Jan 2004 01:07:26 -0600
Message-ID: <3FFE532C.2090503@idigx.com>
Date: Fri, 09 Jan 2004 01:07:24 -0600
From: Thomas Swan <tswan@idigx.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.6b) Gecko/20031205 Thunderbird/0.4
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: pgsql-hackers@postgresql.org
cc: Bruce Momjian <pgman@candle.pha.pa.us>
Subject: Re: [HACKERS] psql \d option list overloaded
References: <200401060504.i0654B012562@candle.pha.pa.us>
In-Reply-To: <200401060504.i0654B012562@candle.pha.pa.us>
X-Enigmail-Version: 0.82.5.0
X-Enigmail-Supports: pgp-inline, pgp-mime
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Spam-Checker-Version: SpamAssassin 2.61 (1.212.2.1-2003-12-09-exp) on 
	candle.pha.pa.us
X-Spam-Status: No, hits=-4.8 required=5.0 tests=BAYES_00,HTML_MESSAGE 
	autolearn=ham version=2.61
Status: OR

Bruce Momjian wrote:

>Alex J. Avriette wrote:
>  
>
>>On Sun, Jan 04, 2004 at 07:59:02PM -0600, D. Dante Lorenso wrote:
>>
>>    
>>
>>>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'.
>>>      
>>>
>>What makes sense to me in csh doesn't make sense in a bourne shell.
>>You can't expect all applications to work correctly. I'd like to second
>>Peter's "yep" when asked if he could remember all the various \d*
>>commands. It really comes down to whether you're trying. New software
>>(even though you may have been using it for a year) requires some
>>adjustment.
>>    
>>
>
>OK, I will drop the idea.  Thanks.
>
>  
>
Bruce,

The idea is not without merit.   What you are looking at is a way to get
this information as a query without having to know all the intricasies
of all the pg_* internals or duplicating complex queries.   "psql -E"
shows you just how tricky this is.  Secondly, if this information
changes in a release, then the end user has to rewrite all of the
queries to work.   Being able to issue a query to the dbms and get the
information as a normal SQL result makes sense and is definately convenient.

The \d* commands work from psql but not from anywhere else.    Try
getting the information from a PHP script by sending a "\dS" query.   It
doesn't work.   If the same queries were stored in the backend and
referenced by psql and also could be referenced by other scripts, this
would be a good thing and keep the work centralized.   If the queries
were in the backend, the psql users could keep the \dS command but it
would call an internal function or execute a queried stored in the
system tables.


One option is to get the information via a function like

SELECT * FROM pg_info('tables');
SELECT * FROM pg_info('indexes');


"psql -E" would show the same query being executed for "\dt"

Another option if no one wanted a language construct, perhaps one option
would be to store the queries themselves in a table like pg_queries. 
This also has the advantage of exposing the queries used so that they
can used as examples for other purposes.

+------------+------------------------------------------+
|pg_info_type|pg_query                                  |
+------------+------------------------------------------+
|tables      |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;                             |
+------------+------------------------------------------+
|indexes     |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", c2.relname as "Table" FROM       |
|            |pg_catalog.pg_class c JOIN                |
|            |pg_catalog.pg_index i ON i.indexrelid =   |
|            |c.oid JOIN pg_catalog.pg_class c2 ON      |
|            |i.indrelid = c2.oid 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 ('i','')|
|            |AND n.nspname NOT IN ('pg_catalog',       |
|            |'pg_toast') AND                           | 
|            |pg_catalog.pg_table_is_visible(c.oid)     |
|            |ORDER BY 1,2;                             |
+------------+------------------------------------------+


Again, this is just food for thought.  Perhaps it is a way to satisfy
both arguments.

Thomas

From pgsql-hackers-owner+M48922@postgresql.org Fri Jan  9 05:23:03 2004
Return-path: <pgsql-hackers-owner+M48922@postgresql.org>
Received: from noon.pghoster.com (noon.pghoster.com [64.246.0.64])
	by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id i09AN1X10448
	for <pgman@candle.pha.pa.us>; Fri, 9 Jan 2004 05:23:02 -0500 (EST)
Received: from svr1.postgresql.org ([200.46.204.71] helo=postgresql.org)
	by noon.pghoster.com with esmtp (Exim 4.24)
	id 1AetmQ-000637-HX; Fri, 09 Jan 2004 04:22:14 -0600
X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id DCA3ED1B447
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>; Fri,  9 Jan 2004 10:20:50 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 95279-02
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>;
	Fri,  9 Jan 2004 06:20:04 -0400 (AST)
Received: from mail.eckpart.de (unknown [62.206.85.106])
	by svr1.postgresql.org (Postfix) with SMTP id 34AE7D1B4C6
	for <pgsql-hackers@postgresql.org>; Fri,  9 Jan 2004 06:19:45 -0400 (AST)
Received: (qmail 21196 invoked from network); 9 Jan 2004 10:19:46 -0000
Received: from unknown (HELO at13.eckpart.de) (192.168.41.70)
  by cserv.eckpart.de with SMTP; 9 Jan 2004 10:19:46 -0000
From: Tommi Maekitalo <t.maekitalo@epgmbh.de>
Organization: Dr. Eckhardt + Partner GmbH
To: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] psql \d option list overloaded
Date: Fri, 9 Jan 2004 11:19:45 +0100
User-Agent: KMail/1.5.4
References: <3FEE6DFB.9040408@lorenso.com> <20040104191322.GD8524@posixnap.net> <3FF8C4E6.9090008@lorenso.com>
In-Reply-To: <3FF8C4E6.9090008@lorenso.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-ID: <200401091119.45778.t.maekitalo@epgmbh.de>
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Mailing-List: pgsql-hackers
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
X-AntiAbuse: This header was added to track abuse, please include it with any abuse report
X-AntiAbuse: Primary Hostname - noon.pghoster.com
X-AntiAbuse: Original Domain - candle.pha.pa.us
X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12]
X-AntiAbuse: Sender Address Domain - postgresql.org
X-Spam-Checker-Version: SpamAssassin 2.61 (1.212.2.1-2003-12-09-exp) on 
	candle.pha.pa.us
X-Spam-Status: No, hits=-4.9 required=5.0 tests=BAYES_00 autolearn=ham 
	version=2.61
Status: OR

Hi,

>
> 2) (using information schema ... little better)
>
>     SELECT table_name FROM information_schema.tables WHERE table_schema
> = 'public';
>
> or ...
>
...

I just looked at the information_schema. It is a very nice feature, but 
difficult to use in psql.

I just wanted to see, what I can find here. After trying and rtfm I ended in 
'\d information_schema.*'. I get a very large page wich is quite unreadable. 
'\d' is normally very usable.

It would be better not to show the view-definition.

What if \d on views just show the column, type and attribute. \d+ would show 
the full view-definition.


Tommi

-- 
Dr. Eckhardt + Partner GmbH
http://www.epgmbh.de


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faqs/FAQ.html

From pgsql-hackers-owner+M48946@postgresql.org Sat Jan 10 07:42:08 2004
Return-path: <pgsql-hackers-owner+M48946@postgresql.org>
Received: from noon.pghoster.com (noon.pghoster.com [64.246.0.64])
	by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id i0ACg6X18515
	for <pgman@candle.pha.pa.us>; Sat, 10 Jan 2004 07:42:07 -0500 (EST)
Received: from svr1.postgresql.org ([200.46.204.71] helo=postgresql.org)
	by noon.pghoster.com with esmtp (Exim 4.24)
	id 1AfINd-0006bw-4f; Sat, 10 Jan 2004 06:38:17 -0600
X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 35C29D1D542
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>; Sat, 10 Jan 2004 12:36:36 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 91943-10
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>;
	Sat, 10 Jan 2004 08:36:07 -0400 (AST)
Received: from filer (c-24-6-183-218.client.comcast.net [24.6.183.218])
	by svr1.postgresql.org (Postfix) with ESMTP id 4ACFBD1D54E
	for <pgsql-hackers@postgresql.org>; Sat, 10 Jan 2004 08:36:03 -0400 (AST)
Received: from localhost (localhost [127.0.0.1])
  (uid 1000)
  by filer with local; Sat, 10 Jan 2004 04:36:06 -0800
Date: Sat, 10 Jan 2004 04:36:06 -0800
From: Kevin Brown <kevin@sysexperts.com>
To: PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] psql \d option list overloaded
Message-ID: <20040110123605.GA2608@filer>
Mail-Followup-To: Kevin Brown <kevin@sysexperts.com>,
	PostgreSQL-development <pgsql-hackers@postgresql.org>
References: <3FEE6DFB.9040408@lorenso.com> <200401040125.i041PLR14687@candle.pha.pa.us> <20040104191322.GD8524@posixnap.net> <3FF8C4E6.9090008@lorenso.com> <20040105154534.GF8524@posixnap.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
In-Reply-To: <20040105154534.GF8524@posixnap.net>
Organization: Frobozzco International
User-Agent: Mutt/1.5.4i
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Mailing-List: pgsql-hackers
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
X-AntiAbuse: This header was added to track abuse, please include it with any abuse report
X-AntiAbuse: Primary Hostname - noon.pghoster.com
X-AntiAbuse: Original Domain - candle.pha.pa.us
X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12]
X-AntiAbuse: Sender Address Domain - postgresql.org
X-Spam-Checker-Version: SpamAssassin 2.61 (1.212.2.1-2003-12-09-exp) on 
	candle.pha.pa.us
X-Spam-Status: No, hits=-4.8 required=5.0 tests=BAYES_00,HTML_MESSAGE 
	autolearn=ham version=2.61
Status: OR

Alex J. Avriette wrote:
> On Sun, Jan 04, 2004 at 07:59:02PM -0600, D. Dante Lorenso wrote:
> 
> > 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'.
> 
> What makes sense to me in csh doesn't make sense in a bourne shell.

And yet, bash has !$ and job control just like csh, even though they're
not standard Bourne-shell features.

It's not a bad thing to adopt good ideas from other projects.

> You can't expect all applications to work correctly. 

You can't expect this anyway, at least when dealing with cross-database
applications.  The intersection of the SQL feature sets across all the
major database engines is pretty limited -- small enough that you'll
almost certainly end up using something database-specific when attempting
to do anything truly nontrivial.

> I'd like to second
> Peter's "yep" when asked if he could remember all the various \d*
> commands. It really comes down to whether you're trying. New software
> (even though you may have been using it for a year) requires some
> adjustment.

This is true, but it's no argument against implementing "show
databases", "show tables", and "describe".

Every database engine is different, but in the case of PG it makes sense
to adopt the best methods we can find.  A consistent and easy to
remember way of showing the various entities in psql (at the very least)
would be of great advantage.  It's something that MySQL gets right.  As
it turns out, we already have "SHOW" in psql and it's used for something
else.  So we might instead use something else (e.g. "VIEW") instead.

Either way, a single command that takes as its argument the type of entity
you want to see would be extremely useful, and much easier to remember
than what we currently have -- because the names of the entities that
are available are already well-defined and are likely known to the user
already.

> > 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!
> 
> Yes, I do it frequently. You may notice a recent post of mine used
> exactly that output.

Now do it from within psql.

It's \l, as it turns out.  This violates the principle of least surprise
because psql generally uses \d* to show entities.

> > 3) like MySQL does it...
> > 
> >    SHOW TABLES;
> 
> Should postgres also support the '#' comment? What other non-sql
> sqlisms should we support?

PG already has a number of PG-specific features.  Adding more,
*especially* if they happen to be compatible with other databases, isn't
going to hurt much.

No, the thing to worry about here is whether or not these commands
("SHOW", for instance) will appear in the SQL spec and will have a
completely different meaning from the meaning in PG.  Also of concern is
that "SHOW" is already reserved and used for something else.  We'd have
to use something other than "SHOW" for the purpose being discussed.

> > 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
> 
> I would argue that these are not "sql extensions" at all. If you like, I
> can go over the source to verify this myself, but my guess is that MySQL
> is doing exactly what postgres is doing, and evaluating this as a macro.

No, they are built into MySQL's backend parser.  You can easily verify
this by executing these commands from within Perl or Python.  They
return a table just like any other SQL command that returns data.

> Furthermore, databases are not designed for "newbies" to jump right in
> with both feet. They are designed to be robust and stable. 

Now this is ludicrous.  Yes, they're designed to be robust and stable,
but that has absolutely nothing to do with how easy they are to use.

> Additionally,
> some SQL compliance is nice. After that, you work on features. 

If we were talking about something that went against the SQL standard
then I would agree with you.  But we're talking about something that,
as far as I know, isn't in the SQL standard at all.  Implementing it
won't make us noncompliant with the SQL standard any more than the
implementation of CREATE INDEX has.

> Changing the interface so that you or others don't have to read the 
> documentation smacks of laziness. 

Really?  One could make the same argument for standards of any kind,
yes?  :-)

> Somebody like Bruce, Peter, or Tom (or indeed somebody else) is going
> to waste yet more time making things like this available to somebody
> who probably won't read any of the other documentation either, and will
> wind up on irc pestering somebody like myself, Dave, or Neil. Why is
> this progress?

It's progress because it will keep those people from pestering someone
in the know about how to show the available databases, or how to
describe a table.

> > 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 ?
> 
> We've been over this. It's \d*. 

For listing databases it's \l.  Not exactly consistent with the rest of
the related psql commands.

> > 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.
> 
> What really frightens me here is that I know of several applications (shudder,
> "LAMP" applications) which use the output of "show tables" or other of your
> "extensions." The problem with this is precisely that it /isn't/ sql, and it
> can't be supported as a static command. 

Of course not.  But applications which rely on information such as that
provided by "show tables" will typically not be possible to write while
adhering to the feature intersection of all major databases anyway.

> It is intended to be there for people
> to use interactively. 

Nonsense.  It's there to be used.  Whether it's used interactively or
not is irrelevant.  The command provides useful information.  But see
below.

> Making "pseudo sql" will encourage more developers to
> (and I'd apologize for this if it weren't true) code in Postgres the same
> lazy way they code in MySQL.

This is a strawman argument, although I understand your concern here.
To be honest, for application development I'd much rather see people
use information_schema, but that's only because information_schema is
in the SQL standard and as such should be the preferred way to retrieve
the information that the "SHOW" commands in MySQL return.  That said,
the inclusion of information_schema is a very recent development on the
PostgreSQL side of things, and doesn't even exist on some other major
databases such as MSSQL.

Of course, a PG equivalent to MySQL's "show" would be an even more
recent development...  :-)


-- 
Kevin Brown					      kevin@sysexperts.com

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match

From pgsql-hackers-owner+M48947@postgresql.org Sat Jan 10 08:12:39 2004
Return-path: <pgsql-hackers-owner+M48947@postgresql.org>
Received: from hosting.commandprompt.com (216.commandprompt.com [207.173.200.216])
	by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id i0ADCbX21504
	for <pgman@candle.pha.pa.us>; Sat, 10 Jan 2004 08:12:38 -0500 (EST)
Received: from postgresql.org (svr1.postgresql.org [200.46.204.71])
	by hosting.commandprompt.com (8.11.6/8.11.6) with ESMTP id i0AD8Gk19951;
	Sat, 10 Jan 2004 05:09:17 -0800
X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 7758BD1D54C
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>; Sat, 10 Jan 2004 13:08:00 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 02466-03
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>;
	Sat, 10 Jan 2004 09:07:32 -0400 (AST)
Received: from curie.credativ.org (credativ.com [217.160.209.18])
	by svr1.postgresql.org (Postfix) with ESMTP id 64975D1D54E
	for <pgsql-hackers@postgresql.org>; Sat, 10 Jan 2004 09:07:28 -0400 (AST)
Received: from localhost (localhost [127.0.0.1])
	by curie.credativ.org (Postfix) with ESMTP
	id 765E356243; Sat, 10 Jan 2004 14:07:25 +0100 (CET)
Received: from colt.pezone.net (dsl-213-023-254-001.arcor-ip.net [213.23.254.1])
	(using TLSv1 with cipher RC4-MD5 (128/128 bits))
	(No client certificate requested)
	by curie.credativ.org (Postfix) with ESMTP
	id B98A65623C; Sat, 10 Jan 2004 14:07:24 +0100 (CET)
From: Peter Eisentraut <peter_e@gmx.net>
To: Kevin Brown <kevin@sysexperts.com>,
   PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] psql \d option list overloaded
Date: Sat, 10 Jan 2004 14:07:24 +0100
User-Agent: KMail/1.5.1
References: <3FEE6DFB.9040408@lorenso.com> <20040105154534.GF8524@posixnap.net> <20040110123605.GA2608@filer>
In-Reply-To: <20040110123605.GA2608@filer>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-ID: <200401101407.24766.peter_e@gmx.net>
X-Virus-Scanned: by AMaViS at credativ.com
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Mailing-List: pgsql-hackers
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
X-Spam-Checker-Version: SpamAssassin 2.61 (1.212.2.1-2003-12-09-exp) on 
	candle.pha.pa.us
X-Spam-Status: No, hits=-4.9 required=5.0 tests=BAYES_00 autolearn=ham 
	version=2.61
Status: OR

Kevin Brown wrote:
> Every database engine is different, but in the case of PG it makes
> sense to adopt the best methods we can find.  A consistent and easy
> to remember way of showing the various entities in psql (at the very
> least) would be of great advantage.  It's something that MySQL gets
> right.  As it turns out, we already have "SHOW" in psql and it's used
> for something else.  So we might instead use something else (e.g.
> "VIEW") instead.

What is wrong with

SELECT * FROM information_schema.tables;

?  If it's too much to type, put information_schema in the path.  This 
syntax has the advantage that you can use qualifications and other SQL 
features.  And you can build customized views on top of it.  Does SHOW 
TABLES or whatever it might be called support that?


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match

From pgsql-hackers-owner+M48948@postgresql.org Sat Jan 10 11:30:24 2004
Return-path: <pgsql-hackers-owner+M48948@postgresql.org>
Received: from noon.pghoster.com (noon.pghoster.com [64.246.0.64])
	by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id i0AGUMX12749
	for <pgman@candle.pha.pa.us>; Sat, 10 Jan 2004 11:30:23 -0500 (EST)
Received: from svr1.postgresql.org ([200.46.204.71] helo=postgresql.org)
	by noon.pghoster.com with esmtp (Exim 4.24)
	id 1AfLzP-0000hA-JY; Sat, 10 Jan 2004 10:29:31 -0600
X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id A69DED1B51D
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>; Sat, 10 Jan 2004 16:28:00 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 14752-08
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>;
	Sat, 10 Jan 2004 12:27:29 -0400 (AST)
Received: from zigo.dhs.org (as2-4-3.an.g.bonet.se [194.236.34.191])
	by svr1.postgresql.org (Postfix) with ESMTP id 89156D1D560
	for <pgsql-hackers@postgresql.org>; Sat, 10 Jan 2004 12:27:23 -0400 (AST)
Received: from zigo.zigo.dhs.org (zigo.zigo.dhs.org [192.168.0.1])
	by zigo.dhs.org (Postfix) with ESMTP
	id 9D7EF8E0D; Sat, 10 Jan 2004 17:27:21 +0100 (CET)
Date: Sat, 10 Jan 2004 17:27:21 +0100 (CET)
From: =?ISO-8859-1?Q?Dennis_Bj=F6rklund?= <db@zigo.dhs.org>
To: Peter Eisentraut <peter_e@gmx.net>
cc: Kevin Brown <kevin@sysexperts.com>,
   PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] psql \d option list overloaded
In-Reply-To: <200401101407.24766.peter_e@gmx.net>
Message-ID: <Pine.LNX.4.44.0401101720500.13405-100000@zigo.dhs.org>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=ISO-8859-1
Content-Transfer-Encoding: 8BIT
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Mailing-List: pgsql-hackers
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
X-AntiAbuse: This header was added to track abuse, please include it with any abuse report
X-AntiAbuse: Primary Hostname - noon.pghoster.com
X-AntiAbuse: Original Domain - candle.pha.pa.us
X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12]
X-AntiAbuse: Sender Address Domain - postgresql.org
X-Spam-Checker-Version: SpamAssassin 2.61 (1.212.2.1-2003-12-09-exp) on 
	candle.pha.pa.us
X-Spam-Status: No, hits=-4.9 required=5.0 tests=BAYES_00 autolearn=ham 
	version=2.61
Status: OR

On Sat, 10 Jan 2004, Peter Eisentraut wrote:

> > to remember way of showing the various entities in psql (at the very
> > least) would be of great advantage.  It's something that MySQL gets
> > right.  As it turns out, we already have "SHOW" in psql and it's used
> > for something else.
> 
> What is wrong with
> 
> SELECT * FROM information_schema.tables;

The result is very hard to read since it's so much of it (try column
instead of tables). The \xx commands do some nice formatting you don't 
get from the above.

I would rather have long commands so one can write

\describe_table foo

and have the tab completion work for these of course (only for the long 
commands, the \dt and such does not belong in completion).

The information schema is nice, but it's not what I want to use at the 
prompt to view the content of the database.

-- 
/Dennis Bj�rklund


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

From pgsql-hackers-owner+M48950@postgresql.org Sat Jan 10 13:30:54 2004
Return-path: <pgsql-hackers-owner+M48950@postgresql.org>
Received: from noon.pghoster.com (noon.pghoster.com [64.246.0.64])
	by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id i0AIUrX25980
	for <pgman@candle.pha.pa.us>; Sat, 10 Jan 2004 13:30:53 -0500 (EST)
Received: from svr1.postgresql.org ([200.46.204.71] helo=postgresql.org)
	by noon.pghoster.com with esmtp (Exim 4.24)
	id 1AfNsG-0002v6-Lh; Sat, 10 Jan 2004 12:30:17 -0600
X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 92952D1D56E
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>; Sat, 10 Jan 2004 18:28:45 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 33571-06
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>;
	Sat, 10 Jan 2004 14:28:14 -0400 (AST)
Received: from smtp.istop.com (dci.doncaster.on.ca [66.11.168.194])
	by svr1.postgresql.org (Postfix) with ESMTP id 9FBA4D1D572
	for <pgsql-hackers@postgresql.org>; Sat, 10 Jan 2004 14:28:13 -0400 (AST)
Received: from stark.xeocode.com (gsstark.mtl.istop.com [66.11.160.162])
	by smtp.istop.com (Postfix) with ESMTP
	id 1CC0837658; Sat, 10 Jan 2004 13:28:09 -0500 (EST)
Received: from localhost
	([127.0.0.1] helo=stark.xeocode.com ident=foobar)
	by stark.xeocode.com with smtp (Exim 3.36 #1 (Debian))
	id 1AfNqD-0001y1-00; Sat, 10 Jan 2004 13:28:09 -0500
To: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] psql \d option list overloaded
References: <Pine.LNX.4.44.0401101720500.13405-100000@zigo.dhs.org>
In-Reply-To: <Pine.LNX.4.44.0401101720500.13405-100000@zigo.dhs.org>
From: Greg Stark <gsstark@mit.edu>
Organization: The Emacs Conspiracy; member since 1992
Date: 10 Jan 2004 13:28:08 -0500
Message-ID: <878ykf4q13.fsf@stark.xeocode.com>
Lines: 22
User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Mailing-List: pgsql-hackers
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
X-AntiAbuse: This header was added to track abuse, please include it with any abuse report
X-AntiAbuse: Primary Hostname - noon.pghoster.com
X-AntiAbuse: Original Domain - candle.pha.pa.us
X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12]
X-AntiAbuse: Sender Address Domain - postgresql.org
X-Spam-Checker-Version: SpamAssassin 2.61 (1.212.2.1-2003-12-09-exp) on 
	candle.pha.pa.us
X-Spam-Status: No, hits=-4.9 required=5.0 tests=BAYES_00 autolearn=ham 
	version=2.61
Status: OR


Dennis Bj�rklund <db@zigo.dhs.org> writes:

> I would rather have long commands so one can write
> 
> \describe_table foo

I would think it would be better to keep everything under a single command and
have a 1-1 correspondence to \d. Ie, just add a long form syntax following the
existing \d. \d would become just an obvious set of abbreviations. 

So for example:

\describe table foo => \dt foo
\describe index foo => \di foo
\describe aggregate foo => \da foo
\describe operator foo => \do foo

...

-- 
greg


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org

From pgsql-hackers-owner+M48952@postgresql.org Sat Jan 10 14:17:26 2004
Return-path: <pgsql-hackers-owner+M48952@postgresql.org>
Received: from noon.pghoster.com (noon.pghoster.com [64.246.0.64])
	by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id i0AJHOX29152
	for <pgman@candle.pha.pa.us>; Sat, 10 Jan 2004 14:17:25 -0500 (EST)
Received: from svr1.postgresql.org ([200.46.204.71] helo=postgresql.org)
	by noon.pghoster.com with esmtp (Exim 4.24)
	id 1AfObD-0003wE-9N; Sat, 10 Jan 2004 13:16:43 -0600
X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 27045D1B498
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>; Sat, 10 Jan 2004 19:15:51 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 42506-02
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>;
	Sat, 10 Jan 2004 15:15:21 -0400 (AST)
Received: from sss.pgh.pa.us (unknown [192.204.191.242])
	by svr1.postgresql.org (Postfix) with ESMTP id 143C4D1B4C5
	for <pgsql-hackers@postgresql.org>; Sat, 10 Jan 2004 15:15:20 -0400 (AST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id i0AJFJ19011695;
	Sat, 10 Jan 2004 14:15:19 -0500 (EST)
To: Greg Stark <gsstark@mit.edu>
cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] psql \d option list overloaded 
In-Reply-To: <878ykf4q13.fsf@stark.xeocode.com> 
References: <Pine.LNX.4.44.0401101720500.13405-100000@zigo.dhs.org> <878ykf4q13.fsf@stark.xeocode.com>
Comments: In-reply-to Greg Stark <gsstark@mit.edu>
	message dated "10 Jan 2004 13:28:08 -0500"
Date: Sat, 10 Jan 2004 14:15:19 -0500
Message-ID: <11694.1073762119@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Mailing-List: pgsql-hackers
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
X-AntiAbuse: This header was added to track abuse, please include it with any abuse report
X-AntiAbuse: Primary Hostname - noon.pghoster.com
X-AntiAbuse: Original Domain - candle.pha.pa.us
X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12]
X-AntiAbuse: Sender Address Domain - postgresql.org
X-Spam-Checker-Version: SpamAssassin 2.61 (1.212.2.1-2003-12-09-exp) on 
	candle.pha.pa.us
X-Spam-Status: No, hits=-4.9 required=5.0 tests=BAYES_00 autolearn=ham 
	version=2.61
Status: OR

Greg Stark <gsstark@mit.edu> writes:
> So for example:

> \describe table foo => \dt foo
> \describe index foo => \di foo
> \describe aggregate foo => \da foo
> \describe operator foo => \do foo

It doesn't seem to me that this buys much except verboseness, though.

ISTM there are three fundamental problems with \d and friends:

	1. Some people have a hard time remembering the commands.
	2. Some people aren't using psql.
	3. psql keeps breaking across backend versions because the
	   needed commands change.

I don't see a lot of value in addressing just one of these problem
areas, when we could instead do something that addresses all three.

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match

From pgsql-hackers-owner+M48954@postgresql.org Sat Jan 10 19:19:30 2004
Return-path: <pgsql-hackers-owner+M48954@postgresql.org>
Received: from noon.pghoster.com (noon.pghoster.com [64.246.0.64])
	by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id i0B0JPX16197
	for <pgman@candle.pha.pa.us>; Sat, 10 Jan 2004 19:19:29 -0500 (EST)
Received: from svr1.postgresql.org ([200.46.204.71] helo=postgresql.org)
	by noon.pghoster.com with esmtp (Exim 4.24)
	id 1AfTJQ-0000BC-Jg; Sat, 10 Jan 2004 18:18:40 -0600
X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 17450D1B465
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>; Sun, 11 Jan 2004 00:17:39 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 71332-04
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>;
	Sat, 10 Jan 2004 20:17:10 -0400 (AST)
Received: from lakemtao03.cox.net (lakemtao03.cox.net [68.1.17.242])
	by svr1.postgresql.org (Postfix) with ESMTP id 937E9D1B461
	for <pgsql-hackers@postgresql.org>; Sat, 10 Jan 2004 20:17:06 -0400 (AST)
Received: from louche.swelter.net ([68.98.183.165]) by lakemtao03.cox.net
          (InterMail vM.5.01.06.05 201-253-122-130-105-20030824) with ESMTP
          id <20040111001710.UTGO2192.lakemtao03.cox.net@louche.swelter.net>
          for <pgsql-hackers@postgresql.org>;
          Sat, 10 Jan 2004 19:17:10 -0500
Received: by louche.swelter.net (Postfix, from userid 513)
	id 0B28AB; Sat, 10 Jan 2004 19:16:59 -0500 (EST)
Received: from localhost (louche.swelter.net [127.0.0.1])
	by louche.swelter.net (Postfix) with ESMTP id 22CDBB
	for <pgsql-hackers@postgresql.org>; Sat, 10 Jan 2004 19:16:58 -0500 (EST)
Date: Sun, 11 Jan 2004 00:16:58 +0000 (UTC)
From: Jon Jensen <jon@endpoint.com>
X-X-Sender: jon@louche.swelter.net
To: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] psql \d option list overloaded 
In-Reply-To: <11694.1073762119@sss.pgh.pa.us>
Message-ID: <Pine.LNX.4.58.0401110014230.1302@louche.swelter.net>
References: <Pine.LNX.4.44.0401101720500.13405-100000@zigo.dhs.org>
	<878ykf4q13.fsf@stark.xeocode.com> <11694.1073762119@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Mailing-List: pgsql-hackers
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
X-AntiAbuse: This header was added to track abuse, please include it with any abuse report
X-AntiAbuse: Primary Hostname - noon.pghoster.com
X-AntiAbuse: Original Domain - candle.pha.pa.us
X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12]
X-AntiAbuse: Sender Address Domain - postgresql.org
X-Spam-Checker-Version: SpamAssassin 2.61 (1.212.2.1-2003-12-09-exp) on 
	candle.pha.pa.us
X-Spam-Status: No, hits=-4.9 required=5.0 tests=BAYES_00 autolearn=ham 
	version=2.61
Status: OR

On Sat, 10 Jan 2004, Tom Lane wrote:

> ISTM there are three fundamental problems with \d and friends:
> 
> 	1. Some people have a hard time remembering the commands.
> 	2. Some people aren't using psql.
> 	3. psql keeps breaking across backend versions because the
> 	   needed commands change.
> 
> I don't see a lot of value in addressing just one of these problem
> areas, when we could instead do something that addresses all three.

I agree, at least for #2 and #3. But I just don't understand #1. Anything
is hard to remember when you're just starting to learn it. But it's still
faster to type \? <CR> then \dt than it is to type "show tables". And
"show tables" is hard (relatively speaking) for me to remember because I'm
used to psql's way of doing things, since I mostly use it.

Jon

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

From pgsql-hackers-owner+M48955@postgresql.org Sat Jan 10 20:09:33 2004
Return-path: <pgsql-hackers-owner+M48955@postgresql.org>
Received: from noon.pghoster.com (noon.pghoster.com [64.246.0.64])
	by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id i0B19WX22168
	for <pgman@candle.pha.pa.us>; Sat, 10 Jan 2004 20:09:33 -0500 (EST)
Received: from svr1.postgresql.org ([200.46.204.71] helo=postgresql.org)
	by noon.pghoster.com with esmtp (Exim 4.24)
	id 1AfU65-000194-6w; Sat, 10 Jan 2004 19:08:57 -0600
X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id B6F12D1B528
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>; Sun, 11 Jan 2004 01:07:56 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 70575-10
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>;
	Sat, 10 Jan 2004 21:07:28 -0400 (AST)
Received: from curie.credativ.org (credativ.com [217.160.209.18])
	by svr1.postgresql.org (Postfix) with ESMTP id EA5B5D1B48B
	for <pgsql-hackers@postgresql.org>; Sat, 10 Jan 2004 21:07:24 -0400 (AST)
Received: from localhost (localhost [127.0.0.1])
	by curie.credativ.org (Postfix) with ESMTP
	id DD86C56243; Sun, 11 Jan 2004 02:07:26 +0100 (CET)
Received: from colt.pezone.net (dsl-213-023-254-001.arcor-ip.net [213.23.254.1])
	(using TLSv1 with cipher RC4-MD5 (128/128 bits))
	(No client certificate requested)
	by curie.credativ.org (Postfix) with ESMTP
	id EA9C35623C; Sun, 11 Jan 2004 02:07:25 +0100 (CET)
From: Peter Eisentraut <peter_e@gmx.net>
To: Tom Lane <tgl@sss.pgh.pa.us>, Greg Stark <gsstark@mit.edu>
Subject: Re: [HACKERS] psql \d option list overloaded
Date: Sun, 11 Jan 2004 02:07:25 +0100
User-Agent: KMail/1.5.1
cc: pgsql-hackers@postgresql.org
References: <Pine.LNX.4.44.0401101720500.13405-100000@zigo.dhs.org> <878ykf4q13.fsf@stark.xeocode.com> <11694.1073762119@sss.pgh.pa.us>
In-Reply-To: <11694.1073762119@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-15"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-ID: <200401110207.26299.peter_e@gmx.net>
X-Virus-Scanned: by AMaViS at credativ.com
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Mailing-List: pgsql-hackers
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
X-AntiAbuse: This header was added to track abuse, please include it with any abuse report
X-AntiAbuse: Primary Hostname - noon.pghoster.com
X-AntiAbuse: Original Domain - candle.pha.pa.us
X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12]
X-AntiAbuse: Sender Address Domain - postgresql.org
X-Spam-Checker-Version: SpamAssassin 2.61 (1.212.2.1-2003-12-09-exp) on 
	candle.pha.pa.us
X-Spam-Status: No, hits=-4.9 required=5.0 tests=BAYES_00 autolearn=ham 
	version=2.61
Status: OR

Tom Lane wrote:
> 	2. Some people aren't using psql.

I don't see why this is an issue.  People not using psql are either 
using a GUI, which presumably supports plenty of "show" and "describe" 
functionality, or they're writing their own program, in which case it 
doesn't really matter how short or easy to remember the commands are.


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

From pgsql-hackers-owner+M48956@postgresql.org Sat Jan 10 20:19:59 2004
Return-path: <pgsql-hackers-owner+M48956@postgresql.org>
Received: from hosting.commandprompt.com (216.commandprompt.com [207.173.200.216])
	by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id i0B1JoX23197
	for <pgman@candle.pha.pa.us>; Sat, 10 Jan 2004 20:19:56 -0500 (EST)
Received: from postgresql.org (svr1.postgresql.org [200.46.204.71])
	by hosting.commandprompt.com (8.11.6/8.11.6) with ESMTP id i0B1E2k19636;
	Sat, 10 Jan 2004 17:15:07 -0800
X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 34FE7D1B436
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>; Sun, 11 Jan 2004 01:13:46 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 76096-05
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>;
	Sat, 10 Jan 2004 21:13:18 -0400 (AST)
Received: from curie.credativ.org (credativ.com [217.160.209.18])
	by svr1.postgresql.org (Postfix) with ESMTP id 56B90D1B430
	for <pgsql-hackers@postgresql.org>; Sat, 10 Jan 2004 21:13:15 -0400 (AST)
Received: from localhost (localhost [127.0.0.1])
	by curie.credativ.org (Postfix) with ESMTP
	id 91B2A56243; Sun, 11 Jan 2004 02:13:18 +0100 (CET)
Received: from colt.pezone.net (dsl-213-023-254-001.arcor-ip.net [213.23.254.1])
	(using TLSv1 with cipher RC4-MD5 (128/128 bits))
	(No client certificate requested)
	by curie.credativ.org (Postfix) with ESMTP
	id 7D86F5623C; Sun, 11 Jan 2004 02:13:17 +0100 (CET)
From: Peter Eisentraut <peter_e@gmx.net>
To: Dennis =?iso-8859-1?q?Bj=F6rklund?= <db@zigo.dhs.org>
Subject: Re: [HACKERS] psql \d option list overloaded
Date: Sun, 11 Jan 2004 02:13:17 +0100
User-Agent: KMail/1.5.1
cc: Kevin Brown <kevin@sysexperts.com>,
   PostgreSQL-development <pgsql-hackers@postgresql.org>
References: <Pine.LNX.4.44.0401101720500.13405-100000@zigo.dhs.org>
In-Reply-To: <Pine.LNX.4.44.0401101720500.13405-100000@zigo.dhs.org>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 8bit
Content-Disposition: inline
Message-ID: <200401110213.17581.peter_e@gmx.net>
X-Virus-Scanned: by AMaViS at credativ.com
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Mailing-List: pgsql-hackers
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
X-Spam-Checker-Version: SpamAssassin 2.61 (1.212.2.1-2003-12-09-exp) on 
	candle.pha.pa.us
X-Spam-Status: No, hits=-4.9 required=5.0 tests=BAYES_00 autolearn=ham 
	version=2.61
Status: OR

Dennis Bj�rklund wrote:
> > What is wrong with
> >
> > SELECT * FROM information_schema.tables;
>
> The result is very hard to read since it's so much of it (try column
> instead of tables). The \xx commands do some nice formatting you
> don't get from the above.

This is an interesting point to remember for those that are advocating 
pushing psql's queries into the backend.  psql's queries are optimized 
for monospaced text screens of limited size.  Unless someone else is 
writing a command-line client, there would be little reuse effect, 
because any given application will have different display requirements.

(Another problem with pushing psql's queries into the backend is that 
much of the output that psql makes is not a single table.  Sometimes 
there is more than one table, or the information is in the table 
footers.  It'd be quite complicated to make the backend produce those 
kinds of displays.)


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

From pgsql-hackers-owner+M48957@postgresql.org Sun Jan 11 04:34:27 2004
Return-path: <pgsql-hackers-owner+M48957@postgresql.org>
Received: from hosting.commandprompt.com (216.commandprompt.com [207.173.200.216])
	by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id i0B9YPX11678
	for <pgman@candle.pha.pa.us>; Sun, 11 Jan 2004 04:34:26 -0500 (EST)
Received: from postgresql.org (svr1.postgresql.org [200.46.204.71])
	by hosting.commandprompt.com (8.11.6/8.11.6) with ESMTP id i0B9U9k04632;
	Sun, 11 Jan 2004 01:31:12 -0800
X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id C49AFD1D555
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>; Sun, 11 Jan 2004 09:29:54 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 26981-08
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>;
	Sun, 11 Jan 2004 05:29:25 -0400 (AST)
Received: from zigo.dhs.org (as2-4-3.an.g.bonet.se [194.236.34.191])
	by svr1.postgresql.org (Postfix) with ESMTP id A75DDD1D27D
	for <pgsql-hackers@postgresql.org>; Sun, 11 Jan 2004 05:29:22 -0400 (AST)
Received: from zigo.zigo.dhs.org (zigo.zigo.dhs.org [192.168.0.1])
	by zigo.dhs.org (Postfix) with ESMTP
	id B8DED8E0D; Sun, 11 Jan 2004 10:29:23 +0100 (CET)
Date: Sun, 11 Jan 2004 10:29:23 +0100 (CET)
From: Dennis Bjorklund <db@zigo.dhs.org>
To: Peter Eisentraut <peter_e@gmx.net>
cc: Kevin Brown <kevin@sysexperts.com>,
   PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] psql \d option list overloaded
In-Reply-To: <200401110213.17581.peter_e@gmx.net>
Message-ID: <Pine.LNX.4.44.0401111012020.13405-100000@zigo.dhs.org>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=ISO-8859-1
Content-Transfer-Encoding: 8BIT
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Mailing-List: pgsql-hackers
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
X-Spam-Checker-Version: SpamAssassin 2.61 (1.212.2.1-2003-12-09-exp) on 
	candle.pha.pa.us
X-Spam-Status: No, hits=-4.9 required=5.0 tests=BAYES_00 autolearn=ham 
	version=2.61
Status: OR

On Sun, 11 Jan 2004, Peter Eisentraut wrote:

> Another problem with pushing psql's queries into the backend is that 
> much of the output that psql makes is not a single table.  Sometimes 
> there is more than one table, or the information is in the table 
> footers.

Yes, pushing the \xx commands into the server makes no sense to me at all.  

The commands in psql are very specific for psql. I don't see why you ever
want to do SHOW TABLES except at the command line in psql. If your
application wants to find all tables in the database, then we have the
standard sql way, which is the information schema.

The argument that "show tables" is easier to remember then \dt might be 
true, but to me that just means that we should make psql better by adding 
\describe_table and such, not to push psql code into the server.

Making a couple of views that are pg specific to make it easier to get
information out could be good however. The information schema does not
always contain all information one might want. Making specialised SQL
commands for it I'm not in favor of at all.

-- 
/Dennis Bj�rklund


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

From pgsql-hackers-owner+M48965@postgresql.org Sun Jan 11 12:20:30 2004
Return-path: <pgsql-hackers-owner+M48965@postgresql.org>
Received: from noon.pghoster.com (noon.pghoster.com [64.246.0.64])
	by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id i0BHKTX05825
	for <pgman@candle.pha.pa.us>; Sun, 11 Jan 2004 12:20:29 -0500 (EST)
Received: from svr1.postgresql.org ([200.46.204.71] helo=postgresql.org)
	by noon.pghoster.com with esmtp (Exim 4.24)
	id 1AfjFg-00073z-AN; Sun, 11 Jan 2004 11:19:52 -0600
X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 46DAED1B4AD
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>; Sun, 11 Jan 2004 17:18:57 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 77771-03
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>;
	Sun, 11 Jan 2004 13:18:26 -0400 (AST)
Received: from sss.pgh.pa.us (unknown [192.204.191.242])
	by svr1.postgresql.org (Postfix) with ESMTP id 4F237D1B51D
	for <pgsql-hackers@postgresql.org>; Sun, 11 Jan 2004 13:18:25 -0400 (AST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id i0BHI519016986;
	Sun, 11 Jan 2004 12:18:05 -0500 (EST)
To: Peter Eisentraut <peter_e@gmx.net>
cc: Greg Stark <gsstark@mit.edu>, pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] psql \d option list overloaded 
In-Reply-To: <200401110207.26299.peter_e@gmx.net> 
References: <Pine.LNX.4.44.0401101720500.13405-100000@zigo.dhs.org> <878ykf4q13.fsf@stark.xeocode.com> <11694.1073762119@sss.pgh.pa.us> <200401110207.26299.peter_e@gmx.net>
Comments: In-reply-to Peter Eisentraut <peter_e@gmx.net>
	message dated "Sun, 11 Jan 2004 02:07:25 +0100"
Date: Sun, 11 Jan 2004 12:18:05 -0500
Message-ID: <16985.1073841485@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Mailing-List: pgsql-hackers
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
X-AntiAbuse: This header was added to track abuse, please include it with any abuse report
X-AntiAbuse: Primary Hostname - noon.pghoster.com
X-AntiAbuse: Original Domain - candle.pha.pa.us
X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12]
X-AntiAbuse: Sender Address Domain - postgresql.org
X-Spam-Checker-Version: SpamAssassin 2.61 (1.212.2.1-2003-12-09-exp) on 
	candle.pha.pa.us
X-Spam-Status: No, hits=-4.9 required=5.0 tests=BAYES_00 autolearn=ham 
	version=2.61
Status: OR

Peter Eisentraut <peter_e@gmx.net> writes:
> Tom Lane wrote:
>> 2. Some people aren't using psql.

> I don't see why this is an issue.  People not using psql are either 
> using a GUI, which presumably supports plenty of "show" and "describe" 
> functionality, or they're writing their own program, in which case it 
> doesn't really matter how short or easy to remember the commands are.

But this interacts with point 3 (psql breaks on every new backend
version).  It's not desirable to have every GUI and large custom program
implementing its own set of metadata inquiry commands: they all have
to go through the same update pain as psql.  Perhaps if people start to
rely on information_schema for those things, life will get better,
but I'm unconvinced that will happen.  psql itself certainly hasn't
moved in that direction.

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

From pgsql-hackers-owner+M48981@postgresql.org Sun Jan 11 20:52:09 2004
Return-path: <pgsql-hackers-owner+M48981@postgresql.org>
Received: from noon.pghoster.com (noon.pghoster.com [64.246.0.64])
	by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id i0C1q8X05166
	for <pgman@candle.pha.pa.us>; Sun, 11 Jan 2004 20:52:09 -0500 (EST)
Received: from svr1.postgresql.org ([200.46.204.71] helo=postgresql.org)
	by noon.pghoster.com with esmtp (Exim 4.24)
	id 1AfrF5-0005Pj-Aq; Sun, 11 Jan 2004 19:51:47 -0600
X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 654B2D1B519
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>; Mon, 12 Jan 2004 01:50:40 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 57929-03
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>;
	Sun, 11 Jan 2004 21:50:12 -0400 (AST)
Received: from lakemtao04.cox.net (lakemtao04.cox.net [68.1.17.241])
	by svr1.postgresql.org (Postfix) with ESMTP id DD464D1D576
	for <pgsql-hackers@postgresql.org>; Sun, 11 Jan 2004 21:50:07 -0400 (AST)
Received: from [192.168.0.13] ([68.105.168.121]) by lakemtao04.cox.net
          (InterMail vM.5.01.06.05 201-253-122-130-105-20030824) with ESMTP
          id <20040112015012.MDVR19895.lakemtao04.cox.net@[192.168.0.13]>;
          Sun, 11 Jan 2004 20:50:12 -0500
From: Robert Treat <xzilla@users.sourceforge.net>
To: Jon Jensen <jon@endpoint.com>, pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] psql \d option list overloaded
Date: Sun, 11 Jan 2004 20:50:08 -0500
User-Agent: KMail/1.5
References: <Pine.LNX.4.44.0401101720500.13405-100000@zigo.dhs.org> <11694.1073762119@sss.pgh.pa.us> <Pine.LNX.4.58.0401110014230.1302@louche.swelter.net>
In-Reply-To: <Pine.LNX.4.58.0401110014230.1302@louche.swelter.net>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-ID: <200401112050.09142.xzilla@users.sourceforge.net>
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Mailing-List: pgsql-hackers
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
X-AntiAbuse: This header was added to track abuse, please include it with any abuse report
X-AntiAbuse: Primary Hostname - noon.pghoster.com
X-AntiAbuse: Original Domain - candle.pha.pa.us
X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12]
X-AntiAbuse: Sender Address Domain - postgresql.org
X-Spam-Checker-Version: SpamAssassin 2.61 (1.212.2.1-2003-12-09-exp) on 
	candle.pha.pa.us
X-Spam-Status: No, hits=-4.9 required=5.0 tests=BAYES_00 autolearn=ham 
	version=2.61
Status: OR

On Saturday 10 January 2004 19:16, Jon Jensen wrote:
> On Sat, 10 Jan 2004, Tom Lane wrote:
> > ISTM there are three fundamental problems with \d and friends:
> >
> > 	1. Some people have a hard time remembering the commands.
> > 	2. Some people aren't using psql.
> > 	3. psql keeps breaking across backend versions because the
> > 	   needed commands change.
> >
> > I don't see a lot of value in addressing just one of these problem
> > areas, when we could instead do something that addresses all three.
>
> I agree, at least for #2 and #3. But I just don't understand #1. Anything
> is hard to remember when you're just starting to learn it. But it's still
> faster to type \? <CR> then \dt than it is to type "show tables". And
> "show tables" is hard (relatively speaking) for me to remember because I'm
> used to psql's way of doing things, since I mostly use it.
>

I'd second this point; I've certainly stumbled over the "show" syntax when 
trying to get anything other than tables in mysql.

Robert Treat
-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

From pgsql-hackers-owner+M48993@postgresql.org Mon Jan 12 12:50:40 2004
Return-path: <pgsql-hackers-owner+M48993@postgresql.org>
Received: from noon.pghoster.com (noon.pghoster.com [64.246.0.64])
	by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id i0CHocX04973
	for <pgman@candle.pha.pa.us>; Mon, 12 Jan 2004 12:50:39 -0500 (EST)
Received: from svr1.postgresql.org ([200.46.204.71] helo=postgresql.org)
	by noon.pghoster.com with esmtp (Exim 4.24)
	id 1Ag6Ck-0008V2-E5; Mon, 12 Jan 2004 11:50:22 -0600
X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id ED886D1D17D
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>; Mon, 12 Jan 2004 17:49:16 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 17616-05
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>;
	Mon, 12 Jan 2004 13:48:46 -0400 (AST)
Received: from curie.credativ.org (credativ.com [217.160.209.18])
	by svr1.postgresql.org (Postfix) with ESMTP id 09691D1B467
	for <pgsql-hackers@postgresql.org>; Mon, 12 Jan 2004 13:48:45 -0400 (AST)
Received: from localhost (localhost [127.0.0.1])
	by curie.credativ.org (Postfix) with ESMTP
	id 45CA05626B; Mon, 12 Jan 2004 18:48:44 +0100 (CET)
Received: from colt.pezone.net (dsl-213-023-254-001.arcor-ip.net [213.23.254.1])
	(using TLSv1 with cipher RC4-MD5 (128/128 bits))
	(No client certificate requested)
	by curie.credativ.org (Postfix) with ESMTP
	id 2F90D5623C; Mon, 12 Jan 2004 18:48:43 +0100 (CET)
From: Peter Eisentraut <peter_e@gmx.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: [HACKERS] psql \d option list overloaded
Date: Mon, 12 Jan 2004 18:48:43 +0100
User-Agent: KMail/1.5.1
cc: Greg Stark <gsstark@mit.edu>, pgsql-hackers@postgresql.org
References: <Pine.LNX.4.44.0401101720500.13405-100000@zigo.dhs.org> <200401110207.26299.peter_e@gmx.net> <16985.1073841485@sss.pgh.pa.us>
In-Reply-To: <16985.1073841485@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-15"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-ID: <200401121848.43765.peter_e@gmx.net>
X-Virus-Scanned: by AMaViS at credativ.com
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Mailing-List: pgsql-hackers
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
X-AntiAbuse: This header was added to track abuse, please include it with any abuse report
X-AntiAbuse: Primary Hostname - noon.pghoster.com
X-AntiAbuse: Original Domain - candle.pha.pa.us
X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12]
X-AntiAbuse: Sender Address Domain - postgresql.org
X-Spam-Checker-Version: SpamAssassin 2.61 (1.212.2.1-2003-12-09-exp) on 
	candle.pha.pa.us
X-Spam-Status: No, hits=-4.9 required=5.0 tests=BAYES_00 autolearn=ham 
	version=2.61
Status: OR

Tom Lane wrote:
> But this interacts with point 3 (psql breaks on every new backend
> version).  It's not desirable to have every GUI and large custom
> program implementing its own set of metadata inquiry commands: they
> all have to go through the same update pain as psql.  Perhaps if
> people start to rely on information_schema for those things, life
> will get better, but I'm unconvinced that will happen.  psql itself
> certainly hasn't moved in that direction.

IIRC, the two killers in psql compatibility have been outer joins and 
schemas.  I don't see how we could have avoided that, except with 
highly specialized and static (parameter-less) commands.  There have 
been additional minor issues, but I suppose we could have avoided those 
if we had cared to do so at all.

Several people have in the past proposed to keep psql backward 
compatible, even if only by means of

if (version =x) {
   ...
}
else if (version = y) {
   ...
}

(which would be fine by me), but apparently no one has felt pressed 
enough yet.


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster