Thread: List schema contents
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 jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On Fri, Jun 09, 2006 at 02:33:57PM -0500, Jim C. Nasby wrote: > 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 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 <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
On Fri, 2006-06-09 at 14:33 -0500, Jim C. Nasby wrote: > 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. 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
"Jim C. Nasby" <jnasby@pervasive.com> writes:
> I'd like to propose that the behavior of \d schemaname. be changed to
> match
> set search_path = schemaname
> \d
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).
> One problem I see is that this will break the ability to search for
> tablename. via regex.
You are aware of the double-quoting option in \d search patterns, no?
regards, tom lane
On Fri, Jun 09, 2006 at 04:20:16PM -0400, Tom Lane wrote:
> "Jim C. Nasby" <jnasby@pervasive.com> writes:
> > I'd like to propose that the behavior of \d schemaname. be changed to
> > match
>
> > set search_path = schemaname
> > \d
>
> 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).
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 | postgres8information_schema | check_constraints | view | postgres8information_schema |
column_domain_usage | view | postgres8
...information_schema | usage_privileges | view | postgres8information_schema | view_column_usage
| view | postgres8information_schema | view_table_usage | view | postgres8information_schema |
views | view | postgres8
(40 rows)
decibel=#
> > One problem I see is that this will break the ability to search for
> > tablename. via regex.
>
> 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 jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
"Jim C. Nasby" <jnasby@pervasive.com> writes:
> 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.
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
On Fri, Jun 09, 2006 at 04:55:07PM -0400, Tom Lane wrote: > "Jim C. Nasby" <jnasby@pervasive.com> writes: > > 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. > > What you're looking for is \dt, or perhaps \dtsv or one of those forms. \dtsv produces exactly what I'd want/expect. > 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. Perhaps \d without an argument should just do whatever \dtsv does? -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
"Jim C. Nasby" <jnasby@pervasive.com> writes:
> On Fri, Jun 09, 2006 at 04:55:07PM -0400, Tom Lane wrote:
>> 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.
> 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