Subject: List schema contents

From: "Jim C. Nasby" <[email-masked]>
Date: Fri, 9 Jun 2006 14:33:57 -0500

Currently, the only way to get a listing of tables in a schema via psql
is to modify your search_path, which is both non-intuitive and a PITA.
You can do \d schemaname., but that's the equivalent of

set search_path = schemaname
\d *

I'd like to propose that the behavior of \d schemaname. be changed to
match

set search_path = schemaname
\d

That avoids issues with table name conflicts that could arise if the
trailing . wasn't required. And the old behavior is still available as
\d schemaname.*

One problem I see is that this will break the ability to search for
tablename. via regex. Since that's easy to do via tablename?, I don't
think it's a big deal, unless someone has better ideas on how to
indicate we want to list something for a specific schema.
-- 
Jim C. Nasby, Sr. Engineering Consultant      [email-masked]
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

---

From: Martijn van Oosterhout <[email-masked]>
Date: Fri, 9 Jun 2006 21:47:00 +0200

> You can do \d schemaname., but that's the equivalent of

Isn't this \dt schemaname.*?

The only irritating thing is that is only displays table, not other
objects...

Have a nice day,
-- 
Martijn van Oosterhout   <[email-masked]>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

---

From: Neil Conway <[email-masked]>
Date: Fri, 09 Jun 2006 12:52:52 -0700

> is to modify your search_path, which is both non-intuitive and a PITA.

I've griped about psql's limited support for schemas in the past:

http://archives.postgresql.org/pgsql-hackers/2004-10/msg00989.php
http://archives.postgresql.org/pgsql-hackers/2004-11/msg00014.php

I never actually got around to making any behavioral changes, but I
agree there is certainly room for improvement.

(The fact that archives.p.o can't properly link between threads that
cross month boundaries is pretty annoying...)

-Neil

---

From: Tom Lane <[email-masked]>
Date: Fri, 09 Jun 2006 16:20:16 -0400

> set search_path = schemaname

I'm not sure what your reasoning is here, but AFAICS this would move the
behavior away from what you say you want.  What exactly have you got
in mind, and why?  In particular, why do you think that "\d schemaname."
doesn't already produce exactly what you asked for, namely a list of the
tables in that schema?  The above would *not* produce such a list
(counterexample: temp tables).

> tablename. via regex.

You are aware of the double-quoting option in \d search patterns, no?

			regards, tom lane

---

From: Tom Lane <[email-masked]>
Date: Fri, 09 Jun 2006 16:22:18 -0400

> cross month boundaries is pretty annoying...)

Indeed, I was just annoyed by that (again) a few minutes ago.  Anyone
on -www have an idea how to fix it?

			regards, tom lane

---

From: "Jim C. Nasby" <[email-masked]>
Date: Fri, 9 Jun 2006 15:27:41 -0500

> (counterexample: temp tables).
 
What I'm looking for is what "\d" provides you, only limited to a
specific schema. "\d information_schema." (for example) doesn't provide
that; it provides the details for every table/view in
information_schema. Changing the search path does what I want, but is a
pain:

decibel=# set search_path=information_schema;
SET
decibel=# \d
                            List of relations
       Schema       |              Name               | Type  |   Owner   
--------------------+---------------------------------+-------+-----------
 information_schema | applicable_roles                | view  | postgres8
 information_schema | check_constraints               | view  | postgres8
 information_schema | column_domain_usage             | view  | postgres8
...
 information_schema | usage_privileges                | view  | postgres8
 information_schema | view_column_usage               | view  | postgres8
 information_schema | view_table_usage                | view  | postgres8
 information_schema | views                           | view  | postgres8
(40 rows)

decibel=# 

> You are aware of the double-quoting option in \d search patterns, no?

No, but I'm not really sure how that would help...
-- 
Jim C. Nasby, Sr. Engineering Consultant      [email-masked]
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

---

From: Bruce Momjian <[email-masked]>
Date: Fri, 9 Jun 2006 16:33:17 -0400 (EDT)

> on -www have an idea how to fix it?

I am also having problems that the date sorting often has a few entries
at the end of out of order.  I think that needs to be fixed too.

-- 
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

---

From: Tom Lane <[email-masked]>
Date: Fri, 09 Jun 2006 16:55:07 -0400

"Jim C. Nasby" <[email-masked]> writes:
> information_schema.

What you're looking for is \dt, or perhaps \dtsv or one of those forms.

I'd be the first to agree that the behavior of \d isn't particularly
orthogonal, but it's not the pattern language that's the problem, it's
the command itself.

			regards, tom lane

---

From: "Jim C. Nasby" <[email-masked]>
Date: Fri, 9 Jun 2006 16:00:01 -0500

> What you're looking for is \dt, or perhaps \dtsv or one of those forms.

\dtsv produces exactly what I'd want/expect.

> orthogonal, but it's not the pattern language that's the problem, it's

Perhaps \d without an argument should just do whatever \dtsv does?
-- 
Jim C. Nasby, Sr. Engineering Consultant      [email-masked]
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

---

From: Tom Lane <[email-masked]>
Date: Fri, 09 Jun 2006 17:08:40 -0400

> Perhaps \d without an argument should just do whatever \dtsv does?

That's exactly what it does ... without an argument.  The
non-orthogonality is that adding an argument changes the printout style,
instead of just determining which objects are displayed.  See the psql
man page:

         Note: If \d is used without a pattern argument, it is
         equivalent to \dtvs which will show a list of all 
         tables, views, and sequences. This is purely a convenience measure. 

			regards, tom lane

---

From: "Marc G. Fournier" <[email-masked]>
Date: Sat, 10 Jun 2006 02:20:05 -0300 (ADT)

> on -www have an idea how to fix it?

Someone that knows mhonarc better then I might know of a way, but, I don't 
believe cross-month linking is actually possible ...

majordomo stores archives by month in mbox format ... mhonarc processes 
each month and deposits it into a directory YYYY-MM ... without merging 
all of the mbox files into one huge archive file, and eliminating the 
YYYY-MM subdirectories, I don't believe that you could do this ...

But, as I said, someone with more knowledge about mhonarc may know a way 
of doing this ... ?

----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email . [email-masked]                              MSN . [email-masked]
Yahoo . yscrappy               Skype: hub.org        ICQ . 7615664

---

From: "Marc G. Fournier" <[email-masked]>
Date: Sat, 10 Jun 2006 02:20:27 -0300 (ADT)

> at the end of out of order.  I think that needs to be fixed too.

please provide a sample of what you are talking about ... :)

----

---

From: Bruce Momjian <[email-masked]>
Date: Sat, 10 Jun 2006 08:48:18 -0400 (EDT)

> please provide a sample of what you are talking about ... :)

Sure, search for "misnomer" and sorty by date.  Look at the last page.
You will see 2005 entries that should have appeared earlier.

---

From: "Marc G. Fournier" <[email-masked]>
Date: Sat, 10 Jun 2006 10:52:58 -0300 (ADT)

> You will see 2005 entries that should have appeared earlier.

Wait, you are talking Search results, not ordering in the archives 
themselves ... can't help you with that one, sorry ...

----

---

From: Jim Nasby <[email-masked]>
Date: Sat, 10 Jun 2006 12:57:35 -0500

On Jun 10, 2006, at 12:20 AM, Marc G. Fournier wrote:
> Someone that knows mhonarc better then I might know of a way, but,  
> I don't believe cross-month linking is actually possible ...
>
> majordomo stores archives by month in mbox format ... mhonarc  
> processes each month and deposits it into a directory YYYY-MM ...  
> without merging all of the mbox files into one huge archive file,  
> and eliminating the YYYY-MM subdirectories, I don't believe that  
> you could do this ...
>
> But, as I said, someone with more knowledge about mhonarc may know  
> a way of doing this ... ?

Are we wed to mhonarc? Is there another archiving tool we could use?
--
Jim C. Nasby, Sr. Engineering Consultant      [email-masked]
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

---

From: "Marc G. Fournier" <[email-masked]>
Date: Sat, 10 Jun 2006 15:09:32 -0300 (ADT)

On Sat, 10 Jun 2006, Jim Nasby wrote:

>> each month and deposits it into a directory YYYY-MM ... without merging all 
>> of the mbox files into one huge archive file, and eliminating the YYYY-MM 
>> subdirectories, I don't believe that you could do this ...
>> 
>> But, as I said, someone with more knowledge about mhonarc may know a way of 
>> doing this ... ?
>

I'll never say we're *wed* to anything ... do you know of another 
archiving tool?

I think the first question is are there any other ones that offer the 
functionality that Tom is asking for ... ?

----

---

From: John Hansen <[email-masked]>
Date: Thu, 15 Jun 2006 11:27:43 +1000

> You will see 2005 entries that should have appeared earlier.

Thats due to a bug in the search engine, not the archives themselves.

... John

---

From: Bruce Momjian <[email-masked]>
Date: Wed, 14 Jun 2006 21:34:08 -0400 (EDT)

> Thats due to a bug in the search engine, not the archives themselves.

OK, how does it get fixed?

---

From: John Hansen <[email-masked]>
Date: Thu, 15 Jun 2006 11:47:34 +1000

> OK, how does it get fixed?

Someone needs to fix the ASPseek source...

---

From: Bruce Momjian <[email-masked]>
Date: Wed, 14 Jun 2006 22:01:46 -0400 (EDT)

> Someone needs to fix the ASPseek source...

Have we submitted a bug report to them?

---

From: John Hansen <[email-masked]>
Date: Thu, 15 Jun 2006 12:04:21 +1000

> Have we submitted a bug report to them?

The project is more or less dead, only the userbase still exists.
And the version we're running is heavily modified.

I will get around to fixing this, I just don't have the time at present.

... John