\describe*
Some of the discussions about making psql more user friendly (more tab
completions help, exit, etc) got me thinking about other ways that psql
could be more friendly, and the one that comes to mind is our terse but
cryptic \d* commands.
I think it would be helpful and instructive to have corresponding long-form
describe commands.
Take describing schemas. Is \dn intuitive? Not really. In hindsight, you
may think "yeah, a schema is a namespace", but you never guessed 'n' on the
first try, or the second.
Looking over exec_command_d() a bit, I think it's a bit of a stretch do
have each command handle a long form like this:
\describe table my_table
or
\describe table verbose my_table
because then each \d-variant has to account for objects named "table" and
"verbose" and that's a path to unhappiness.
But if we dash-separated them, then all of the strcmps would be in the 'e'
subsection, and each one would just have to know it's long to short
translation, and call exec_command_d with the corresponding short command
describe => d
describe-verbose => d+
describe-aggregates-verbose => da+
describe-roles => du
We could even presume the verbose flag in all cases (after all, the user
was being verbose...), which would also cut down on tab-completion results,
and we could check for interactive mode and display a message like
\describe-schemas (short: \dn+)
so that the person has the opportunity to learn the corresponding short
command.
In additional to aiding tab completion discovery of the commands (i.e.
typing "\desc" and then hitting tab, it would also make scripts a little
more self-documenting.
Thoughts?
On 01/26/2018 02:11 AM, Corey Huinker wrote:
Some of the discussions about making psql more user friendly (more tab
completions help, exit, etc) got me thinking about other ways that psql
could be more friendly, and the one that comes to mind is our terse but
cryptic \d* commands.I think it would be helpful and instructive to have corresponding
long-form describe commands.Take describing schemas. Is \dn intuitive? Not really. In hindsight, you
may think "yeah, a schema is a namespace", but you never guessed 'n' on
the first try, or the second.
At first blush, I support this idea.
Looking over exec_command_d() a bit, I think it's a bit of a stretch do
have each command handle a long form like this:\describe table my_table
or
\describe table verbose my_tablebecause then each \d-variant has to account for objects named "table"
and "verbose" and that's a path to unhappiness.
We're already being verbose so we can easily require
\describe table table
for the first case, and if you move "verbose" to before the object, then
we can have
\describe verbose table verbose
So basically, the grammar would be "\describe [verbose] [system] object
name" instead of "\dXS[+] name" where X is the object.
One thing not addressed here is a long version of \ditvS+. Maybe
something like
\describe verbose system index, table, view <pattern>
But if we dash-separated them, then all of the strcmps would be in the
'e' subsection, and each one would just have to know it's long to short
translation, and call exec_command_d with the corresponding short commanddescribe => d
describe-verbose => d+
describe-aggregates-verbose => da+
describe-roles => du
-1
We could even presume the verbose flag in all cases (after all, the user
was being verbose...), which would also cut down on tab-completion
results, and we could check for interactive mode and display a message like\describe-schemas (short: \dn+)
so that the person has the opportunity to learn the corresponding short
command.
-1 on this, too.
If we presume "verbose", we need to add a "terse". If the user is
interested in the short forms, they can issue a \? like everybody else.
In additional to aiding tab completion discovery of the commands (i.e.
typing "\desc" and then hitting tab, it would also make scripts a little
more self-documenting.
I always use long versions of options when writing scripts specifically
because they are self-documenting (see 0be22457d7) so I certainly
support this argument.
Note: I am not volunteering to implement any of this, but I'll happily
review it.
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
Corey Huinker wrote:
Some of the discussions about making psql more user friendly (more tab completions help, exit, etc) got me thinking about other ways that psql could be more friendly, and the one that comes to mind is our terse but cryptic \d* commands.
I think it would be helpful and instructive to have corresponding long-form describe commands.
Take describing schemas. Is \dn intuitive? Not really. In hindsight, you may think "yeah, a schema is a namespace", but you never guessed 'n' on the first try, or the second.
Looking over exec_command_d() a bit, I think it's a bit of a stretch do have each command handle a long form like this:
\describe table my_table
or
\describe table verbose my_tablebecause then each \d-variant has to account for objects named "table" and "verbose" and that's a path to unhappiness.
But if we dash-separated them, then all of the strcmps would be in the 'e' subsection, and each one would just have to know it's long to short translation, and call exec_command_d with the corresponding short command
describe => d
describe-verbose => d+
describe-aggregates-verbose => da+
describe-roles => duWe could even presume the verbose flag in all cases (after all, the user was being verbose...), which would also cut down on tab-completion results, and we could check for interactive mode and display a message like
\describe-schemas (short: \dn+)
so that the person has the opportunity to learn the corresponding short command.
In additional to aiding tab completion discovery of the commands (i.e. typing "\desc" and then hitting tab, it would also make scripts a little more self-documenting.
Thoughts?
I'm somewhat -1 on this.
It would be about as hard to memorize \describe-schemas as it is to memorize \dn:
You'd have to remember that it is "-" and not "_", that it is "describe", not "desc"
and that it is "schemas", not "schema".
Moreover, it would be as awkward to have
\describe-schemas public
as it would be to list all schemas with
\describe-schema
But my strongest criticism is that the \d* commands are for interactive use,
and who wants to type in a long string like that? The beginner won't be able to
guess the correct command, and the experienced user would refuse to use it.
Having said all that, I can imagine that having \desc and \describe as an
alternative to \d would help beginners who come e.g. from Oracle,
but that would mean a change of the current behavior:
test=> \describe
List of foreign servers
Name | Owner | Foreign-data wrapper
--------+----------+----------------------
oracle | postgres | oracle_fdw
(1 row)
This is because \des lists foreign servers, and the rest of the command is ignored.
Yours,
Laurenz Albe
On Thu, Jan 25, 2018 at 08:11:00PM -0500, Corey Huinker wrote:
Some of the discussions about making psql more user friendly (more
tab completions help, exit, etc) got me thinking about other ways
that psql could be more friendly, and the one that comes to mind is
our terse but cryptic \d* commands.
They are indeed terse and cryptic, and what's worse, they're not
available to clients other than psql, so I propose that we do what at
least MySQL, Oracle, and DB2 do and implement DESCRIBE as its own
command.
Especially handy would be a variant DESCRIBE CREATE, which would do
what it says on the label in a copy-and-paste-able form, but that's
not strictly necessary for the first cut.
Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
On 01/26/2018 03:49 PM, David Fetter wrote:> They are indeed terse and
cryptic, and what's worse, they're not
available to clients other than psql, so I propose that we do what at
least MySQL, Oracle, and DB2 do and implement DESCRIBE as its own
command.Especially handy would be a variant DESCRIBE CREATE, which would do
what it says on the label in a copy-and-paste-able form, but that's
not strictly necessary for the first cut.
I am not fan of this since I like how easy it is to explain to beginners
that all backslash commands are processed by the client while everything
else is handled by the server. Yes, "help" is an exception, but nobody
really needs to know about that command.
As for the actually proposal I do not care strongly either way. The \d
commands are a bit cryptic and unfriendly to the occasional user, but I
am not sure that having two ways to do it would be better.
Andreas
On 01/26/2018 03:49 PM, David Fetter wrote:
I propose that we do what at least MySQL, Oracle, and DB2 do and
implement DESCRIBE as its own command.
Hard pass.
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
It would be about as hard to memorize \describe-schemas as it is to
memorize \dn:
You'd have to remember that it is "-" and not "_", that it is "describe",
not "desc"
and that it is "schemas", not "schema".
You wouldn't memorize them. You'd discover them with tab completion.
Type "\d<tab>" and you'll see
\d \dA \dc \dd \ddp \des \deu \df \dFd \dFt \di \dL \dn \d0 \drds \dS \dT
\dv \dy
\da \db \dC \dD \dE \det \dew \dF \dFp \dg \dl \dm \do \dp \ds \dt \du
\dx
which is more heat than light. Yes, those are all the possibilites, but I,
Joe Newguy, want to list schemas, and \ds and \dS look like the good
guesses, neither of which is the right answer. If, with this feature, I
typed \desc<tab>, I might see:
\describe \describe-functions \describe-schemas \describe-tables
...
So my voyage of discovery would have completed with having typed
"\desc<tab>-sc<tab>" and if we add a note to interactive mode, I'd be shown
the hint that \dn is the shortcut for that just above the list of schemas.
On Fri, Jan 26, 2018 at 04:28:24PM +0100, Vik Fearing wrote:
On 01/26/2018 03:49 PM, David Fetter wrote:
I propose that we do what at least MySQL, Oracle, and DB2 do and
implement DESCRIBE as its own command.Hard pass.
Would you be so kind as to expand on this? "Pass" might indicate a
lack of interest in doing the work, but "hard pass" seems to indicate
that you have reasons the work should not be done. Have I interpreted
this correctly?
Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
On 01/27/2018 05:39 PM, David Fetter wrote:
On Fri, Jan 26, 2018 at 04:28:24PM +0100, Vik Fearing wrote:
On 01/26/2018 03:49 PM, David Fetter wrote:
I propose that we do what at least MySQL, Oracle, and DB2 do and
implement DESCRIBE as its own command.Hard pass.
Would you be so kind as to expand on this? "Pass" might indicate a
lack of interest in doing the work, but "hard pass" seems to indicate
that you have reasons the work should not be done. Have I interpreted
this correctly?
Andreas said it quite well. I don't like having client commands look
like server commands. I don't mind exceptions for "help" and "quit",
but I see no reason for anything more.
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
On Sat, Jan 27, 2018 at 10:54:07PM +0100, Vik Fearing wrote:
On 01/27/2018 05:39 PM, David Fetter wrote:
On Fri, Jan 26, 2018 at 04:28:24PM +0100, Vik Fearing wrote:
On 01/26/2018 03:49 PM, David Fetter wrote:
I propose that we do what at least MySQL, Oracle, and DB2 do and
implement DESCRIBE as its own command.Hard pass.
Would you be so kind as to expand on this? "Pass" might indicate a
lack of interest in doing the work, but "hard pass" seems to indicate
that you have reasons the work should not be done. Have I interpreted
this correctly?Andreas said it quite well. I don't like having client commands look
like server commands. I don't mind exceptions for "help" and "quit",
but I see no reason for anything more.
I did not propose a client command mimicking a server command. I
thought I made that clear by mentioning that the \ commands are
unavailable to clients other than psql, and offering an alternative.
What I propose is in fact a server command, which at least three of
the other popular RDBMSs already have.
Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
What I propose is in fact a server command, >which at least three of
the other popular RDBMSs already have.
Well to actually implement it, it would probably be a client command,
because that's what \d* are. We would most likely want them implemented
the same, to avoid needless complexity.
I think people are more ok with \describe (with the backslash), which seems
like what you're suggesting anyway. I read Vik's "hard pass" as being on
having DESCRIBE which looks like an SQL command but would actually be
implemented on the client. This seems simpler at first but could cause
deep confusion later.
But \describe gives a hint that it's different with the \, so it might be
fine.
Overall I agree with your idea: the \d* commands are cryptic and a longhand
form would help people learning.
Best,
Ryan
On Mon, Jan 29, 2018 at 02:51:53PM +0000, Ryan Murphy wrote:
What I propose is in fact a server command, >which at least three of
the other popular RDBMSs already have.Well to actually implement it, it would probably be a client command,
because that's what \d* are.
Why should this command be silo'ed off to the psql client? If it's a
server command, it's available to all clients, not just psql.
We would most likely want them implemented the same, to avoid
needless complexity.
We could certainly have \d call DESCRIBE for later versions of the
server. \ commands which call different SQL depending on server
version have long been a standard practice.
I think people are more ok with \describe (with the backslash), which seems
like what you're suggesting anyway. I read Vik's "hard pass" as being on
having DESCRIBE which looks like an SQL command but would actually be
implemented on the client. This seems simpler at first but could cause
deep confusion later.
If we implement \d as DESCRIBE for server versions as of when DESCRIBE
is actually implemented, we've got wins all around.
Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
Attached is a patch to add verbose \describe commands to compliment our
existing but slightly cryptic family of \d commands.
The goals of this are:
- aid user discovery of \d-commands via tab completion
- make scripts and snippets slightly more self-documenting and
understandable
- save experienced users that 0.22 seconds where they try to remember what
\dFpS+ means or which command lists user mappings.
DESIGN CHOICES:
Every new command is of the form
\describe-some-system-object-type[-system][-verbose]. The -system suffix
stands in for the 'S' suffix and -verbose stands in for '+'.
New commands used the singular form, not plural.
Every new command has a direct analog \d-command, but the reverse is not
always true, especially when it comes to the commands that can specify
multiple object types. In those cases, there are multiple long versions
that correspond to several singular parameters (\describe-view,
\describe-materialized-view, \describe-index, etc) but no combinatorics
(i.e. no \describe-view-and-foreign-table).
There is a \describe-schema and \describe-namespace, both of which perform
\dn.
There is a \describe-role but no \describe-user or \describe-database-role.
I chose \describe-privilege for \dp
I chose \describe-type for \dT instead of \describe-data-type.
The command \describe-aggregate-function is \dfa, whereas
\describe-aggregate is \da.
NOTES:
There is currently nothing stopping you from using the short form suffixes
on long form commands, but the reverse isn't true. For example, you can
type \describe-functionS+ and it'll work, but \df-verbose will not. I allow
this mostly because it would take work to prevent it.
Documentation XML was updated but not formatted to make the diff easier to
read.
No regression cases were added. Currently our coverage of \d commands in
psql ifself is quite minimal:
~/src/postgres$ grep '\\d' src/test/regress/sql/psql.sql | sort | uniq
\copyright \dt arg1 \e arg1 arg2
\df exp
\d psql_serial_tab_id_seq
but perhaps we could test it indirectly in these other areas:
~/src/postgres/src/test/regress/sql$ grep '\\d' * | sed -e 's/^.*\\d/\\d/g'
-e 's/ .*//g' | sort | uniq -c
156 \d
2 \d'
1 \d*',
157 \d+
1 \d{4})',
1 \da
2 \d+):([a-zA-Z0-9+/=]+)\$([a-zA-Z0-9+=/]+):([a-zA-Z0-9+/=]+)',
4 \des
8 \des+
1 \det+
4 \deu
6 \deu+
1 \dew
14 \dew+
21 \df
1 \dfn
1 \dfp
4 \dp
4 \dRp
6 \dRp+
2 \dRs
3 \dRs+
2 \dt
On Mon, Jan 29, 2018 at 9:56 AM David Fetter <david@fetter.org> wrote:
Show quoted text
On Mon, Jan 29, 2018 at 02:51:53PM +0000, Ryan Murphy wrote:
What I propose is in fact a server command, >which at least three of
the other popular RDBMSs already have.Well to actually implement it, it would probably be a client command,
because that's what \d* are.Why should this command be silo'ed off to the psql client? If it's a
server command, it's available to all clients, not just psql.We would most likely want them implemented the same, to avoid
needless complexity.We could certainly have \d call DESCRIBE for later versions of the
server. \ commands which call different SQL depending on server
version have long been a standard practice.I think people are more ok with \describe (with the backslash), which
seems
like what you're suggesting anyway. I read Vik's "hard pass" as being on
having DESCRIBE which looks like an SQL command but would actually be
implemented on the client. This seems simpler at first but could cause
deep confusion later.If we implement \d as DESCRIBE for server versions as of when DESCRIBE
is actually implemented, we've got wins all around.Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
Attachments:
0001-Add-describe-commands-to-compliment-d-commands.patchtext/x-patch; charset=US-ASCII; name=0001-Add-describe-commands-to-compliment-d-commands.patchDownload
From e67e61ae789b09c98fe03378c819224d838c2f65 Mon Sep 17 00:00:00 2001
From: Corey Huinker <corey.huinker@gmail.com>
Date: Fri, 25 Jan 2019 00:57:23 +0000
Subject: [PATCH] Add \describe commands to compliment \d commands
---
doc/src/sgml/ref/psql-ref.sgml | 175 ++++++++++++++++++++++++---------
src/bin/psql/command.c | 132 ++++++++++++++++++++++++-
src/bin/psql/describe.c | 13 ++-
src/bin/psql/describe.h | 3 +
src/bin/psql/tab-complete.c | 135 ++++++++++++++++++++-----
5 files changed, 381 insertions(+), 77 deletions(-)
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 6c76cf2f00..363d6d9678 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -871,6 +871,17 @@ testdb=>
same line.
</para>
+ <para>
+ The family of meta-commands starting with <literal>\d</literal> often
+ have an equivalent <literal>\describe-</literal> "long form" command.
+ The long-form commands often have the suffixes <literal>-system</literal>
+ and <literal>-verbose</literal> which are the equivalent of the
+ short form suffixes <literal>S</literal> and <literal>+</literal>
+ respectively. The long form suffixes cannot be used on the short form
+ variants. Every <literal>\describe</literal> variant has an equivalent
+ short form variant.
+ </para>
+
<para>
The following meta-commands are defined:
@@ -1133,6 +1144,7 @@ testdb=>
<varlistentry>
<term><literal>\d[S+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe[-system][-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
@@ -1151,13 +1163,13 @@ testdb=>
</para>
<para>
- For some types of relation, <literal>\d</literal> shows additional information
+ For some types of relation, <literal>\d</literal> (and <literal>\describe</literal> shows additional information
for each column: column values for sequences, indexed expressions for
indexes, and foreign data wrapper options for foreign tables.
</para>
<para>
- The command form <literal>\d+</literal> is identical, except that
+ The command forms <literal>\d+</literal> and <literal>\describe-verbose</literal> are identical to <literal>\d</literal>, except that
more information is displayed: any comments associated with the
columns of the table are shown, as is the presence of OIDs in the
table, the view definition if the relation is a view, a non-default
@@ -1167,13 +1179,13 @@ testdb=>
<para>
By default, only user-created objects are shown; supply a
- pattern or the <literal>S</literal> modifier to include system
+ pattern or the <literal>S</literal> modifier for <literal>\d</literal> or <literal>-verbose</literal> modifier for <literal>\describe</literal> modifier to include system
objects.
</para>
<note>
<para>
- If <command>\d</command> is used without a
+ If <command>\d</command> / <command>\describe</command> is used without a
<replaceable class="parameter">pattern</replaceable> argument, it is
equivalent to <command>\dtvmsE</command> which will show a list of
all visible tables, views, materialized views, sequences and
@@ -1186,6 +1198,7 @@ testdb=>
<varlistentry>
<term><literal>\da[S] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-aggregate[-system] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
@@ -1194,7 +1207,7 @@ testdb=>
class="parameter">pattern</replaceable>
is specified, only aggregates whose names match the pattern are shown.
By default, only user-created objects are shown; supply a
- pattern or the <literal>S</literal> modifier to include system
+ pattern or the <literal>S</literal>/<literal>-system</literal> modifier to include system
objects.
</para>
</listitem>
@@ -1202,13 +1215,14 @@ testdb=>
<varlistentry>
<term><literal>\dA[+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-access-method[-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists access methods. If <replaceable
class="parameter">pattern</replaceable> is specified, only access
methods whose names match the pattern are shown. If
- <literal>+</literal> is appended to the command name, each access
+ <literal>+</literal>/<literal>-verbose</literal> is appended to the command name, each access
method is listed with its associated handler function and description.
</para>
</listitem>
@@ -1216,13 +1230,14 @@ testdb=>
<varlistentry>
<term><literal>\db[+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-tablespace[-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists tablespaces. If <replaceable
class="parameter">pattern</replaceable>
is specified, only tablespaces whose names match the pattern are shown.
- If <literal>+</literal> is appended to the command name, each tablespace
+ If <literal>+</literal>/<literal>-verbose</literal> is appended to the command name, each tablespace
is listed with its associated options, on-disk size, permissions and
description.
</para>
@@ -1232,6 +1247,7 @@ testdb=>
<varlistentry>
<term><literal>\dc[S+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-conversion[-system][-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists conversions between character-set encodings.
@@ -1239,9 +1255,9 @@ testdb=>
is specified, only conversions whose names match the pattern are
listed.
By default, only user-created objects are shown; supply a
- pattern or the <literal>S</literal> modifier to include system
+ pattern or the <literal>S</literal>/<literal>-system</literal> modifier to include system
objects.
- If <literal>+</literal> is appended to the command name, each object
+ If <literal>+</literal>/<literal>-verbose</literal> is appended to the command name, each object
is listed with its associated description.
</para>
</listitem>
@@ -1250,13 +1266,14 @@ testdb=>
<varlistentry>
<term><literal>\dC[+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-cast[-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists type casts.
If <replaceable class="parameter">pattern</replaceable>
is specified, only casts whose source or target types match the
pattern are listed.
- If <literal>+</literal> is appended to the command name, each object
+ If <literal>+</literal>/<literal>-verbose</literal> is appended to the command name, each object
is listed with its associated description.
</para>
</listitem>
@@ -1265,6 +1282,11 @@ testdb=>
<varlistentry>
<term><literal>\dd[S] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-constraint[-system] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-operator-class[-system] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-operator-family[-system] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-rule[-system] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-trigger[-system] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Shows the descriptions of objects of type <literal>constraint</literal>,
@@ -1279,7 +1301,7 @@ testdb=>
objects of the appropriate type if no argument is given. But in either
case, only objects that have a description are listed.
By default, only user-created objects are shown; supply a
- pattern or the <literal>S</literal> modifier to include system
+ pattern or the <literal>S</literal>/<literal>-system</literal> modifier to include system
objects.
</para>
@@ -1294,15 +1316,16 @@ testdb=>
<varlistentry>
<term><literal>\dD[S+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-domain[-system][-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists domains. If <replaceable
class="parameter">pattern</replaceable>
is specified, only domains whose names match the pattern are shown.
By default, only user-created objects are shown; supply a
- pattern or the <literal>S</literal> modifier to include system
+ pattern or the <literal>S</literal>/<literal>-system</literal> modifier to include system
objects.
- If <literal>+</literal> is appended to the command name, each object
+ If <literal>+</literal>/<literal>-verbose</literal> is appended to the command name, each object
is listed with its associated permissions and description.
</para>
</listitem>
@@ -1311,6 +1334,7 @@ testdb=>
<varlistentry>
<term><literal>\ddp [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-default-access-privelege [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists default access privilege settings. An entry is shown for
@@ -1338,6 +1362,11 @@ testdb=>
<term><literal>\ds[S+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<term><literal>\dt[S+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<term><literal>\dv[S+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-index[-system][-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-materialized-view[-system][-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-sequence[-system][-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-table[-system][-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-view[-system][-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
@@ -1346,16 +1375,22 @@ testdb=>
<literal>t</literal>, and <literal>v</literal>
stand for foreign table, index, materialized view, sequence, table, and view,
respectively.
- You can specify any or all of
+ In the short forms, you can specify any or all of
these letters, in any order, to obtain a listing of objects
of these types. For example, <literal>\dit</literal> lists indexes
- and tables. If <literal>+</literal> is
+ and tables. If <literal>+</literal>/<literal>-verbose</literal> is
appended to the command name, each object is listed with its
physical size on disk and its associated description, if any.
+ <literal>\describe-foreign-table</literal> is equivalent to <literal>\dE</literal>.
+ <literal>\describe-index</literal> is equivalent to <literal>\di</literal>.
+ <literal>\describe-materialized-view</literal> is equivalent to <literal>\dm</literal>.
+ <literal>\describe-sequence</literal> is equivalent to <literal>\ds</literal>.
+ <literal>\describe-table</literal> is equivalent to <literal>\dt</literal>.
+ <literal>\describe-view</literal> is equivalent to <literal>\dv</literal>.
If <replaceable class="parameter">pattern</replaceable> is
specified, only objects whose names match the pattern are listed.
By default, only user-created objects are shown; supply a
- pattern or the <literal>S</literal> modifier to include system
+ pattern or the <literal>S</literal>/<literal>-system</literal> modifier to include system
objects.
</para>
</listitem>
@@ -1364,13 +1399,14 @@ testdb=>
<varlistentry>
<term><literal>\des[+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-foreign-servers[-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists foreign servers (mnemonic: <quote>external
servers</quote>).
If <replaceable class="parameter">pattern</replaceable> is
specified, only those servers whose name matches the pattern
- are listed. If the form <literal>\des+</literal> is used, a
+ are listed. If the forms <literal>\des+</literal> or <literal>\describe-foreign-server-verbose</literal> are used, a
full description of each server is shown, including the
server's access privileges, type, version, options, and description.
</para>
@@ -1380,13 +1416,14 @@ testdb=>
<varlistentry>
<term><literal>\det[+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-foreign-table[-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists foreign tables (mnemonic: <quote>external tables</quote>).
If <replaceable class="parameter">pattern</replaceable> is
specified, only entries whose table name or schema name matches
- the pattern are listed. If the form <literal>\det+</literal>
- is used, generic options and the foreign table description
+ the pattern are listed. If the forms <literal>\det+</literal> or <literal>\describe-foreign-table-verbose</literal>
+ are used, generic options and the foreign table description
are also displayed.
</para>
</listitem>
@@ -1395,19 +1432,20 @@ testdb=>
<varlistentry>
<term><literal>\deu[+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-user-mapping[-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists user mappings (mnemonic: <quote>external
users</quote>).
If <replaceable class="parameter">pattern</replaceable> is
specified, only those mappings whose user names match the
- pattern are listed. If the form <literal>\deu+</literal> is
+ pattern are listed. If the forms <literal>\deu+</literal> or <literal>\describe-user-mapping-verboze</literal> are
used, additional information about each mapping is shown.
</para>
<caution>
<para>
- <literal>\deu+</literal> might also display the user name and
+ <literal>\deu+</literal> and <literal>\describe-user-mapping-verboze</literal> might also display the user name and
password of the remote user, so care should be taken not to
disclose them.
</para>
@@ -1418,14 +1456,15 @@ testdb=>
<varlistentry>
<term><literal>\dew[+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-foreign-data-wrapper[-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists foreign-data wrappers (mnemonic: <quote>external
wrappers</quote>).
If <replaceable class="parameter">pattern</replaceable> is
specified, only those foreign-data wrappers whose name matches
- the pattern are listed. If the form <literal>\dew+</literal>
- is used, the access privileges, options, and description of the
+ the pattern are listed. If the forms <literal>\dew+</literal> or <literal>\describe-foreign-data-wrapper-verbose</literal>
+ are used, the access privileges, options, and description of the
foreign-data wrapper are also shown.
</para>
</listitem>
@@ -1434,25 +1473,37 @@ testdb=>
<varlistentry>
<term><literal>\df[anptwS+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-function[-system][-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-normal-function[-system][-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-procedure[-system][-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-aggregate-function[-system][-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-trigger-function[-system][-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-window-function[-system][-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists functions, together with their result data types, argument data
types, and function types, which are classified as <quote>agg</quote>
(aggregate), <quote>normal</quote>, <quote>procedure</quote>, <quote>trigger</quote>, or <quote>window</quote>.
- To display only functions
+ In the short form, to display only functions
of specific type(s), add the corresponding letters <literal>a</literal>,
<literal>n</literal>, <literal>p</literal>, <literal>t</literal>, or <literal>w</literal> to the command.
If <replaceable
class="parameter">pattern</replaceable> is specified, only
functions whose names match the pattern are shown.
By default, only user-created
- objects are shown; supply a pattern or the <literal>S</literal>
+ objects are shown; supply a pattern or the <literal>S</literal>/<literal>-system</literal>
modifier to include system objects.
- If the form <literal>\df+</literal> is used, additional information
+ If the form <literal>\df+</literal> or the <literal>-verbose</literal> suffix is used, additional information
about each function is shown, including volatility,
parallel safety, owner, security classification, access privileges,
language, source code and description.
+ <literal>\describe-function</literal> is the equivalent of <literal>\df</literal>.
+ <literal>\describe-normal-function</literal> is the equivalent of <literal>\dff</literal>.
+ <literal>\describe-procedure</literal> is the equivalent of <literal>\dfp</literal>.
+ <literal>\describe-aggregate-function</literal> is the equivalent of <literal>\dfa</literal>.
+ <literal>\describe-trigger-function</literal> is the equivalent of <literal>\dft</literal>.
+ <literal>\describe-window-function</literal> is the equivalent of <literal>\dfw</literal>.
</para>
<tip>
@@ -1468,12 +1519,13 @@ testdb=>
<varlistentry>
<term><literal>\dF[+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-text-search-configuration[-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists text search configurations.
If <replaceable class="parameter">pattern</replaceable> is specified,
only configurations whose names match the pattern are shown.
- If the form <literal>\dF+</literal> is used, a full description of
+ If the form <literal>\dF+</literal> or <literal>\describe-text-search-configuration-verbose</literal> is used, a full description of
each configuration is shown, including the underlying text search
parser and the dictionary list for each parser token type.
</para>
@@ -1482,12 +1534,13 @@ testdb=>
<varlistentry>
<term><literal>\dFd[+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-text-search-dictionary[-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists text search dictionaries.
If <replaceable class="parameter">pattern</replaceable> is specified,
only dictionaries whose names match the pattern are shown.
- If the form <literal>\dFd+</literal> is used, additional information
+ If the form <literal>\dFd+</literal> or <literal>\describe-text-search-dictionary-verbose</literal> is used, additional information
is shown about each selected dictionary, including the underlying
text search template and the option values.
</para>
@@ -1496,12 +1549,13 @@ testdb=>
<varlistentry>
<term><literal>\dFp[+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-text-search-parser[-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists text search parsers.
If <replaceable class="parameter">pattern</replaceable> is specified,
only parsers whose names match the pattern are shown.
- If the form <literal>\dFp+</literal> is used, a full description of
+ If the form <literal>\dFp+</literal> or <literal>\describe-text-search-parser-verbose</literal> is used, a full description of
each parser is shown, including the underlying functions and the
list of recognized token types.
</para>
@@ -1510,12 +1564,13 @@ testdb=>
<varlistentry>
<term><literal>\dFt[+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-text-search-template[-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists text search templates.
If <replaceable class="parameter">pattern</replaceable> is specified,
only templates whose names match the pattern are shown.
- If the form <literal>\dFt+</literal> is used, additional information
+ If the form <literal>\dFt+</literal> or <literal>\describe-text-search-template-verbose</literal> is used, additional information
is shown about each template, including the underlying function names.
</para>
</listitem>
@@ -1524,6 +1579,7 @@ testdb=>
<varlistentry>
<term><literal>\dg[S+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-role[-system][-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists database roles.
@@ -1531,10 +1587,10 @@ testdb=>
unified into <quote>roles</quote>, this command is now equivalent to
<literal>\du</literal>.)
By default, only user-created roles are shown; supply the
- <literal>S</literal> modifier to include system roles.
+ <literal>S</literal> or <literal>-system</literal> modifier to include system roles.
If <replaceable class="parameter">pattern</replaceable> is specified,
only those roles whose names match the pattern are listed.
- If the form <literal>\dg+</literal> is used, additional information
+ If the suffix <literal>+</literal> or <literal>-verbose></literal> is specified, additional information
is shown about each role; currently this adds the comment for each
role.
</para>
@@ -1554,14 +1610,15 @@ testdb=>
<varlistentry>
<term><literal>\dL[S+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-procedural-language[-system][-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists procedural languages. If <replaceable
class="parameter">pattern</replaceable>
is specified, only languages whose names match the pattern are listed.
By default, only user-created languages
- are shown; supply the <literal>S</literal> modifier to include system
- objects. If <literal>+</literal> is appended to the command name, each
+ are shown; supply the <literal>S</literal> or <literal>-system</literal> modifier to include system
+ objects. If <literal>+</literal> or <literal>-verbose</literal> is appended to the command name, each
language is listed with its call handler, validator, access privileges,
and whether it is a system object.
</para>
@@ -1571,6 +1628,8 @@ testdb=>
<varlistentry>
<term><literal>\dn[S+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-schema[-system][-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-namespace[-system][-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
@@ -1578,8 +1637,8 @@ testdb=>
class="parameter">pattern</replaceable>
is specified, only schemas whose names match the pattern are listed.
By default, only user-created objects are shown; supply a
- pattern or the <literal>S</literal> modifier to include system objects.
- If <literal>+</literal> is appended to the command name, each object
+ pattern or the <literal>S</literal> or <literal>-system</literal> modifier to include system objects.
+ If <literal>+</literal> (or <literal>-verbose</literal> is appended to the command name, each object
is listed with its associated permissions and description, if any.
</para>
</listitem>
@@ -1588,15 +1647,16 @@ testdb=>
<varlistentry>
<term><literal>\do[S+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-operator[-system][-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists operators with their operand and result types.
If <replaceable class="parameter">pattern</replaceable> is
specified, only operators whose names match the pattern are listed.
By default, only user-created objects are shown; supply a
- pattern or the <literal>S</literal> modifier to include system
+ pattern or the <literal>S</literal> or <literal>-system</literal> modifier to include system
objects.
- If <literal>+</literal> is appended to the command name,
+ If <literal>+</literal> or <literal>-verbose</literal> is appended to the command name,
additional information about each operator is shown, currently just
the name of the underlying function.
</para>
@@ -1606,14 +1666,15 @@ testdb=>
<varlistentry>
<term><literal>\dO[S+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-collation[-system][-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists collations.
If <replaceable class="parameter">pattern</replaceable> is
specified, only collations whose names match the pattern are
listed. By default, only user-created objects are shown;
- supply a pattern or the <literal>S</literal> modifier to
- include system objects. If <literal>+</literal> is appended
+ supply a pattern or the <literal>S</literal> or <literal>-system</literal> modifier to
+ include system objects. If <literal>+</literal> or <literal>-verbose</literal> is appended
to the command name, each collation is listed with its associated
description, if any.
Note that only collations usable with the current database's encoding
@@ -1626,6 +1687,7 @@ testdb=>
<varlistentry>
<term><literal>\dp [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-privilege [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists tables, views and sequences with their
@@ -1647,6 +1709,7 @@ testdb=>
<varlistentry>
<term><literal>\drds [ <link linkend="app-psql-patterns"><replaceable class="parameter">role-pattern</replaceable></link> [ <link linkend="app-psql-patterns"><replaceable class="parameter">database-pattern</replaceable></link> ] ]</literal></term>
+ <term><literal>\describe-defined-configuration-setting [ <link linkend="app-psql-patterns"><replaceable class="parameter">role-pattern</replaceable></link> [ <link linkend="app-psql-patterns"><replaceable class="parameter">database-pattern</replaceable></link> ] ]</literal></term>
<listitem>
<para>
Lists defined configuration settings. These settings can be
@@ -1669,13 +1732,14 @@ testdb=>
<varlistentry>
<term><literal>\dRp[+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-replication-publication[-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists replication publications.
If <replaceable class="parameter">pattern</replaceable> is
specified, only those publications whose names match the pattern are
listed.
- If <literal>+</literal> is appended to the command name, the tables
+ If <literal>+</literal> or <literal>-verbose</literal> is appended to the command name, the tables
associated with each publication are shown as well.
</para>
</listitem>
@@ -1683,13 +1747,14 @@ testdb=>
<varlistentry>
<term><literal>\dRs[+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-replication-subscription[-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists replication subscriptions.
If <replaceable class="parameter">pattern</replaceable> is
specified, only those subscriptions whose names match the pattern are
listed.
- If <literal>+</literal> is appended to the command name, additional
+ If <literal>+</literal> or <literal>-verbose</literal> is appended to the command name, additional
properties of the subscriptions are shown.
</para>
</listitem>
@@ -1697,16 +1762,17 @@ testdb=>
<varlistentry>
<term><literal>\dT[S+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-type[-system][-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists data types.
If <replaceable class="parameter">pattern</replaceable> is
specified, only types whose names match the pattern are listed.
- If <literal>+</literal> is appended to the command name, each type is
+ If <literal>+</literal> or <literal>-verbose</literal> is appended to the command name, each type is
listed with its internal name and size, its allowed values
if it is an <type>enum</type> type, and its associated permissions.
By default, only user-created objects are shown; supply a
- pattern or the <literal>S</literal> modifier to include system
+ pattern or the <literal>S</literal> or <literal>-system</literal> modifier to include system
objects.
</para>
</listitem>
@@ -1714,6 +1780,7 @@ testdb=>
<varlistentry>
<term><literal>\du[S+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-role[S+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists database roles.
@@ -1721,10 +1788,10 @@ testdb=>
unified into <quote>roles</quote>, this command is now equivalent to
<literal>\dg</literal>.)
By default, only user-created roles are shown; supply the
- <literal>S</literal> modifier to include system roles.
+ <literal>S</literal> or <literal>-system</literal> modifier to include system roles.
If <replaceable class="parameter">pattern</replaceable> is specified,
only those roles whose names match the pattern are listed.
- If the form <literal>\du+</literal> is used, additional information
+ If the suffix <literal>+</literal> or <literal>-verbose</literal> is used, additional information
is shown about each role; currently this adds the comment for each
role.
</para>
@@ -1733,13 +1800,14 @@ testdb=>
<varlistentry>
<term><literal>\dx[+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-extension[-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists installed extensions.
If <replaceable class="parameter">pattern</replaceable>
is specified, only those extensions whose names match the pattern
are listed.
- If the form <literal>\dx+</literal> is used, all the objects belonging
+ If the suffix <literal>+</literal> or <literal>-verbose</literal> is used, all the objects belonging
to each matching extension are listed.
</para>
</listitem>
@@ -1747,15 +1815,24 @@ testdb=>
<varlistentry>
<term><literal>\dy[+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-event-trigger[-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists event triggers.
If <replaceable class="parameter">pattern</replaceable>
is specified, only those event triggers whose names match the pattern
are listed.
- If <literal>+</literal> is appended to the command name, each object
+ If <literal>+</literal> or <literal>-verbose</literal> is appended to the command name, each object
is listed with its associated description.
</para>
+
+ <tip>
+ <para>
+ Long form commands can aid in clarity when sharing code with others.
+ Using tab completion can reduce typing and aid in the discovery of new
+ <literal>\describe</literal> commands.
+ </para>
+ </tip>
</listitem>
</varlistentry>
@@ -2291,7 +2368,7 @@ SELECT
character set encodings, and access privileges.
If <replaceable class="parameter">pattern</replaceable> is specified,
only databases whose names match the pattern are listed.
- If <literal>+</literal> is appended to the command name, database
+ If <literal>+</literal> (or <literal>-verbose</literal> in the long form) is appended to the command name, database
sizes, default tablespaces, and descriptions are also displayed.
(Size information is only available for databases that the current
user can connect to.)
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index ab259c473a..a27be4d916 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -685,6 +685,12 @@ exec_command_crosstabview(PsqlScanState scan_state, bool active_branch)
return status;
}
+static
+bool cmd_match(const char* str, const char* pattern)
+{
+ return (strncmp(str, pattern, strlen(pattern)) == 0);
+}
+
/*
* \d* commands
*/
@@ -853,7 +859,131 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
switch (cmd[2])
{
case 's':
- success = listForeignServers(pattern, show_verbose);
+ if (cmd[3] != 'c')
+ {
+ /* no \describe, just \des */
+ success = listForeignServers(pattern, show_verbose);
+ break;
+ }
+ else if (!cmd_match(cmd,"describe"))
+ {
+ status = PSQL_CMD_UNKNOWN;
+ break;
+ }
+ else
+ {
+ /* Allow -verbose suffix but keep '+' */
+ if (strstr(cmd, "-verbose") != NULL)
+ show_verbose = true;
+ /* Allow -system suffix but keep 'S' */
+ if (strstr(cmd, "-system") != NULL)
+ show_system = true;
+
+ if (cmd_match(cmd,"describe-aggregate-function"))
+ success = describeAggregates(pattern, show_verbose, show_system);
+ else if (cmd_match(cmd, "describe-access-method"))
+ success = describeAccessMethods(pattern, show_verbose);
+ else if (cmd_match(cmd, "describe-tablespace"))
+ success = describeTablespaces(pattern, show_verbose);
+ else if (cmd_match(cmd, "describe-conversion"))
+ success = listConversions(pattern, show_verbose, show_system);
+ else if (cmd_match(cmd, "describe-cast"))
+ success = listCasts(pattern, show_verbose);
+ else if (cmd_match(cmd, "describe-constraint") ||
+ cmd_match(cmd, "describe-operator-class") ||
+ cmd_match(cmd, "describe-operator-family") ||
+ cmd_match(cmd, "describe-rule") ||
+ cmd_match(cmd, "describe-trigger"))
+ success = objectDescription(pattern, show_system);
+ else if (cmd_match(cmd, "describe-domain"))
+ success = listDomains(pattern, show_verbose, show_system);
+ else if (cmd_match(cmd, "describe-default-access-privelege "))
+ success = listDefaultACLs(pattern);
+ else if (cmd_match(cmd, "describe-foreign-server"))
+ success = listForeignServers(pattern, show_verbose);
+ else if (cmd_match(cmd, "describe-foreign-table"))
+ success = listForeignTables(pattern, show_verbose);
+ else if (cmd_match(cmd, "describe-index"))
+ success = listTables("i", pattern, show_verbose, show_system);
+ else if (cmd_match(cmd, "describe-materialized-view"))
+ success = listTables("m", pattern, show_verbose, show_system);
+ else if (cmd_match(cmd, "describe-sequence"))
+ success = listTables("s", pattern, show_verbose, show_system);
+ else if (cmd_match(cmd, "describe-table"))
+ success = listTables("t", pattern, show_verbose, show_system);
+ else if (cmd_match(cmd, "describe-view"))
+ success = listTables("v", pattern, show_verbose, show_system);
+ else if (cmd_match(cmd, "describe-user-mapping"))
+ success = listUserMappings(pattern, show_verbose);
+ else if (cmd_match(cmd, "describe-foreign-data-wrapper"))
+ success = listForeignDataWrappers(pattern, show_verbose);
+ else if (cmd_match(cmd, "describe-function"))
+ success = describeFunctions("\0", pattern, show_verbose, show_system);
+ else if (cmd_match(cmd, "describe-procedure"))
+ success = describeFunctions("p", pattern, show_verbose, show_system);
+ else if (cmd_match(cmd, "describe-trigger-function"))
+ success = describeFunctions("t", pattern, show_verbose, show_system);
+ else if (cmd_match(cmd, "describe-window-function"))
+ success = describeFunctions("w", pattern, show_verbose, show_system);
+ else if (cmd_match(cmd, "describe-normal-function"))
+ success = describeFunctions("n", pattern, show_verbose, show_system);
+ else if (cmd_match(cmd, "describe-text-search-configuration"))
+ success = listTSConfigs(pattern, show_verbose);
+ else if (cmd_match(cmd, "describe-text-search-dictionary"))
+ success = listTSDictionaries(pattern, show_verbose);
+ else if (cmd_match(cmd, "describe-text-search-parser"))
+ success = listTSParsers(pattern, show_verbose);
+ else if (cmd_match(cmd, "describe-text-search-templates"))
+ success = listTSTemplates(pattern, show_verbose);
+ else if (cmd_match(cmd, "describe-role"))
+ success = describeRoles(pattern, show_verbose, show_system);
+ else if (cmd_match(cmd, "describe-procedural-language"))
+ success = listLanguages(pattern, show_verbose, show_system);
+ else if (cmd_match(cmd, "describe-schema") ||
+ cmd_match(cmd, "describe-namespace"))
+ success = listSchemas(pattern, show_verbose, show_system);
+ else if (cmd_match(cmd, "describe-operator"))
+ success = describeOperators(pattern, show_verbose, show_system);
+ else if (cmd_match(cmd, "describe-collation"))
+ success = listCollations(pattern, show_verbose, show_system);
+ else if (cmd_match(cmd, "describe-privilege "))
+ success = permissionsList(pattern);
+ else if (cmd_match(cmd, "describe-defined-configuration-setting "))
+ {
+ char *pattern2 = NULL;
+
+ if (pattern)
+ pattern2 = psql_scan_slash_option(scan_state,
+ OT_NORMAL, NULL, true);
+ success = listDbRoleSettings(pattern, pattern2);
+
+ if (pattern2)
+ free(pattern2);
+ }
+ else if (cmd_match(cmd, "describe-replication-publication"))
+ if (show_verbose)
+ success = describePublications(pattern);
+ else
+ success = listPublications(pattern);
+ else if (cmd_match(cmd, "describe-replication-subscription"))
+ success = describeSubscriptions(pattern, show_verbose);
+ else if (cmd_match(cmd, "describe-type"))
+ success = describeTypes(pattern, show_verbose, show_system);
+ else if (cmd_match(cmd, "describe-extension"))
+ if (show_verbose)
+ success = listExtensionContents(pattern);
+ else
+ success = listExtensions(pattern);
+ else if (cmd_match(cmd, "describe-event-trigger"))
+ success = listEventTriggers(pattern, show_verbose);
+ else if (pattern)
+ /* generic describe on a specific pattern */
+ success = describeTableDetails(pattern, show_verbose, show_system);
+ else
+ /* standard listing of interesting things */
+ success = listTables("tvmsE", NULL, show_verbose, show_system);
+ }
+ status = PSQL_CMD_UNKNOWN;
break;
case 'u':
success = listUserMappings(pattern, show_verbose);
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 4da6719ce7..444b0a2ae4 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -54,6 +54,18 @@ static bool listOneExtensionContents(const char *extname, const char *oid);
*----------------
*/
+/*
+ * \describe
+ *
+ * The full-word version of every \d* command
+ */
+bool
+describe(const char *words, const char *pattern, bool verbose, bool showSystem)
+{
+
+ return true;
+}
+
/*
* \da
@@ -294,7 +306,6 @@ describeTablespaces(const char *pattern, bool verbose)
return true;
}
-
/*
* \df
* Takes an optional regexp to select particular functions.
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index 4ff1f91f38..ff13e3bbd1 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -9,6 +9,9 @@
#define DESCRIBE_H
+/* \describe, \describe-table, etc */
+extern bool describe(const char *words, const char *pattern, bool verbose, bool showSystem);
+
/* \da */
extern bool describeAggregates(const char *pattern, bool verbose, bool showSystem);
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 292b1f483a..11821a4c86 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1400,6 +1400,51 @@ psql_completion(const char *text, int start, int end)
"\\w", "\\watch",
"\\z",
"\\!", "\\?",
+ "\\describe-aggregate-function",
+ "\\describe-aggregate",
+ "\\describe-access-method",
+ "\\describe-tablespace",
+ "\\describe-conversion",
+ "\\describe-cast",
+ "\\describe-constraint",
+ "\\describe-operator-class",
+ "\\describe-operator-family",
+ "\\describe-rule",
+ "\\describe-trigger",
+ "\\describe-domain",
+ "\\describe-default-access-privelege ",
+ "\\describe-foreign-server",
+ "\\describe-foreign-table",
+ "\\describe-index",
+ "\\describe-materialized-view",
+ "\\describe-sequence",
+ "\\describe-table",
+ "\\describe-view",
+ "\\describe-user-mapping",
+ "\\describe-foreign-data-wrapper",
+ "\\describe-function",
+ "\\describe-procedure",
+ "\\describe-trigger-function",
+ "\\describe-window-function",
+ "\\describe-normal-function",
+ "\\describe-text-search-configuration",
+ "\\describe-text-search-dictionary",
+ "\\describe-text-search-parser",
+ "\\describe-text-search-templates",
+ "\\describe-role",
+ "\\describe-procedural-language",
+ "\\describe-schema",
+ "\\describe-namespace",
+ "\\describe-operator",
+ "\\describe-collation",
+ "\\describe-privilege ",
+ "\\describe-defined-configuration-setting ",
+ "\\describe-replication-publication",
+ "\\describe-replication-subscription",
+ "\\describe-type",
+ "\\describe-extension",
+ "\\describe-event-trigger",
+ "\\describe",
NULL
};
@@ -3460,60 +3505,98 @@ psql_completion(const char *text, int start, int end)
if (!recognized_connection_string(prev_wd))
COMPLETE_WITH_QUERY(Query_for_list_of_roles);
}
- else if (TailMatchesCS("\\da*"))
+ else if (TailMatchesCS("\\df*") ||
+ TailMatchesCS("\\describe-function*") ||
+ TailMatchesCS("\\describe-aggregate-function*") ||
+ TailMatchesCS("\\describe-window-function*") ||
+ TailMatchesCS("\\describe-procedure*") ||
+ TailMatchesCS("\\describe-normal-function*"))
+ COMPLETE_WITH_VERSIONED_SCHEMA_QUERY(Query_for_list_of_functions, NULL);
+ else if (TailMatchesCS("\\da*") ||
+ TailMatchesCS("\\describe-aggregate*"))
COMPLETE_WITH_VERSIONED_SCHEMA_QUERY(Query_for_list_of_aggregates, NULL);
- else if (TailMatchesCS("\\dA*"))
+ else if (TailMatchesCS("\\dA*") ||
+ TailMatchesCS("\\describe-access-method*"))
COMPLETE_WITH_QUERY(Query_for_list_of_access_methods);
- else if (TailMatchesCS("\\db*"))
+ else if (TailMatchesCS("\\db*") ||
+ TailMatchesCS("\\describe-tablespace*"))
COMPLETE_WITH_QUERY(Query_for_list_of_tablespaces);
- else if (TailMatchesCS("\\dD*"))
+ else if (TailMatchesCS("\\dD*") ||
+ TailMatchesCS("\\describe-domain*"))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_domains, NULL);
- else if (TailMatchesCS("\\des*"))
- COMPLETE_WITH_QUERY(Query_for_list_of_servers);
- else if (TailMatchesCS("\\deu*"))
+ else if (TailMatchesCS("\\deu*") ||
+ TailMatchesCS("\\describe-user-mapping*"))
COMPLETE_WITH_QUERY(Query_for_list_of_user_mappings);
- else if (TailMatchesCS("\\dew*"))
+ else if (TailMatchesCS("\\dew*") ||
+ TailMatchesCS("\\describe-foreign-data-wrapper*"))
COMPLETE_WITH_QUERY(Query_for_list_of_fdws);
- else if (TailMatchesCS("\\df*"))
- COMPLETE_WITH_VERSIONED_SCHEMA_QUERY(Query_for_list_of_functions, NULL);
- else if (TailMatchesCS("\\dFd*"))
+ else if (TailMatchesCS("\\dFd*") ||
+ TailMatchesCS("\\describe-text-search-dictionary*"))
COMPLETE_WITH_QUERY(Query_for_list_of_ts_dictionaries);
- else if (TailMatchesCS("\\dFp*"))
+ else if (TailMatchesCS("\\dFp*") ||
+ TailMatchesCS("\\describe-text-search-parser*"))
COMPLETE_WITH_QUERY(Query_for_list_of_ts_parsers);
- else if (TailMatchesCS("\\dFt*"))
+ else if (TailMatchesCS("\\dFt*") ||
+ TailMatchesCS("\\describe-text-search-template*"))
COMPLETE_WITH_QUERY(Query_for_list_of_ts_templates);
/* must be at end of \dF alternatives: */
else if (TailMatchesCS("\\dF*"))
COMPLETE_WITH_QUERY(Query_for_list_of_ts_configurations);
- else if (TailMatchesCS("\\di*"))
+ else if (TailMatchesCS("\\di*") ||
+ TailMatchesCS("\\describe-index*"))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_indexes, NULL);
- else if (TailMatchesCS("\\dL*"))
+ else if (TailMatchesCS("\\dL*") ||
+ TailMatchesCS("\\describe-procedural-language*"))
COMPLETE_WITH_QUERY(Query_for_list_of_languages);
- else if (TailMatchesCS("\\dn*"))
+ else if (TailMatchesCS("\\dn*") ||
+ TailMatchesCS("\\describe-schema*") ||
+ TailMatchesCS("\\describe-namespace*"))
COMPLETE_WITH_QUERY(Query_for_list_of_schemas);
- else if (TailMatchesCS("\\dp") || TailMatchesCS("\\z"))
+ else if (TailMatchesCS("\\dp") || TailMatchesCS("\\z") ||
+ TailMatchesCS("\\describe-privelege*"))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_grantables, NULL);
- else if (TailMatchesCS("\\ds*"))
+ else if (TailMatchesCS("\\ds*") ||
+ TailMatchesCS("\\describe-sequence*"))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_sequences, NULL);
- else if (TailMatchesCS("\\dt*"))
+ else if (TailMatchesCS("\\dt*") ||
+ TailMatchesCS("\\describe-table*"))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, NULL);
- else if (TailMatchesCS("\\dT*"))
+ else if (TailMatchesCS("\\dT*") ||
+ TailMatchesCS("\\describe-type*"))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_datatypes, NULL);
- else if (TailMatchesCS("\\du*") || TailMatchesCS("\\dg*"))
+ else if (TailMatchesCS("\\du*") || TailMatchesCS("\\dg*") ||
+ TailMatchesCS("\\describe-role*"))
COMPLETE_WITH_QUERY(Query_for_list_of_roles);
- else if (TailMatchesCS("\\dv*"))
+ else if (TailMatchesCS("\\dv*") ||
+ TailMatchesCS("\\describe-view*"))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_views, NULL);
- else if (TailMatchesCS("\\dx*"))
+ else if (TailMatchesCS("\\dx*") ||
+ TailMatchesCS("\\describe-extension*"))
COMPLETE_WITH_QUERY(Query_for_list_of_extensions);
- else if (TailMatchesCS("\\dm*"))
+ else if (TailMatchesCS("\\dm*") ||
+ TailMatchesCS("\\describe-materialized-view*"))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_matviews, NULL);
- else if (TailMatchesCS("\\dE*"))
+ else if (TailMatchesCS("\\dE*") ||
+ TailMatchesCS("\\describe-foreign-table*"))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_foreign_tables, NULL);
- else if (TailMatchesCS("\\dy*"))
+ else if (TailMatchesCS("\\dy*") ||
+ TailMatchesCS("\\describe-event-trigger*"))
COMPLETE_WITH_QUERY(Query_for_list_of_event_triggers);
+ /* short version \\des has conflict with \\describe */
+ else if (TailMatchesCS("\\describe-foreign-server*"))
+ COMPLETE_WITH_QUERY(Query_for_list_of_servers);
+
+ /* catch-all \\describe */
+ else if (TailMatchesCS("\\describe*"))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_relations, NULL);
+
+ /* \\des* must go after \\describe */
+ else if (TailMatchesCS("\\des*"))
+ COMPLETE_WITH_QUERY(Query_for_list_of_servers);
+
/* must be at end of \d alternatives: */
else if (TailMatchesCS("\\d*"))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_relations, NULL);
--
2.17.1
Hi,
On 2019-01-24 20:37:48 -0500, Corey Huinker wrote:
Attached is a patch to add verbose \describe commands to compliment our
existing but slightly cryptic family of \d commands.
Given that this patch has been added to the last commitfest for v12, I
think we should mark it as targeting 13, so it can be skipped over by
people looking to get things into v12. Even leaving fairness aside, I
don't think it's likely to be ready quickly enough...
Greetings,
Andres Freund
Given that this patch has been added to the last commitfest for v12, I
think we should mark it as targeting 13, so it can be skipped over by
people looking to get things into v12. Even leaving fairness aside, I
don't think it's likely to be ready quickly enough...
Obviously this patch is nowhere near the importance of most patches slated
for v12, but I would hope it can be considered, time permitting.
The size of the patch may look large (1036 lines), but 650+ of that is pure
documentation changes, ~50 lines of added autocomplete strings, ~140 lines
are added TailMatches calls (one per new autocomplete string), and what
remains is strncmp() calls to match those same strings, so it's pretty mild
in terms of impact.
Hi,
On 2019-02-23 19:14:27 -0500, Corey Huinker wrote:
Given that this patch has been added to the last commitfest for v12, I
think we should mark it as targeting 13, so it can be skipped over by
people looking to get things into v12. Even leaving fairness aside, I
don't think it's likely to be ready quickly enough...Obviously this patch is nowhere near the importance of most patches slated
for v12, but I would hope it can be considered, time permitting.The size of the patch may look large (1036 lines), but 650+ of that is pure
documentation changes, ~50 lines of added autocomplete strings, ~140 lines
are added TailMatches calls (one per new autocomplete string), and what
remains is strncmp() calls to match those same strings, so it's pretty mild
in terms of impact.
Sure, but it was late, and we have far more patches than we can deal
with. Many of them much much older than this.
Greetings,
Andres Freund
On Sat, Feb 23, 2019 at 7:19 PM Andres Freund <andres@anarazel.de> wrote:
Sure, but it was late, and we have far more patches than we can deal
with. Many of them much much older than this.
More importantly, at least in my opinion, is that this is one of those
questions that people tend to have very strong feelings about. Doing
something at the last minute risks people not feeling that they had an
adequate time to express those feelings before something got shipped.
Not everybody reads this list every day, or tests every new commit as
soon as it goes into the tree.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
The following review has been posted through the commitfest application:
make installcheck-world: not tested
Implements feature: not tested
Spec compliant: not tested
Documentation: not tested
Thanks for the patch, I have reviewed the patch and have some comments about the patch. The review includes the testing of the patch along with some code review.
Here are my testings results,
- Tab completion for \descibe-verbose.
I know that \d+ tab completion is also not there, but I think we must have tab completion for \descibe-verbose.
postgres=# \describe-
\describe-extension \describe-replication-publication \describe-user-mapping
\describe-foreign-data-wrapper \describe-replication-subscription \describe-view
\describe-foreign-server \describe-role \describe-window-function
\describe-foreign-table \describe-rule
...
- Error message in each command.
There is an error message after each command, here is the example.
postgres=# \describe
List of relations
Schema | Name | Type | Owner
--------+------+-------+---------
public | foo | table | vagrant
(1 row)
Invalid command \describe. Try \? for help.
I think this status is causing the problem.
+ /* standard listing of interesting things */
+ success = listTables("tvmsE", NULL, show_verbose, show_system);
+ }
+ status = PSQL_CMD_UNKNOWN;- Confusion about \desc and \desC
There is confusion while running the \desc command. I know the problem, but the user may confuse by this.
postgres=# \desC
List of foreign servers
Name | Owner | Foreign-data wrapper
------+-------+----------------------
(0 rows)
postgres=# \desc
Invalid command \desc. Try \? for help.
- Auto-completion of commands.
There is some more confusion in the completion of commands.
This command shows List of aggregates.
postgres=# \describe-aggregate-function
List of aggregate functions
Schema | Name | Result data type | Argument data types | Description
--------+------+------------------+---------------------+-------------
(0 rows)
This command shows a list of relation "\d"
postgres=# \describe-aggregatE-function
List of relations
Schema | Name | Type | Owner
--------+------+-------+---------
public | foo | table | vagrant
(1 row)
This command also shows a list of relations "\d".
postgres=# \describe-aggr
List of relations
Schema | Name | Type | Owner
--------+------+-------+---------
public | foo | table | vagrant
(1 row)
This command shows error messages.
postgres=# \descr
Invalid command \descr. Try \? for help.
...
Code review.
-------------
I have done a brief code review except for the documentation code. I don't like this code
if (cmd_match(cmd,"describe-aggregate-function"))
success = describeAggregates(pattern, show_verbose, show_system);
else if (cmd_match(cmd, "describe-access-method"))
success = describeAccessMethods(pattern, show_verbose);
else if (cmd_match(cmd, "describe-tablespace"))
success = describeTablespaces(pattern, show_verbose);
else if (cmd_match(cmd, "describe-conversion"))
success = listConversions(pattern, show_verbose, show_system);
else if (cmd_match(cmd, "describe-cast"))
success = listCasts(pattern, show_verbose
This can be achieved with the list/array/hash table, so I have changed that code in the attached patch just for a sample if you want I can do that for whole code.
--
Ibrar Ahmed
The new status of this patch is: Waiting on Author
Hi Corey,
Here is the modified patch (sample).
On Mon, Mar 4, 2019 at 7:02 PM Ibrar Ahmed <ibrar.ahmad@gmail.com> wrote:
The following review has been posted through the commitfest application:
make installcheck-world: not tested
Implements feature: not tested
Spec compliant: not tested
Documentation: not testedThanks for the patch, I have reviewed the patch and have some comments
about the patch. The review includes the testing of the patch along with
some code review.Here are my testings results,
- Tab completion for \descibe-verbose.
I know that \d+ tab completion is also not there, but I think we must have
tab completion for \descibe-verbose.postgres=# \describe-
\describe-extension
\describe-replication-publication \describe-user-mapping
\describe-foreign-data-wrapper
\describe-replication-subscription \describe-view
\describe-foreign-server \describe-role
\describe-window-function
\describe-foreign-table \describe-rule
...- Error message in each command.
There is an error message after each command, here is the example.
postgres=# \describe
List of relations
Schema | Name | Type | Owner
--------+------+-------+---------
public | foo | table | vagrant(1 row)
Invalid command \describe. Try \? for help.I think this status is causing the problem.
+ /* standard listing of interesting things */ + success = listTables("tvmsE", NULL, show_verbose, show_system); + } + status = PSQL_CMD_UNKNOWN;- Confusion about \desc and \desC
There is confusion while running the \desc command. I know the problem,
but the user may confuse by this.
postgres=# \desC
List of foreign servers
Name | Owner | Foreign-data wrapper
------+-------+----------------------
(0 rows)postgres=# \desc
Invalid command \desc. Try \? for help.- Auto-completion of commands.
There is some more confusion in the completion of commands.This command shows List of aggregates.
postgres=# \describe-aggregate-function
List of aggregate functions
Schema | Name | Result data type | Argument data types | Description
--------+------+------------------+---------------------+-------------
(0 rows)This command shows a list of relation "\d"
postgres=# \describe-aggregatE-function
List of relations
Schema | Name | Type | Owner
--------+------+-------+---------
public | foo | table | vagrant
(1 row)This command also shows a list of relations "\d".
postgres=# \describe-aggr
List of relations
Schema | Name | Type | Owner
--------+------+-------+---------
public | foo | table | vagrant
(1 row)This command shows error messages.
postgres=# \descr
Invalid command \descr. Try \? for help....
Code review.
-------------I have done a brief code review except for the documentation code. I don't
like this codeif (cmd_match(cmd,"describe-aggregate-function"))
success = describeAggregates(pattern, show_verbose, show_system);
else if (cmd_match(cmd,
"describe-access-method"))
success = describeAccessMethods(pattern,
show_verbose);
else if (cmd_match(cmd,
"describe-tablespace"))
success = describeTablespaces(pattern,
show_verbose);
else if (cmd_match(cmd,
"describe-conversion"))
success = listConversions(pattern,
show_verbose, show_system);
else if (cmd_match(cmd, "describe-cast"))
success = listCasts(pattern, show_verboseThis can be achieved with the list/array/hash table, so I have changed
that code in the attached patch just for a sample if you want I can do that
for whole code.--
Ibrar AhmedThe new status of this patch is: Waiting on Author
--
Ibrar Ahmed
Attachments:
0001-Add-describe-commands-to-compliment-d-commands-ibrar-v2.patchapplication/octet-stream; name=0001-Add-describe-commands-to-compliment-d-commands-ibrar-v2.patchDownload
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index a27be4d916..a9f4d8afca 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -167,6 +167,18 @@ static void checkWin32Codepage(void);
#endif
+typedef struct descCommand
+{
+ const char *command;
+ bool (*func)(const char *, bool);
+} descCommand;
+
+static const descCommand desc_commands[] = {
+ {"describe-tablespace", describeTablespaces},
+ {"describe-access-methods", describeAccessMethods},
+ {NULL, NULL}
+};
+
/*----------
* HandleSlashCmds:
@@ -691,6 +703,23 @@ bool cmd_match(const char* str, const char* pattern)
return (strncmp(str, pattern, strlen(pattern)) == 0);
}
+
+static
+bool descibeCmd(const char* str, const char *pattern, bool show_verbose)
+{
+ const descCommand *cmd;
+
+ for (cmd = desc_commands; cmd->command; cmd++)
+ {
+ if (cmd_match(cmd->command, str))
+ {
+ cmd->func(pattern, show_verbose);
+ return true;
+ }
+ }
+ return false;
+}
+
/*
* \d* commands
*/
@@ -878,112 +907,8 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
/* Allow -system suffix but keep 'S' */
if (strstr(cmd, "-system") != NULL)
show_system = true;
-
- if (cmd_match(cmd,"describe-aggregate-function"))
- success = describeAggregates(pattern, show_verbose, show_system);
- else if (cmd_match(cmd, "describe-access-method"))
- success = describeAccessMethods(pattern, show_verbose);
- else if (cmd_match(cmd, "describe-tablespace"))
- success = describeTablespaces(pattern, show_verbose);
- else if (cmd_match(cmd, "describe-conversion"))
- success = listConversions(pattern, show_verbose, show_system);
- else if (cmd_match(cmd, "describe-cast"))
- success = listCasts(pattern, show_verbose);
- else if (cmd_match(cmd, "describe-constraint") ||
- cmd_match(cmd, "describe-operator-class") ||
- cmd_match(cmd, "describe-operator-family") ||
- cmd_match(cmd, "describe-rule") ||
- cmd_match(cmd, "describe-trigger"))
- success = objectDescription(pattern, show_system);
- else if (cmd_match(cmd, "describe-domain"))
- success = listDomains(pattern, show_verbose, show_system);
- else if (cmd_match(cmd, "describe-default-access-privelege "))
- success = listDefaultACLs(pattern);
- else if (cmd_match(cmd, "describe-foreign-server"))
- success = listForeignServers(pattern, show_verbose);
- else if (cmd_match(cmd, "describe-foreign-table"))
- success = listForeignTables(pattern, show_verbose);
- else if (cmd_match(cmd, "describe-index"))
- success = listTables("i", pattern, show_verbose, show_system);
- else if (cmd_match(cmd, "describe-materialized-view"))
- success = listTables("m", pattern, show_verbose, show_system);
- else if (cmd_match(cmd, "describe-sequence"))
- success = listTables("s", pattern, show_verbose, show_system);
- else if (cmd_match(cmd, "describe-table"))
- success = listTables("t", pattern, show_verbose, show_system);
- else if (cmd_match(cmd, "describe-view"))
- success = listTables("v", pattern, show_verbose, show_system);
- else if (cmd_match(cmd, "describe-user-mapping"))
- success = listUserMappings(pattern, show_verbose);
- else if (cmd_match(cmd, "describe-foreign-data-wrapper"))
- success = listForeignDataWrappers(pattern, show_verbose);
- else if (cmd_match(cmd, "describe-function"))
- success = describeFunctions("\0", pattern, show_verbose, show_system);
- else if (cmd_match(cmd, "describe-procedure"))
- success = describeFunctions("p", pattern, show_verbose, show_system);
- else if (cmd_match(cmd, "describe-trigger-function"))
- success = describeFunctions("t", pattern, show_verbose, show_system);
- else if (cmd_match(cmd, "describe-window-function"))
- success = describeFunctions("w", pattern, show_verbose, show_system);
- else if (cmd_match(cmd, "describe-normal-function"))
- success = describeFunctions("n", pattern, show_verbose, show_system);
- else if (cmd_match(cmd, "describe-text-search-configuration"))
- success = listTSConfigs(pattern, show_verbose);
- else if (cmd_match(cmd, "describe-text-search-dictionary"))
- success = listTSDictionaries(pattern, show_verbose);
- else if (cmd_match(cmd, "describe-text-search-parser"))
- success = listTSParsers(pattern, show_verbose);
- else if (cmd_match(cmd, "describe-text-search-templates"))
- success = listTSTemplates(pattern, show_verbose);
- else if (cmd_match(cmd, "describe-role"))
- success = describeRoles(pattern, show_verbose, show_system);
- else if (cmd_match(cmd, "describe-procedural-language"))
- success = listLanguages(pattern, show_verbose, show_system);
- else if (cmd_match(cmd, "describe-schema") ||
- cmd_match(cmd, "describe-namespace"))
- success = listSchemas(pattern, show_verbose, show_system);
- else if (cmd_match(cmd, "describe-operator"))
- success = describeOperators(pattern, show_verbose, show_system);
- else if (cmd_match(cmd, "describe-collation"))
- success = listCollations(pattern, show_verbose, show_system);
- else if (cmd_match(cmd, "describe-privilege "))
- success = permissionsList(pattern);
- else if (cmd_match(cmd, "describe-defined-configuration-setting "))
- {
- char *pattern2 = NULL;
-
- if (pattern)
- pattern2 = psql_scan_slash_option(scan_state,
- OT_NORMAL, NULL, true);
- success = listDbRoleSettings(pattern, pattern2);
-
- if (pattern2)
- free(pattern2);
- }
- else if (cmd_match(cmd, "describe-replication-publication"))
- if (show_verbose)
- success = describePublications(pattern);
- else
- success = listPublications(pattern);
- else if (cmd_match(cmd, "describe-replication-subscription"))
- success = describeSubscriptions(pattern, show_verbose);
- else if (cmd_match(cmd, "describe-type"))
- success = describeTypes(pattern, show_verbose, show_system);
- else if (cmd_match(cmd, "describe-extension"))
- if (show_verbose)
- success = listExtensionContents(pattern);
- else
- success = listExtensions(pattern);
- else if (cmd_match(cmd, "describe-event-trigger"))
- success = listEventTriggers(pattern, show_verbose);
- else if (pattern)
- /* generic describe on a specific pattern */
- success = describeTableDetails(pattern, show_verbose, show_system);
- else
- /* standard listing of interesting things */
- success = listTables("tvmsE", NULL, show_verbose, show_system);
+ descibeCmd(cmd, pattern, show_verbose);
}
- status = PSQL_CMD_UNKNOWN;
break;
case 'u':
success = listUserMappings(pattern, show_verbose);
- Tab completion for \descibe-verbose.
I know that \d+ tab completion is also not there, but I think we must
have tab completion for \descibe-verbose.postgres=# \describe-
\describe-extension
\describe-replication-publication \describe-user-mapping
\describe-foreign-data-wrapper
\describe-replication-subscription \describe-view
\describe-foreign-server \describe-role
\describe-window-function
\describe-foreign-table \describe-rule
...
I just confirmed that there isn't tab completion for the existing S/+
options, so it's hard to justify them for the equivalent verbose suffixes.
(1 row)
Invalid command \describe. Try \? for help.
I think this status is causing the problem.
+ /* standard listing of interesting things */ + success = listTables("tvmsE", NULL, show_verbose, show_system); + } + status = PSQL_CMD_UNKNOWN;
I'll look into this, thanks!
- Confusion about \desc and \desC
There is confusion while running the \desc command. I know the problem,
but the user may confuse by this.
postgres=# \desC
List of foreign servers
Name | Owner | Foreign-data wrapper
------+-------+----------------------
(0 rows)postgres=# \desc
Invalid command \desc. Try \? for help.- Auto-completion of commands.
There is some more confusion in the completion of commands.This command shows List of aggregates.
postgres=# \describe-aggregate-function
List of aggregate functions
Schema | Name | Result data type | Argument data types | Description
--------+------+------------------+---------------------+-------------
(0 rows)This command shows a list of relation "\d"
postgres=# \describe-aggregatE-function
List of relations
Schema | Name | Type | Owner
--------+------+-------+---------
public | foo | table | vagrant
(1 row)This command also shows a list of relations "\d".
postgres=# \describe-aggr
List of relations
Schema | Name | Type | Owner
--------+------+-------+---------
public | foo | table | vagrant
(1 row)This command shows error messages.
postgres=# \descr
Invalid command \descr. Try \? for help.
I will look into it.
I have done a brief code review except for the documentation code. I
don't like this codeif (cmd_match(cmd,"describe-aggregate-function"))
success = describeAggregates(pattern, show_verbose, show_system);
else if (cmd_match(cmd,
"describe-access-method"))
success = describeAccessMethods(pattern,
show_verbose);
else if (cmd_match(cmd,
"describe-tablespace"))
success = describeTablespaces(pattern,
show_verbose);
else if (cmd_match(cmd,
"describe-conversion"))
success = listConversions(pattern,
show_verbose, show_system);
else if (cmd_match(cmd, "describe-cast"))
success = listCasts(pattern, show_verboseThis can be achieved with the list/array/hash table, so I have changed
that code in the attached patch just for a sample if you want I can do that
for whole code.
There's some problems with a hash table. The function signatures vary quite
a lot, and some require additional psql_scan_slash_options to be called.
The hash option, if implemented, probably should be expanded to all slash
commands, at which point maybe it belongs in psqlscanslash.l...
Show quoted text
On 2/25/19 9:44 PM, Robert Haas wrote:
On Sat, Feb 23, 2019 at 7:19 PM Andres Freund <andres@anarazel.de> wrote:
Sure, but it was late, and we have far more patches than we can deal
with. Many of them much much older than this.More importantly, at least in my opinion, is that this is one of those
questions that people tend to have very strong feelings about. Doing
something at the last minute risks people not feeling that they had an
adequate time to express those feelings before something got shipped.
Not everybody reads this list every day, or tests every new commit as
soon as it goes into the tree.
I agree with Andres and Robert. This patch should be pushed to PG13.
I'll do that on March 8 unless there is a compelling argument not to.
Regards,
--
-David
david@pgmasters.net
I agree with Andres and Robert. This patch should be pushed to PG13.
I'll do that on March 8 unless there is a compelling argument not to.
No objection. I'll continue to work on it, though.
On Mon, Mar 4, 2019 at 1:45 PM Corey Huinker <corey.huinker@gmail.com>
wrote:
- Tab completion for \descibe-verbose.
I know that \d+ tab completion is also not there, but I think we must
have tab completion for \descibe-verbose.postgres=# \describe-
\describe-extension
\describe-replication-publication \describe-user-mapping
\describe-foreign-data-wrapper
\describe-replication-subscription \describe-view
\describe-foreign-server \describe-role
\describe-window-function
\describe-foreign-table \describe-rule
...I just confirmed that there isn't tab completion for the existing S/+
options, so it's hard to justify them for the equivalent verbose suffixes.
We can add completions for describe[-thing-]-verbose, but the
auto-completions start to run into combinatoric complexity, and the
original short-codes don't do that completion, probably for the same reason.
+ success =
listTables("tvmsE", NULL, show_verbose, show_system); + } + status = PSQL_CMD_UNKNOWN;I'll look into this, thanks!
This was fixed, good find.
- Confusion about \desc and \desC
There is confusion while running the \desc command. I know the problem,
but the user may confuse by this.
postgres=# \desC
List of foreign servers
Name | Owner | Foreign-data wrapper
------+-------+----------------------
(0 rows)postgres=# \desc
Invalid command \desc. Try \? for help.
I've changed the code to first strip out 0-1 instances of "-verbose" and
"-system" and the remaining string must be an exact match of a describe
command or it's an error. This same system could be applied to the short
commands to strip out 'S' and '+' and it might clean up the original code a
bit.
This command shows a list of relation "\d"
postgres=# \describe-aggregatE-function
List of relations
Schema | Name | Type | Owner
--------+------+-------+---------
public | foo | table | vagrant
(1 row)
Same issue, same fix.
I have done a brief code review except for the documentation code. I
don't like this codeif (cmd_match(cmd,"describe-aggregate-function"))
success = describeAggregates(pattern, show_verbose, show_system);
else if (cmd_match(cmd,
"describe-access-method"))
success =
describeAccessMethods(pattern, show_verbose);
else if (cmd_match(cmd,
"describe-tablespace"))
success = describeTablespaces(pattern,
show_verbose);
else if (cmd_match(cmd,
"describe-conversion"))
success = listConversions(pattern,
show_verbose, show_system);
else if (cmd_match(cmd, "describe-cast"))
success = listCasts(pattern,
show_verboseThis can be achieved with the list/array/hash table, so I have changed
that code in the attached patch just for a sample if you want I can do that
for whole code.There's some problems with a hash table. The function signatures vary
quite a lot, and some require additional psql_scan_slash_options to be
called. The hash option, if implemented, probably should be expanded to all
slash commands, at which point maybe it belongs in psqlscanslash.l...
As I suspected, there's a lot of variance in the function signatures of the
various listSomething()/describeSomething() commands,
and listDbRoleSettings requires a second pattern to be scanned, and as far
as I know PsqlScanState isn't known inside describe.h, so building and
using a hash table would be a lot of work for uncertain gain. The original
code just plows through strings in alphabetical order, breaking things up
by comparing leading characters, so I largely did the same at the
des/decribe levels.
Instead of a hash table, It might be fun to write something that takes a
list of alphabetized strings, and builds a binary search tree at compile
time, but that would only work for the long form commands, the short forms
that allow filters like df[anptw]+ and d[tvmisE]+ effectively defeat any
attempt at hashing or btree-ing that I can presently imagine.
Having said that, here's v3 of the patch.
Since this is now waiting for v13, there's a bit more time to entertain the
question of whether we'd rather have these in psql or in a new server
command DESCRIBE [verbose] [system], and if so, whether the output of that
would itself be query-able or not.
Attachments:
0001-Add-describe-commands-to-compliment-d-commands.patchtext/x-patch; charset=US-ASCII; name=0001-Add-describe-commands-to-compliment-d-commands.patchDownload
From 6ff5ebd39a5bda3c2b398ac0b0062bd629f3c877 Mon Sep 17 00:00:00 2001
From: Corey Huinker <corey.huinker@gmail.com>
Date: Fri, 8 Mar 2019 23:38:10 +0000
Subject: [PATCH] Add describe commands to compliment d commands
---
doc/src/sgml/ref/psql-ref.sgml | 175 ++++++++++++++++++--------
src/bin/psql/command.c | 190 +++++++++++++++++++++++-----
src/bin/psql/command.h | 1 -
src/bin/psql/describe.c | 39 ++++++
src/bin/psql/describe.h | 15 ++-
src/bin/psql/tab-complete.c | 222 ++++++++++++++++++++++++---------
6 files changed, 505 insertions(+), 137 deletions(-)
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index d7539ae743..afdc212cd5 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -871,6 +871,17 @@ testdb=>
same line.
</para>
+ <para>
+ The family of meta-commands starting with <literal>\d</literal> often
+ have an equivalent <literal>\describe-</literal> "long form" command.
+ The long-form commands often have the suffixes <literal>-system</literal>
+ and <literal>-verbose</literal> which are the equivalent of the
+ short form suffixes <literal>S</literal> and <literal>+</literal>
+ respectively. The long form suffixes cannot be used on the short form
+ variants. Every <literal>\describe</literal> variant has an equivalent
+ short form variant.
+ </para>
+
<para>
The following meta-commands are defined:
@@ -1147,6 +1158,7 @@ testdb=>
<varlistentry>
<term><literal>\d[S+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe[-system][-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
@@ -1165,13 +1177,13 @@ testdb=>
</para>
<para>
- For some types of relation, <literal>\d</literal> shows additional information
+ For some types of relation, <literal>\d</literal> (and <literal>\describe</literal> shows additional information
for each column: column values for sequences, indexed expressions for
indexes, and foreign data wrapper options for foreign tables.
</para>
<para>
- The command form <literal>\d+</literal> is identical, except that
+ The command forms <literal>\d+</literal> and <literal>\describe-verbose</literal> are identical to <literal>\d</literal>, except that
more information is displayed: any comments associated with the
columns of the table are shown, as is the presence of OIDs in the
table, the view definition if the relation is a view, a non-default
@@ -1181,13 +1193,13 @@ testdb=>
<para>
By default, only user-created objects are shown; supply a
- pattern or the <literal>S</literal> modifier to include system
+ pattern or the <literal>S</literal> modifier for <literal>\d</literal> or <literal>-verbose</literal> modifier for <literal>\describe</literal> modifier to include system
objects.
</para>
<note>
<para>
- If <command>\d</command> is used without a
+ If <command>\d</command> / <command>\describe</command> is used without a
<replaceable class="parameter">pattern</replaceable> argument, it is
equivalent to <command>\dtvmsE</command> which will show a list of
all visible tables, views, materialized views, sequences and
@@ -1200,6 +1212,7 @@ testdb=>
<varlistentry>
<term><literal>\da[S] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-aggregate[-system] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
@@ -1208,7 +1221,7 @@ testdb=>
class="parameter">pattern</replaceable>
is specified, only aggregates whose names match the pattern are shown.
By default, only user-created objects are shown; supply a
- pattern or the <literal>S</literal> modifier to include system
+ pattern or the <literal>S</literal>/<literal>-system</literal> modifier to include system
objects.
</para>
</listitem>
@@ -1216,13 +1229,14 @@ testdb=>
<varlistentry>
<term><literal>\dA[+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-access-method[-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists access methods. If <replaceable
class="parameter">pattern</replaceable> is specified, only access
methods whose names match the pattern are shown. If
- <literal>+</literal> is appended to the command name, each access
+ <literal>+</literal>/<literal>-verbose</literal> is appended to the command name, each access
method is listed with its associated handler function and description.
</para>
</listitem>
@@ -1230,13 +1244,14 @@ testdb=>
<varlistentry>
<term><literal>\db[+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-tablespace[-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists tablespaces. If <replaceable
class="parameter">pattern</replaceable>
is specified, only tablespaces whose names match the pattern are shown.
- If <literal>+</literal> is appended to the command name, each tablespace
+ If <literal>+</literal>/<literal>-verbose</literal> is appended to the command name, each tablespace
is listed with its associated options, on-disk size, permissions and
description.
</para>
@@ -1246,6 +1261,7 @@ testdb=>
<varlistentry>
<term><literal>\dc[S+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-conversion[-system][-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists conversions between character-set encodings.
@@ -1253,9 +1269,9 @@ testdb=>
is specified, only conversions whose names match the pattern are
listed.
By default, only user-created objects are shown; supply a
- pattern or the <literal>S</literal> modifier to include system
+ pattern or the <literal>S</literal>/<literal>-system</literal> modifier to include system
objects.
- If <literal>+</literal> is appended to the command name, each object
+ If <literal>+</literal>/<literal>-verbose</literal> is appended to the command name, each object
is listed with its associated description.
</para>
</listitem>
@@ -1264,13 +1280,14 @@ testdb=>
<varlistentry>
<term><literal>\dC[+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-cast[-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists type casts.
If <replaceable class="parameter">pattern</replaceable>
is specified, only casts whose source or target types match the
pattern are listed.
- If <literal>+</literal> is appended to the command name, each object
+ If <literal>+</literal>/<literal>-verbose</literal> is appended to the command name, each object
is listed with its associated description.
</para>
</listitem>
@@ -1279,6 +1296,11 @@ testdb=>
<varlistentry>
<term><literal>\dd[S] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-constraint[-system] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-operator-class[-system] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-operator-family[-system] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-rule[-system] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-trigger[-system] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Shows the descriptions of objects of type <literal>constraint</literal>,
@@ -1293,7 +1315,7 @@ testdb=>
objects of the appropriate type if no argument is given. But in either
case, only objects that have a description are listed.
By default, only user-created objects are shown; supply a
- pattern or the <literal>S</literal> modifier to include system
+ pattern or the <literal>S</literal>/<literal>-system</literal> modifier to include system
objects.
</para>
@@ -1308,15 +1330,16 @@ testdb=>
<varlistentry>
<term><literal>\dD[S+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-domain[-system][-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists domains. If <replaceable
class="parameter">pattern</replaceable>
is specified, only domains whose names match the pattern are shown.
By default, only user-created objects are shown; supply a
- pattern or the <literal>S</literal> modifier to include system
+ pattern or the <literal>S</literal>/<literal>-system</literal> modifier to include system
objects.
- If <literal>+</literal> is appended to the command name, each object
+ If <literal>+</literal>/<literal>-verbose</literal> is appended to the command name, each object
is listed with its associated permissions and description.
</para>
</listitem>
@@ -1325,6 +1348,7 @@ testdb=>
<varlistentry>
<term><literal>\ddp [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-default-access-privelege [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists default access privilege settings. An entry is shown for
@@ -1352,6 +1376,11 @@ testdb=>
<term><literal>\ds[S+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<term><literal>\dt[S+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<term><literal>\dv[S+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-index[-system][-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-materialized-view[-system][-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-sequence[-system][-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-table[-system][-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-view[-system][-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
@@ -1360,16 +1389,22 @@ testdb=>
<literal>t</literal>, and <literal>v</literal>
stand for foreign table, index, materialized view, sequence, table, and view,
respectively.
- You can specify any or all of
+ In the short forms, you can specify any or all of
these letters, in any order, to obtain a listing of objects
of these types. For example, <literal>\dit</literal> lists indexes
- and tables. If <literal>+</literal> is
+ and tables. If <literal>+</literal>/<literal>-verbose</literal> is
appended to the command name, each object is listed with its
physical size on disk and its associated description, if any.
+ <literal>\describe-foreign-table</literal> is equivalent to <literal>\dE</literal>.
+ <literal>\describe-index</literal> is equivalent to <literal>\di</literal>.
+ <literal>\describe-materialized-view</literal> is equivalent to <literal>\dm</literal>.
+ <literal>\describe-sequence</literal> is equivalent to <literal>\ds</literal>.
+ <literal>\describe-table</literal> is equivalent to <literal>\dt</literal>.
+ <literal>\describe-view</literal> is equivalent to <literal>\dv</literal>.
If <replaceable class="parameter">pattern</replaceable> is
specified, only objects whose names match the pattern are listed.
By default, only user-created objects are shown; supply a
- pattern or the <literal>S</literal> modifier to include system
+ pattern or the <literal>S</literal>/<literal>-system</literal> modifier to include system
objects.
</para>
</listitem>
@@ -1378,13 +1413,14 @@ testdb=>
<varlistentry>
<term><literal>\des[+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-foreign-servers[-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists foreign servers (mnemonic: <quote>external
servers</quote>).
If <replaceable class="parameter">pattern</replaceable> is
specified, only those servers whose name matches the pattern
- are listed. If the form <literal>\des+</literal> is used, a
+ are listed. If the forms <literal>\des+</literal> or <literal>\describe-foreign-server-verbose</literal> are used, a
full description of each server is shown, including the
server's access privileges, type, version, options, and description.
</para>
@@ -1394,13 +1430,14 @@ testdb=>
<varlistentry>
<term><literal>\det[+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-foreign-table[-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists foreign tables (mnemonic: <quote>external tables</quote>).
If <replaceable class="parameter">pattern</replaceable> is
specified, only entries whose table name or schema name matches
- the pattern are listed. If the form <literal>\det+</literal>
- is used, generic options and the foreign table description
+ the pattern are listed. If the forms <literal>\det+</literal> or <literal>\describe-foreign-table-verbose</literal>
+ are used, generic options and the foreign table description
are also displayed.
</para>
</listitem>
@@ -1409,19 +1446,20 @@ testdb=>
<varlistentry>
<term><literal>\deu[+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-user-mapping[-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists user mappings (mnemonic: <quote>external
users</quote>).
If <replaceable class="parameter">pattern</replaceable> is
specified, only those mappings whose user names match the
- pattern are listed. If the form <literal>\deu+</literal> is
+ pattern are listed. If the forms <literal>\deu+</literal> or <literal>\describe-user-mapping-verboze</literal> are
used, additional information about each mapping is shown.
</para>
<caution>
<para>
- <literal>\deu+</literal> might also display the user name and
+ <literal>\deu+</literal> and <literal>\describe-user-mapping-verboze</literal> might also display the user name and
password of the remote user, so care should be taken not to
disclose them.
</para>
@@ -1432,14 +1470,15 @@ testdb=>
<varlistentry>
<term><literal>\dew[+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-foreign-data-wrapper[-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists foreign-data wrappers (mnemonic: <quote>external
wrappers</quote>).
If <replaceable class="parameter">pattern</replaceable> is
specified, only those foreign-data wrappers whose name matches
- the pattern are listed. If the form <literal>\dew+</literal>
- is used, the access privileges, options, and description of the
+ the pattern are listed. If the forms <literal>\dew+</literal> or <literal>\describe-foreign-data-wrapper-verbose</literal>
+ are used, the access privileges, options, and description of the
foreign-data wrapper are also shown.
</para>
</listitem>
@@ -1448,25 +1487,37 @@ testdb=>
<varlistentry>
<term><literal>\df[anptwS+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-function[-system][-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-normal-function[-system][-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-procedure[-system][-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-aggregate-function[-system][-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-trigger-function[-system][-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-window-function[-system][-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists functions, together with their result data types, argument data
types, and function types, which are classified as <quote>agg</quote>
(aggregate), <quote>normal</quote>, <quote>procedure</quote>, <quote>trigger</quote>, or <quote>window</quote>.
- To display only functions
+ In the short form, to display only functions
of specific type(s), add the corresponding letters <literal>a</literal>,
<literal>n</literal>, <literal>p</literal>, <literal>t</literal>, or <literal>w</literal> to the command.
If <replaceable
class="parameter">pattern</replaceable> is specified, only
functions whose names match the pattern are shown.
By default, only user-created
- objects are shown; supply a pattern or the <literal>S</literal>
+ objects are shown; supply a pattern or the <literal>S</literal>/<literal>-system</literal>
modifier to include system objects.
- If the form <literal>\df+</literal> is used, additional information
+ If the form <literal>\df+</literal> or the <literal>-verbose</literal> suffix is used, additional information
about each function is shown, including volatility,
parallel safety, owner, security classification, access privileges,
language, source code and description.
+ <literal>\describe-function</literal> is the equivalent of <literal>\df</literal>.
+ <literal>\describe-normal-function</literal> is the equivalent of <literal>\dff</literal>.
+ <literal>\describe-procedure</literal> is the equivalent of <literal>\dfp</literal>.
+ <literal>\describe-aggregate-function</literal> is the equivalent of <literal>\dfa</literal>.
+ <literal>\describe-trigger-function</literal> is the equivalent of <literal>\dft</literal>.
+ <literal>\describe-window-function</literal> is the equivalent of <literal>\dfw</literal>.
</para>
<tip>
@@ -1482,12 +1533,13 @@ testdb=>
<varlistentry>
<term><literal>\dF[+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-text-search-configuration[-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists text search configurations.
If <replaceable class="parameter">pattern</replaceable> is specified,
only configurations whose names match the pattern are shown.
- If the form <literal>\dF+</literal> is used, a full description of
+ If the form <literal>\dF+</literal> or <literal>\describe-text-search-configuration-verbose</literal> is used, a full description of
each configuration is shown, including the underlying text search
parser and the dictionary list for each parser token type.
</para>
@@ -1496,12 +1548,13 @@ testdb=>
<varlistentry>
<term><literal>\dFd[+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-text-search-dictionary[-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists text search dictionaries.
If <replaceable class="parameter">pattern</replaceable> is specified,
only dictionaries whose names match the pattern are shown.
- If the form <literal>\dFd+</literal> is used, additional information
+ If the form <literal>\dFd+</literal> or <literal>\describe-text-search-dictionary-verbose</literal> is used, additional information
is shown about each selected dictionary, including the underlying
text search template and the option values.
</para>
@@ -1510,12 +1563,13 @@ testdb=>
<varlistentry>
<term><literal>\dFp[+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-text-search-parser[-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists text search parsers.
If <replaceable class="parameter">pattern</replaceable> is specified,
only parsers whose names match the pattern are shown.
- If the form <literal>\dFp+</literal> is used, a full description of
+ If the form <literal>\dFp+</literal> or <literal>\describe-text-search-parser-verbose</literal> is used, a full description of
each parser is shown, including the underlying functions and the
list of recognized token types.
</para>
@@ -1524,12 +1578,13 @@ testdb=>
<varlistentry>
<term><literal>\dFt[+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-text-search-template[-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists text search templates.
If <replaceable class="parameter">pattern</replaceable> is specified,
only templates whose names match the pattern are shown.
- If the form <literal>\dFt+</literal> is used, additional information
+ If the form <literal>\dFt+</literal> or <literal>\describe-text-search-template-verbose</literal> is used, additional information
is shown about each template, including the underlying function names.
</para>
</listitem>
@@ -1538,6 +1593,7 @@ testdb=>
<varlistentry>
<term><literal>\dg[S+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-role[-system][-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists database roles.
@@ -1545,10 +1601,10 @@ testdb=>
unified into <quote>roles</quote>, this command is now equivalent to
<literal>\du</literal>.)
By default, only user-created roles are shown; supply the
- <literal>S</literal> modifier to include system roles.
+ <literal>S</literal> or <literal>-system</literal> modifier to include system roles.
If <replaceable class="parameter">pattern</replaceable> is specified,
only those roles whose names match the pattern are listed.
- If the form <literal>\dg+</literal> is used, additional information
+ If the suffix <literal>+</literal> or <literal>-verbose></literal> is specified, additional information
is shown about each role; currently this adds the comment for each
role.
</para>
@@ -1568,14 +1624,15 @@ testdb=>
<varlistentry>
<term><literal>\dL[S+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-procedural-language[-system][-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists procedural languages. If <replaceable
class="parameter">pattern</replaceable>
is specified, only languages whose names match the pattern are listed.
By default, only user-created languages
- are shown; supply the <literal>S</literal> modifier to include system
- objects. If <literal>+</literal> is appended to the command name, each
+ are shown; supply the <literal>S</literal> or <literal>-system</literal> modifier to include system
+ objects. If <literal>+</literal> or <literal>-verbose</literal> is appended to the command name, each
language is listed with its call handler, validator, access privileges,
and whether it is a system object.
</para>
@@ -1585,6 +1642,8 @@ testdb=>
<varlistentry>
<term><literal>\dn[S+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-schema[-system][-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-namespace[-system][-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
@@ -1592,8 +1651,8 @@ testdb=>
class="parameter">pattern</replaceable>
is specified, only schemas whose names match the pattern are listed.
By default, only user-created objects are shown; supply a
- pattern or the <literal>S</literal> modifier to include system objects.
- If <literal>+</literal> is appended to the command name, each object
+ pattern or the <literal>S</literal> or <literal>-system</literal> modifier to include system objects.
+ If <literal>+</literal> (or <literal>-verbose</literal> is appended to the command name, each object
is listed with its associated permissions and description, if any.
</para>
</listitem>
@@ -1602,15 +1661,16 @@ testdb=>
<varlistentry>
<term><literal>\do[S+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-operator[-system][-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists operators with their operand and result types.
If <replaceable class="parameter">pattern</replaceable> is
specified, only operators whose names match the pattern are listed.
By default, only user-created objects are shown; supply a
- pattern or the <literal>S</literal> modifier to include system
+ pattern or the <literal>S</literal> or <literal>-system</literal> modifier to include system
objects.
- If <literal>+</literal> is appended to the command name,
+ If <literal>+</literal> or <literal>-verbose</literal> is appended to the command name,
additional information about each operator is shown, currently just
the name of the underlying function.
</para>
@@ -1620,14 +1680,15 @@ testdb=>
<varlistentry>
<term><literal>\dO[S+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-collation[-system][-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists collations.
If <replaceable class="parameter">pattern</replaceable> is
specified, only collations whose names match the pattern are
listed. By default, only user-created objects are shown;
- supply a pattern or the <literal>S</literal> modifier to
- include system objects. If <literal>+</literal> is appended
+ supply a pattern or the <literal>S</literal> or <literal>-system</literal> modifier to
+ include system objects. If <literal>+</literal> or <literal>-verbose</literal> is appended
to the command name, each collation is listed with its associated
description, if any.
Note that only collations usable with the current database's encoding
@@ -1640,6 +1701,7 @@ testdb=>
<varlistentry>
<term><literal>\dp [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-privilege [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists tables, views and sequences with their
@@ -1661,6 +1723,7 @@ testdb=>
<varlistentry>
<term><literal>\drds [ <link linkend="app-psql-patterns"><replaceable class="parameter">role-pattern</replaceable></link> [ <link linkend="app-psql-patterns"><replaceable class="parameter">database-pattern</replaceable></link> ] ]</literal></term>
+ <term><literal>\describe-defined-configuration-setting [ <link linkend="app-psql-patterns"><replaceable class="parameter">role-pattern</replaceable></link> [ <link linkend="app-psql-patterns"><replaceable class="parameter">database-pattern</replaceable></link> ] ]</literal></term>
<listitem>
<para>
Lists defined configuration settings. These settings can be
@@ -1683,13 +1746,14 @@ testdb=>
<varlistentry>
<term><literal>\dRp[+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-replication-publication[-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists replication publications.
If <replaceable class="parameter">pattern</replaceable> is
specified, only those publications whose names match the pattern are
listed.
- If <literal>+</literal> is appended to the command name, the tables
+ If <literal>+</literal> or <literal>-verbose</literal> is appended to the command name, the tables
associated with each publication are shown as well.
</para>
</listitem>
@@ -1697,13 +1761,14 @@ testdb=>
<varlistentry>
<term><literal>\dRs[+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-replication-subscription[-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists replication subscriptions.
If <replaceable class="parameter">pattern</replaceable> is
specified, only those subscriptions whose names match the pattern are
listed.
- If <literal>+</literal> is appended to the command name, additional
+ If <literal>+</literal> or <literal>-verbose</literal> is appended to the command name, additional
properties of the subscriptions are shown.
</para>
</listitem>
@@ -1711,16 +1776,17 @@ testdb=>
<varlistentry>
<term><literal>\dT[S+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-type[-system][-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists data types.
If <replaceable class="parameter">pattern</replaceable> is
specified, only types whose names match the pattern are listed.
- If <literal>+</literal> is appended to the command name, each type is
+ If <literal>+</literal> or <literal>-verbose</literal> is appended to the command name, each type is
listed with its internal name and size, its allowed values
if it is an <type>enum</type> type, and its associated permissions.
By default, only user-created objects are shown; supply a
- pattern or the <literal>S</literal> modifier to include system
+ pattern or the <literal>S</literal> or <literal>-system</literal> modifier to include system
objects.
</para>
</listitem>
@@ -1728,6 +1794,7 @@ testdb=>
<varlistentry>
<term><literal>\du[S+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-role[S+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists database roles.
@@ -1735,10 +1802,10 @@ testdb=>
unified into <quote>roles</quote>, this command is now equivalent to
<literal>\dg</literal>.)
By default, only user-created roles are shown; supply the
- <literal>S</literal> modifier to include system roles.
+ <literal>S</literal> or <literal>-system</literal> modifier to include system roles.
If <replaceable class="parameter">pattern</replaceable> is specified,
only those roles whose names match the pattern are listed.
- If the form <literal>\du+</literal> is used, additional information
+ If the suffix <literal>+</literal> or <literal>-verbose</literal> is used, additional information
is shown about each role; currently this adds the comment for each
role.
</para>
@@ -1747,13 +1814,14 @@ testdb=>
<varlistentry>
<term><literal>\dx[+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-extension[-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists installed extensions.
If <replaceable class="parameter">pattern</replaceable>
is specified, only those extensions whose names match the pattern
are listed.
- If the form <literal>\dx+</literal> is used, all the objects belonging
+ If the suffix <literal>+</literal> or <literal>-verbose</literal> is used, all the objects belonging
to each matching extension are listed.
</para>
</listitem>
@@ -1761,15 +1829,24 @@ testdb=>
<varlistentry>
<term><literal>\dy[+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\describe-event-trigger[-verbose] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists event triggers.
If <replaceable class="parameter">pattern</replaceable>
is specified, only those event triggers whose names match the pattern
are listed.
- If <literal>+</literal> is appended to the command name, each object
+ If <literal>+</literal> or <literal>-verbose</literal> is appended to the command name, each object
is listed with its associated description.
</para>
+
+ <tip>
+ <para>
+ Long form commands can aid in clarity when sharing code with others.
+ Using tab completion can reduce typing and aid in the discovery of new
+ <literal>\describe</literal> commands.
+ </para>
+ </tip>
</listitem>
</varlistentry>
@@ -2305,7 +2382,7 @@ SELECT
character set encodings, and access privileges.
If <replaceable class="parameter">pattern</replaceable> is specified,
only databases whose names match the pattern are listed.
- If <literal>+</literal> is appended to the command name, database
+ If <literal>+</literal> (or <literal>-verbose</literal> in the long form) is appended to the command name, database
sizes, default tablespaces, and descriptions are also displayed.
(Size information is only available for databases that the current
user can connect to.)
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index ab259c473a..5ec4fb7a34 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -685,6 +685,27 @@ exec_command_crosstabview(PsqlScanState scan_state, bool active_branch)
return status;
}
+/*
+ * \ddp pattern1 [pattern2]
+ * \describe-defined-configuration-setting pattern1 [pattern2]
+ */
+static bool
+describe_defined_configuration_setting(PsqlScanState scan_state, const char *pattern)
+{
+ char *pattern2 = NULL;
+ bool success;
+
+ if (pattern)
+ pattern2 = psql_scan_slash_option(scan_state,
+ OT_NORMAL, NULL, true);
+ success = listDbRoleSettings(pattern, pattern2);
+
+ if (pattern2)
+ free(pattern2);
+
+ return success;
+}
+
/*
* \d* commands
*/
@@ -712,11 +733,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
case '\0':
case '+':
case 'S':
- if (pattern)
- success = describeTableDetails(pattern, show_verbose, show_system);
- else
- /* standard listing of interesting things */
- success = listTables("tvmsE", NULL, show_verbose, show_system);
+ success = describeAnything(pattern, show_verbose, show_system);
break;
case 'A':
success = describeAccessMethods(pattern, show_verbose);
@@ -795,17 +812,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
break;
case 'r':
if (cmd[2] == 'd' && cmd[3] == 's')
- {
- char *pattern2 = NULL;
-
- if (pattern)
- pattern2 = psql_scan_slash_option(scan_state,
- OT_NORMAL, NULL, true);
- success = listDbRoleSettings(pattern, pattern2);
-
- if (pattern2)
- free(pattern2);
- }
+ success = describe_defined_configuration_setting(scan_state, pattern);
else
status = PSQL_CMD_UNKNOWN;
break;
@@ -813,10 +820,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
switch (cmd[2])
{
case 'p':
- if (show_verbose)
- success = describePublications(pattern);
- else
- success = listPublications(pattern);
+ success = describeReplicationPublication(pattern, show_verbose);
break;
case 's':
success = describeSubscriptions(pattern, show_verbose);
@@ -849,12 +853,10 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
break;
}
break;
- case 'e': /* SQL/MED subsystem */
+ case 'e':
+ /* SQL/MED subsystem and longform describe */
switch (cmd[2])
{
- case 's':
- success = listForeignServers(pattern, show_verbose);
- break;
case 'u':
success = listUserMappings(pattern, show_verbose);
break;
@@ -864,16 +866,148 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
case 't':
success = listForeignTables(pattern, show_verbose);
break;
+ case 's':
+ if (cmd[3] != 'c')
+ {
+ /* no \describe, just \des */
+ success = listForeignServers(pattern, show_verbose);
+ break;
+ }
+ else if (strncmp(cmd, "describe", strlen("describe")) == 0)
+ {
+ char *command_detail = pg_malloc(1 + strlen(cmd) - strlen("describe"));
+ char *suffix_pos;
+
+ /* copy everyting after "describe[-]" to the detail string */
+ strcpy(command_detail,
+ (cmd[strlen("describe")] == '-') ?
+ &cmd[strlen("describe-")] :
+ &cmd[strlen("describe")]);
+
+ /* search for and remove first -verbose found */
+ show_verbose = false;
+ suffix_pos = strstr(command_detail, "-verbose");
+ if (suffix_pos != NULL)
+ {
+ show_verbose = true;
+ strcpy(suffix_pos, &suffix_pos[strlen("-verbose")]);
+ }
+
+ /* search for and remove first -system found */
+ show_system = false;
+ suffix_pos = strstr(command_detail, "-system");
+ if (suffix_pos != NULL)
+ {
+ show_system = true;
+ strcpy(suffix_pos, &suffix_pos[strlen("-system")]);
+ }
+
+ /* most common case is \describe + suffixes */
+ if (command_detail[0] == '\0')
+ success = describeAnything(pattern, show_verbose, show_system);
+ else if (strcmp(command_detail, "access-method") == 0)
+ success = describeAccessMethods(pattern, show_verbose);
+ else if (strcmp(command_detail, "aggregate-function") == 0)
+ success = describeAggregates(pattern, show_verbose, show_system);
+ else if (strcmp(command_detail, "cast") == 0)
+ success = listCasts(pattern, show_verbose);
+ else if (strcmp(command_detail, "constraint") == 0)
+ success = objectDescription(pattern, show_system);
+ else if (strcmp(command_detail, "conversion") == 0)
+ success = listConversions(pattern, show_verbose, show_system);
+ else if (strcmp(command_detail, "constraint") == 0)
+ success = listConversions(pattern, show_verbose, show_system);
+ else if (strcmp(command_detail, "collation") == 0)
+ success = listCollations(pattern, show_verbose, show_system);
+ else if (strcmp(command_detail, "defined-configuration-setting") == 0)
+ success = describe_defined_configuration_setting(scan_state, pattern);
+ else if (strcmp(command_detail, "default-access-privelege") == 0)
+ success = listDefaultACLs(pattern);
+ else if (strcmp(command_detail, "domain") == 0)
+ success = listDomains(pattern, show_verbose, show_system);
+ else if (strcmp(command_detail, "extension") == 0)
+ success = describeExtension(pattern, show_verbose);
+ else if (strcmp(command_detail, "event-trigger") == 0)
+ success = listEventTriggers(pattern, show_verbose);
+ else if (strcmp(command_detail, "foreign-data-wrapper") == 0)
+ success = listForeignDataWrappers(pattern, show_verbose);
+ else if (strcmp(command_detail, "foreign-server") == 0)
+ success = listForeignServers(pattern, show_verbose);
+ else if (strcmp(command_detail, "foreign-table") == 0)
+ success = listForeignTables(pattern, show_verbose);
+ else if (strcmp(command_detail, "function") == 0)
+ success = describeFunctions("\0", pattern, show_verbose, show_system);
+ else if (strcmp(command_detail, "index") == 0)
+ success = listTables("i", pattern, show_verbose, show_system);
+ else if (strcmp(command_detail, "materialized-view") == 0)
+ success = listTables("m", pattern, show_verbose, show_system);
+ else if (strcmp(command_detail, "namespace") == 0)
+ success = listSchemas(pattern, show_verbose, show_system);
+ else if (strcmp(command_detail, "normal-function") == 0)
+ success = describeFunctions("n", pattern, show_verbose, show_system);
+ else if (strcmp(command_detail, "operator") == 0)
+ success = describeOperators(pattern, show_verbose, show_system);
+ else if (strcmp(command_detail, "operator-class") == 0)
+ success = objectDescription(pattern, show_system);
+ else if (strcmp(command_detail, "operator-family") == 0)
+ success = objectDescription(pattern, show_system);
+ else if (strcmp(command_detail, "privilege") == 0)
+ success = permissionsList(pattern);
+ else if (strcmp(command_detail, "procedural-language") == 0)
+ success = listLanguages(pattern, show_verbose, show_system);
+ else if (strcmp(command_detail, "procedure") == 0)
+ success = describeFunctions("p", pattern, show_verbose, show_system);
+ else if (strcmp(command_detail, "replication-publication") == 0)
+ success = describeReplicationPublication(pattern, show_verbose);
+ else if (strcmp(command_detail, "replication-subscription") == 0)
+ success = describeSubscriptions(pattern, show_verbose);
+ else if (strcmp(command_detail, "role") == 0)
+ success = describeRoles(pattern, show_verbose, show_system);
+ else if (strcmp(command_detail, "rule") == 0)
+ success = objectDescription(pattern, show_system);
+ else if (strcmp(command_detail, "schema") == 0)
+ success = listSchemas(pattern, show_verbose, show_system);
+ else if (strcmp(command_detail, "sequence") == 0)
+ success = listTables("s", pattern, show_verbose, show_system);
+ else if (strcmp(command_detail, "table") == 0)
+ success = listTables("t", pattern, show_verbose, show_system);
+ else if (strcmp(command_detail, "tablespace") == 0)
+ success = describeTablespaces(pattern, show_verbose);
+ else if (strcmp(command_detail, "text-search-configuration") == 0)
+ success = listTSConfigs(pattern, show_verbose);
+ else if (strcmp(command_detail, "text-search-dictionary") == 0)
+ success = listTSDictionaries(pattern, show_verbose);
+ else if (strcmp(command_detail, "text-search-parser") == 0)
+ success = listTSParsers(pattern, show_verbose);
+ else if (strcmp(command_detail, "text-search-templates") == 0)
+ success = listTSTemplates(pattern, show_verbose);
+ else if (strcmp(command_detail, "trigger") == 0)
+ success = objectDescription(pattern, show_system);
+ else if (strcmp(command_detail, "trigger-function") == 0)
+ success = describeFunctions("t", pattern, show_verbose, show_system);
+ else if (strcmp(command_detail, "type") == 0)
+ success = describeTypes(pattern, show_verbose, show_system);
+ else if (strcmp(command_detail, "user-mapping") == 0)
+ success = listUserMappings(pattern, show_verbose);
+ else if (strcmp(command_detail, "view") == 0)
+ success = listTables("v", pattern, show_verbose, show_system);
+ else if (strcmp(command_detail, "window-function") == 0)
+ success = describeFunctions("w", pattern, show_verbose, show_system);
+ else
+ status = PSQL_CMD_UNKNOWN;
+ free(command_detail);
+ break;
+ }
+ else
+ status = PSQL_CMD_UNKNOWN;
+ break;
default:
status = PSQL_CMD_UNKNOWN;
break;
}
break;
case 'x': /* Extensions */
- if (show_verbose)
- success = listExtensionContents(pattern);
- else
- success = listExtensions(pattern);
+ success = describeExtension(pattern, show_verbose);
break;
case 'y': /* Event Triggers */
success = listEventTriggers(pattern, show_verbose);
diff --git a/src/bin/psql/command.h b/src/bin/psql/command.h
index de748d320e..ed05d0de93 100644
--- a/src/bin/psql/command.h
+++ b/src/bin/psql/command.h
@@ -24,7 +24,6 @@ typedef enum _backslashResult
* resulted in an error */
} backslashResult;
-
extern backslashResult HandleSlashCmds(PsqlScanState scan_state,
ConditionalStack cstack,
PQExpBuffer query_buf,
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 4da6719ce7..ce02472987 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -5611,3 +5611,42 @@ printACLColumn(PQExpBuffer buf, const char *colname)
"pg_catalog.array_to_string(%s, '\\n') AS \"%s\"",
colname, gettext_noop("Access privileges"));
}
+
+/*
+ * \d [pattern]
+ * \describe [pattern]
+ */
+bool
+describeAnything(const char *pattern, bool show_verbose, bool show_system)
+{
+ if (pattern)
+ return describeTableDetails(pattern, show_verbose, show_system);
+ /* standard listing of interesting things */
+ return listTables("tvmsE", NULL, show_verbose, show_system);
+}
+
+/*
+ * \dRp
+ * \describe-replication-publication pattern
+ */
+bool
+describeReplicationPublication(const char *pattern, bool show_verbose)
+{
+ if (show_verbose)
+ return describePublications(pattern);
+
+ return listPublications(pattern);
+}
+
+/*
+ * \dx
+ * \describe-extension
+ */
+bool
+describeExtension(const char *pattern, bool show_verbose)
+{
+ if (show_verbose)
+ return listExtensionContents(pattern);
+
+ return listExtensions(pattern);
+}
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index 4ff1f91f38..34a497461e 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -103,12 +103,21 @@ extern bool listExtensionContents(const char *pattern);
extern bool listEventTriggers(const char *pattern, bool verbose);
/* \dRp */
-bool listPublications(const char *pattern);
+extern bool listPublications(const char *pattern);
/* \dRp+ */
-bool describePublications(const char *pattern);
+extern bool describePublications(const char *pattern);
/* \dRs */
-bool describeSubscriptions(const char *pattern, bool verbose);
+extern bool describeSubscriptions(const char *pattern, bool verbose);
+
+/* \d \describe */
+extern bool describeAnything(const char *pattern, bool show_verbose, bool show_system);
+
+/* \dRp \describe-replication-publication pattern */
+extern bool describeReplicationPublication(const char *pattern, bool show_verbose);
+
+/* \dx \describe-extension */
+extern bool describeExtension(const char *pattern, bool show_verbose);
#endif /* DESCRIBE_H */
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 7b7a88fda3..a83ab73192 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1402,6 +1402,51 @@ psql_completion(const char *text, int start, int end)
"\\w", "\\watch",
"\\z",
"\\!", "\\?",
+ "\\describe-access-method",
+ "\\describe-aggregate-function",
+ "\\describe-aggregate",
+ "\\describe-cast",
+ "\\describe-collation",
+ "\\describe-constraint",
+ "\\describe-conversion",
+ "\\describe-default-access-privelege",
+ "\\describe-defined-configuration-setting",
+ "\\describe-domain",
+ "\\describe-event-trigger",
+ "\\describe-extension",
+ "\\describe-foreign-data-wrapper",
+ "\\describe-foreign-server",
+ "\\describe-foreign-table",
+ "\\describe-function",
+ "\\describe-index",
+ "\\describe-materialized-view",
+ "\\describe-namespace",
+ "\\describe-normal-function",
+ "\\describe-operator-class",
+ "\\describe-operator-family",
+ "\\describe-operator",
+ "\\describe-privilege",
+ "\\describe-procedural-language",
+ "\\describe-procedure",
+ "\\describe-replication-publication",
+ "\\describe-replication-subscription",
+ "\\describe-role",
+ "\\describe-rule",
+ "\\describe-schema",
+ "\\describe-sequence",
+ "\\describe-tablespace",
+ "\\describe-table",
+ "\\describe-text-search-configuration",
+ "\\describe-text-search-dictionary",
+ "\\describe-text-search-parser",
+ "\\describe-text-search-templates",
+ "\\describe-trigger-function",
+ "\\describe-trigger",
+ "\\describe-type",
+ "\\describe-user-mapping",
+ "\\describe-view",
+ "\\describe-window-function",
+ "\\describe",
NULL
};
@@ -3468,63 +3513,128 @@ psql_completion(const char *text, int start, int end)
if (!recognized_connection_string(prev_wd))
COMPLETE_WITH_QUERY(Query_for_list_of_roles);
}
- else if (TailMatchesCS("\\da*"))
- COMPLETE_WITH_VERSIONED_SCHEMA_QUERY(Query_for_list_of_aggregates, NULL);
- else if (TailMatchesCS("\\dA*"))
- COMPLETE_WITH_QUERY(Query_for_list_of_access_methods);
- else if (TailMatchesCS("\\db*"))
- COMPLETE_WITH_QUERY(Query_for_list_of_tablespaces);
- else if (TailMatchesCS("\\dD*"))
- COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_domains, NULL);
- else if (TailMatchesCS("\\des*"))
- COMPLETE_WITH_QUERY(Query_for_list_of_servers);
- else if (TailMatchesCS("\\deu*"))
- COMPLETE_WITH_QUERY(Query_for_list_of_user_mappings);
- else if (TailMatchesCS("\\dew*"))
- COMPLETE_WITH_QUERY(Query_for_list_of_fdws);
- else if (TailMatchesCS("\\df*"))
- COMPLETE_WITH_VERSIONED_SCHEMA_QUERY(Query_for_list_of_functions, NULL);
-
- else if (TailMatchesCS("\\dFd*"))
- COMPLETE_WITH_QUERY(Query_for_list_of_ts_dictionaries);
- else if (TailMatchesCS("\\dFp*"))
- COMPLETE_WITH_QUERY(Query_for_list_of_ts_parsers);
- else if (TailMatchesCS("\\dFt*"))
- COMPLETE_WITH_QUERY(Query_for_list_of_ts_templates);
- /* must be at end of \dF alternatives: */
- else if (TailMatchesCS("\\dF*"))
- COMPLETE_WITH_QUERY(Query_for_list_of_ts_configurations);
-
- else if (TailMatchesCS("\\di*"))
- COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_indexes, NULL);
- else if (TailMatchesCS("\\dL*"))
- COMPLETE_WITH_QUERY(Query_for_list_of_languages);
- else if (TailMatchesCS("\\dn*"))
- COMPLETE_WITH_QUERY(Query_for_list_of_schemas);
- else if (TailMatchesCS("\\dp") || TailMatchesCS("\\z"))
- COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_grantables, NULL);
- else if (TailMatchesCS("\\ds*"))
- COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_sequences, NULL);
- else if (TailMatchesCS("\\dt*"))
- COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, NULL);
- else if (TailMatchesCS("\\dT*"))
- COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_datatypes, NULL);
- else if (TailMatchesCS("\\du*") || TailMatchesCS("\\dg*"))
- COMPLETE_WITH_QUERY(Query_for_list_of_roles);
- else if (TailMatchesCS("\\dv*"))
- COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_views, NULL);
- else if (TailMatchesCS("\\dx*"))
- COMPLETE_WITH_QUERY(Query_for_list_of_extensions);
- else if (TailMatchesCS("\\dm*"))
- COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_matviews, NULL);
- else if (TailMatchesCS("\\dE*"))
- COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_foreign_tables, NULL);
- else if (TailMatchesCS("\\dy*"))
- COMPLETE_WITH_QUERY(Query_for_list_of_event_triggers);
-
- /* must be at end of \d alternatives: */
else if (TailMatchesCS("\\d*"))
- COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_relations, NULL);
+ {
+ if (TailMatchesCS("\\df*"))
+ COMPLETE_WITH_VERSIONED_SCHEMA_QUERY(Query_for_list_of_functions, NULL);
+ else if (TailMatchesCS("\\da*"))
+ COMPLETE_WITH_VERSIONED_SCHEMA_QUERY(Query_for_list_of_aggregates, NULL);
+ else if (TailMatchesCS("\\dA*"))
+ COMPLETE_WITH_QUERY(Query_for_list_of_access_methods);
+ else if (TailMatchesCS("\\db*"))
+ COMPLETE_WITH_QUERY(Query_for_list_of_tablespaces);
+ else if (TailMatchesCS("\\dD*"))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_domains, NULL);
+ else if (TailMatchesCS("\\deu*"))
+ COMPLETE_WITH_QUERY(Query_for_list_of_user_mappings);
+ else if (TailMatchesCS("\\dew*"))
+ COMPLETE_WITH_QUERY(Query_for_list_of_fdws);
+ else if (TailMatchesCS("\\dFd*"))
+ COMPLETE_WITH_QUERY(Query_for_list_of_ts_dictionaries);
+ else if (TailMatchesCS("\\dFp*"))
+ COMPLETE_WITH_QUERY(Query_for_list_of_ts_parsers);
+ else if (TailMatchesCS("\\dFt*"))
+ COMPLETE_WITH_QUERY(Query_for_list_of_ts_templates);
+ /* must be at end of \dF alternatives: */
+ else if (TailMatchesCS("\\dF*"))
+ COMPLETE_WITH_QUERY(Query_for_list_of_ts_configurations);
+ else if (TailMatchesCS("\\di*"))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_indexes, NULL);
+ else if (TailMatchesCS("\\dL*"))
+ COMPLETE_WITH_QUERY(Query_for_list_of_languages);
+ else if (TailMatchesCS("\\dn*"))
+ COMPLETE_WITH_QUERY(Query_for_list_of_schemas);
+ else if (TailMatchesCS("\\dp") || TailMatchesCS("\\z"))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_grantables, NULL);
+ else if (TailMatchesCS("\\ds*"))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_sequences, NULL);
+ else if (TailMatchesCS("\\dt*"))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, NULL);
+ else if (TailMatchesCS("\\dT*"))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_datatypes, NULL);
+ else if (TailMatchesCS("\\du*") || TailMatchesCS("\\dg*"))
+ COMPLETE_WITH_QUERY(Query_for_list_of_roles);
+ else if (TailMatchesCS("\\dv*"))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_views, NULL);
+ else if (TailMatchesCS("\\dx*"))
+ COMPLETE_WITH_QUERY(Query_for_list_of_extensions);
+ else if (TailMatchesCS("\\dm*"))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_matviews, NULL);
+ else if (TailMatchesCS("\\dE*"))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_foreign_tables, NULL);
+ else if (TailMatchesCS("\\dy*"))
+ COMPLETE_WITH_QUERY(Query_for_list_of_event_triggers);
+ else if (TailMatchesCS("\\des*"))
+ {
+ if (TailMatchesCS("\\describe*"))
+ {
+ if (TailMatchesCS("\\describe-access-method*"))
+ COMPLETE_WITH_QUERY(Query_for_list_of_access_methods);
+ else if (TailMatchesCS("\\describe-aggregate-function*"))
+ COMPLETE_WITH_VERSIONED_SCHEMA_QUERY(Query_for_list_of_functions, NULL);
+ else if (TailMatchesCS("\\describe-aggregate*"))
+ COMPLETE_WITH_VERSIONED_SCHEMA_QUERY(Query_for_list_of_aggregates, NULL);
+ else if (TailMatchesCS("\\describe-domain*"))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_domains, NULL);
+ else if (TailMatchesCS("\\describe-event-trigger*"))
+ COMPLETE_WITH_QUERY(Query_for_list_of_event_triggers);
+ else if (TailMatchesCS("\\describe-extension*"))
+ COMPLETE_WITH_QUERY(Query_for_list_of_extensions);
+ else if (TailMatchesCS("\\describe-foreign-data-wrapper*"))
+ COMPLETE_WITH_QUERY(Query_for_list_of_fdws);
+ else if (TailMatchesCS("\\describe-foreign-server*"))
+ COMPLETE_WITH_QUERY(Query_for_list_of_servers);
+ else if (TailMatchesCS("\\describe-foreign-table*"))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_foreign_tables, NULL);
+ else if (TailMatchesCS("\\describe-function*"))
+ COMPLETE_WITH_VERSIONED_SCHEMA_QUERY(Query_for_list_of_functions, NULL);
+ else if (TailMatchesCS("\\describe-index*"))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_indexes, NULL);
+ else if (TailMatchesCS("\\describe-materialized-view*"))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_matviews, NULL);
+ else if (TailMatchesCS("\\describe-namespace*"))
+ COMPLETE_WITH_QUERY(Query_for_list_of_schemas);
+ else if (TailMatchesCS("\\describe-normal-function*"))
+ COMPLETE_WITH_VERSIONED_SCHEMA_QUERY(Query_for_list_of_functions, NULL);
+ else if (TailMatchesCS("\\describe-privelege*"))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_grantables, NULL);
+ else if (TailMatchesCS("\\describe-procedural-language*"))
+ COMPLETE_WITH_QUERY(Query_for_list_of_languages);
+ else if (TailMatchesCS("\\describe-procedure*"))
+ COMPLETE_WITH_VERSIONED_SCHEMA_QUERY(Query_for_list_of_functions, NULL);
+ else if (TailMatchesCS("\\describe-role*"))
+ COMPLETE_WITH_QUERY(Query_for_list_of_roles);
+ else if (TailMatchesCS("\\describe-tablespace*"))
+ COMPLETE_WITH_QUERY(Query_for_list_of_tablespaces);
+ else if (TailMatchesCS("\\describe-schema*"))
+ COMPLETE_WITH_QUERY(Query_for_list_of_schemas);
+ else if (TailMatchesCS("\\describe-sequence*"))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_sequences, NULL);
+ else if (TailMatchesCS("\\describe-table*"))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, NULL);
+ else if (TailMatchesCS("\\describe-text-search-dictionary*"))
+ COMPLETE_WITH_QUERY(Query_for_list_of_ts_dictionaries);
+ else if (TailMatchesCS("\\describe-text-search-parser*"))
+ COMPLETE_WITH_QUERY(Query_for_list_of_ts_parsers);
+ else if (TailMatchesCS("\\describe-text-search-template*"))
+ COMPLETE_WITH_QUERY(Query_for_list_of_ts_templates);
+ else if (TailMatchesCS("\\describe-type*"))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_datatypes, NULL);
+ else if (TailMatchesCS("\\describe-user-mapping*"))
+ COMPLETE_WITH_QUERY(Query_for_list_of_user_mappings);
+ else if (TailMatchesCS("\\describe-view*"))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_views, NULL);
+ else if (TailMatchesCS("\\describe-window-function*"))
+ COMPLETE_WITH_VERSIONED_SCHEMA_QUERY(Query_for_list_of_functions, NULL);
+ else
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_relations, NULL);
+ }
+ else /* \\des* must go after \\describe */
+ COMPLETE_WITH_QUERY(Query_for_list_of_servers);
+ }
+ else /* must be at end of \d alternatives: */
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_relations, NULL);
+ }
else if (TailMatchesCS("\\ef"))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_routines, NULL);
--
2.17.1
Hi
Since this is now waiting for v13, there's a bit more time to entertain
the question of whether we'd rather have these in psql or in a new server
command DESCRIBE [verbose] [system], and if so, whether the output of that
would itself be query-able or not.
Including this feature in core can be nice. If they are on server side,
then should to produce result via API - like EXPLAIN. That's all.
Regards
Pavel
On 2018-Jan-29, David Fetter wrote:
We could certainly have \d call DESCRIBE for later versions of the
server. \ commands which call different SQL depending on server
version have long been a standard practice.
So what is the uptake on implementing this at the server side, ie.
DESCRIBE?
--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Fri, Jun 21, 2019 at 05:20:54PM -0400, Alvaro Herrera wrote:
On 2018-Jan-29, David Fetter wrote:
We could certainly have \d call DESCRIBE for later versions of the
server. \ commands which call different SQL depending on server
version have long been a standard practice.So what is the uptake on implementing this at the server side, ie.
DESCRIBE?
I've got a few Round Tuits available this weekend. This seems like a
worthwhile thing to spend them on.
Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
On 2019-Jun-21, David Fetter wrote:
On Fri, Jun 21, 2019 at 05:20:54PM -0400, Alvaro Herrera wrote:
On 2018-Jan-29, David Fetter wrote:
We could certainly have \d call DESCRIBE for later versions of the
server. \ commands which call different SQL depending on server
version have long been a standard practice.So what is the uptake on implementing this at the server side, ie.
DESCRIBE?I've got a few Round Tuits available this weekend. This seems like a
worthwhile thing to spend them on.
That's great, but my question is whether you managed to convince anyone
whether it's a good idea.
--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Alvaro Herrera <alvherre@2ndquadrant.com> writes:
On 2018-Jan-29, David Fetter wrote:
We could certainly have \d call DESCRIBE for later versions of the
server. \ commands which call different SQL depending on server
version have long been a standard practice.
So what is the uptake on implementing this at the server side, ie.
DESCRIBE?
I'm pretty skeptical of this idea, unless you are willing to throw
away at least one and possibly both of the following goals:
1. Compatibility with psql's existing \d behavior.
2. Usability of DESCRIBE for any purpose whatsoever other than emitting
something that looks just like what psql prints.
We've migrated many of the \d displays so far away from "a single query
result" that I don't believe there's a way for a server command to
duplicate them, at least not without some seriously unholy in-bed-ness
between the server command and some postprocessing logic in describe.c.
(At which point you've lost whatever system architectural value there
might be in the whole project, since having a more-arm's-length
relationship there kinda seems like the point to me.)
There are a bunch of other little behavioral differences that you just
can't replicate server-side, like the fact that localization of the
results depends on psql's LC_MESSAGES not the server's. Maybe people
would be okay with changing that, but it's not a transparent
reimplementation.
I think if we want to have server-side describe capability, we're better
off just to implement a DESCRIBE command that's not intended to be exactly
like \d anything, and not try to make it be the implementation for \d
anything. (This was, in fact, where David started IIUC. Other people's
sniping at that idea hasn't yielded any better idea.)
In particular, I'm really strongly against having "\describe-foo-bar"
invoke DESCRIBE, because (a) that will break compatibility with the
existing \des command, and (b) it's not actually saving any typing,
and (c) I think it'd confuse users no end.
Of course, this line of thought does lead to the conclusion that we'd be
maintaining psql/describe.c and server-side DESCRIBE in parallel forever,
which doesn't sound like fun. But we should be making DESCRIBE with an
eye to more use-cases than psql. If it allows jdbc to not also maintain
a pile of equivalent code, that'd be a win. If it allows pg_dump to toss
a bunch of logic overboard (or at least stop incrementally adding new
variants), that'd be a big win.
regards, tom lane
On Fri, Jun 21, 2019 at 05:49:43PM -0400, Alvaro Herrera wrote:
On 2019-Jun-21, David Fetter wrote:
On Fri, Jun 21, 2019 at 05:20:54PM -0400, Alvaro Herrera wrote:
On 2018-Jan-29, David Fetter wrote:
We could certainly have \d call DESCRIBE for later versions of the
server. \ commands which call different SQL depending on server
version have long been a standard practice.So what is the uptake on implementing this at the server side, ie.
DESCRIBE?I've got a few Round Tuits available this weekend. This seems like a
worthwhile thing to spend them on.That's great, but my question is whether you managed to convince anyone
whether it's a good idea.
Everybody who's used MySQL will. In some sense, I'm more concerned
about the users in the future, who I hope vastly outnumber the users
in the present and past.
Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
So what is the uptake on implementing this at the server side, ie.
DESCRIBE?I'm pretty skeptical of this idea, unless you are willing to throw
away at least one and possibly both of the following goals:1. Compatibility with psql's existing \d behavior.
I don't think *compatibility* with the behavior should be a goal in itself.
Coverage of the majority of the use-cases is.
2. Usability of DESCRIBE for any purpose whatsoever other than emitting
something that looks just like what psql prints.
We've migrated many of the \d displays so far away from "a single query
result" that I don't believe there's a way for a server command to
duplicate them, at least not without some seriously unholy in-bed-ness
between the server command and some postprocessing logic in describe.c.
(At which point you've lost whatever system architectural value there
might be in the whole project, since having a more-arm's-length
relationship there kinda seems like the point to me.)
I think there's a genuine use for regular printed output, and there's also
a use for a query-able output. Maybe that queryable output is just a JSONB
output that the outer query can pick apart as it sees fit, and that would
handle the fact that the data often doesn't fit into a single query's
output.
Incidentally, I had need of this very functionality in Snowflake the other
day. The data dictionary there isn't capable of telling you which columns
are in a primary key, but that information is printed when you run
"DESCRIBE my_table". The workaround is to run "DESCRIBE my_table" and then
make another query using a table function to recall the output of the last
query made in the session, and then filter that. Yeah, as a pattern it's
weird and sad, but it shows that there's are uses for something
DESCRIBE-ish on the server side.
So if we're going servier-side on DESCRIBE, it should be it's own entity,
not beholden to design decisions made in psql.
There are a bunch of other little behavioral differences that you just
can't replicate server-side, like the fact that localization of the
results depends on psql's LC_MESSAGES not the server's. Maybe people
would be okay with changing that, but it's not a transparent
reimplementation.
I think people would be OK with that. We're asking the server what it knows
about an object, not how psql feels about that same information.
I think if we want to have server-side describe capability, we're better
off just to implement a DESCRIBE command that's not intended to be exactly
like \d anything, and not try to make it be the implementation for \d
anything. (This was, in fact, where David started IIUC. Other people's
sniping at that idea hasn't yielded any better idea.)
I'm very much in support of server-side DESCRIBE that's not beholden to \d
in any way. For instance, I'm totally fine with DESCRIBE not being able to
handle wildcard patterns.
My initial suggestion for client-side \describe was mostly borne of it
being easy to implement a large subset of the \d commands to help users.
Not all users have psql access, so having a server side command helps more
people.
It could be that we decide that DESCRIBE is set-returning, and we have to
break up \d functionality to suit. By this I mean that we might find it
simpler to require DESCRIBE TABLE foo to only show columns with minimal
information about PKs and follow up commands like "DESCRIBE TABLE foo
INDEXES" or "DESCRIBE TABLE foo CONSTRAINTS" to keep output in tabular
format.
In particular, I'm really strongly against having "\describe-foo-bar"
invoke DESCRIBE, because (a) that will break compatibility with the
existing \des command, and (b) it's not actually saving any typing,
and (c) I think it'd confuse users no end.
+1. Having psql figure out which servers can give proper
servier-side-describes would boggle the mind.
Of course, this line of thought does lead to the conclusion that we'd be
maintaining psql/describe.c and server-side DESCRIBE in parallel forever,
Not fun, but what's our motivation for adding new new \d functionality once
a viable DESCRIBE is in place? Wouldn't the \d commands essentially be
feature-frozen at that point?
which doesn't sound like fun. But we should be making DESCRIBE with an
eye to more use-cases than psql. If it allows jdbc to not also maintain
a pile of equivalent code, that'd be a win. If it allows pg_dump to toss
a bunch of logic overboard (or at least stop incrementally adding new
variants), that'd be a big win.
I don't know enough about JDBC internals to know what sort of non-set
results it can handle, but that seems key to showing us how to proceed.
As for pg_dump, that same goal was a motivation for a similar server-side
command "SHOW CREATE <object>" (essentially, pg_dump of <object>) which
would have basically the same design issues as DESCRIBE would, though the
result set would be a much simpler SETOF text.
On Sun, Jun 23, 2019 at 7:34 AM Corey Huinker <corey.huinker@gmail.com> wrote:
So what is the uptake on implementing this at the server side, ie.
DESCRIBE?I'm pretty skeptical of this idea, unless you are willing to throw
away at least one and possibly both of the following goals:
It seems this topic is ongoing so I've moved it to the September CF,
but it's in "Waiting on Author" because we don't have a concrete patch
that applies (or agreement on what it should do?) right now.
--
Thomas Munro
https://enterprisedb.com
It seems this topic is ongoing so I've moved it to the September CF,
but it's in "Waiting on Author" because we don't have a concrete patch
that applies (or agreement on what it should do?) right now.
All recent work has been investigating the need(s) we're trying to address.
This is as good of a time as any to share my findings (with much
collaboration with Dave Fetter) so far.
1. Adding helper commands to psql aids only psql, and a great number of
users do not, or can not, use psql. So adding something on the server side
would have broader usage and appeal. Furthermore, some access tools
(especially browser-based ones) are not good about returning non-tabular
results, so helper commands that return result sets would have the broadest
usage.
2. Our own interest in server-side commands is all over the map. Some just
want the convenience of having them server side, or familiarity with
$OTHER_DB. Others want to eliminate the need for some code in pg_dump,
JDBC, or elsewhere.
3. There isn't much consensus in the other databases, though all of them do
*something*:
SQLServer
---------------
SQLServer has sp_help (
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-help-transact-sql?view=sql-server-2017
)
which contextually returns one of two different result sets (name, owner,
object type) or (column name, type, storage, length, precision, scale,
nullable, default, rule, collation)
DB2
------
Has a describe command (source:
https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0002019.html)
which
can be used to describe query output (data type, data type length, column
name, column name length).
It also has an option to DESCRIBE TABLE foo which returns a set of
(col_name, schema_of_datatype, data_type, data_type_length,
data_type_scale, Nulls t/f)
It also has DESCRIBE INDEXES FOR TABLE foo which returns a set of (schema
of index, name of index, unique flag, number of columns, index type)
It also has DESCRIBE DATA PARTITIONS FOR TABLE which as you might guess
shows partitions.
All of these options have a SHOW DETAIL modifier which adds more columns.
MySQL
----------
(https://dev.mysql.com/doc/refman/8.0/en/show-columns.html)
MySSQL has SHOW COLUMNS which also returns a set of (name, type similar to
format_type(), null flag, PK or index indicator, default value, notes about
auto-increment/autogreneration/implicit trggers), and can be extended to
show privileges and comments with the EXTENDED and FULL options.
MySQL has a DESCRIBE command, but it is a synonym of EXPLAIN.
MySQL also has a raft of commands like SHOW CREATE USER, SHOW CREATE VIEW,
SHOW CREATE TRIGGER, SHOW CREATE TABLE, etc. (ex:
https://dev.mysql.com/doc/refman/8.0/en/show-create-user.html) These
commands all return a result set of of exactly one column, each row
representing one SQL statement, essentially doing a single-object
schema-only pg_dump.
Oracle
---------
https://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12019.htm
SQL*Plus has a describe command that works on tables and views and
composite types (tabular set of: name, null, type) procedures (tabular set
of: arg name, type, in/out), and packages (a series of sets one per type
and procedure)
SQLcl has the INFO statement, which is roughly analogous to psql's \d in
that it is a mix of tabular and non-tabular information.
Oracle itself has dbms_metadata.get_ddl() which seems analogous to mysql's
SHOW CREATE commands.
Snowflake
--------------
Snowflake has DESCRIBE TABLE
https://docs.snowflake.net/manuals/sql-reference/sql/desc-table.html and
DESCRIBE VIEW
https://docs.snowflake.net/manuals/sql-reference/functions/get_ddl.html
Which return a set of: (name, type, column type, null flag, default,
primary key, unique key, check, expression, comment).
It also has an option for describing "stage" tables, which are s3 buckets
with a file format associated, the closest postgresql analog would be a
file_fdw foreign table, and there is a separate result set format for that.
Snowflake has no concept of indexes (it understands that there's things
called a unique keys, and it remembers that you said you wanted one, but
does nothing to enforce it), so no command for that.
These result sets are not composable in a query, however, they are stored
in the RESULT_SCAN cache, which means that you can run a describe, and then
immediately fetch the results of that command as if it was a table.
Snowflake also has a get_ddl() function
https://docs.snowflake.net/manuals/sql-reference/sql/desc-view.html which
is a one-column result set of statements to re-create the given object.
From all this, I have so far concluded:
1. There is real demand to be able to easily see the basic structure of
tables, views, and indexes in a way that strikes a balance between detail
and clutter.
2. There is some acknowledgement that this data be useful if it was further
filtered through SQL, though only one vendor has attempted to implement
that (Snowflake) and even that was far from seamless.
3. There's a clear need to be able to get the DDL steps needed to re-create
most common objects. This could be to copy-paste the info into another
session to create a similar object elsewhere, or for test cases, or so an
experienced person can see the "real guts" of an object without worrying
about what details have been hidden.
4. The needs in #1 and #3 are in direct opposition to each other, and
cannot easily be handled by the same command. Indeed, no one has tried.
5. The SHOW CREATE commands are neat, but the plethora of options (include
comments? include RI constraints? does current session locale figure into
the answer? dependencies?) probably mean that a function the get_ddl()
examples above, can be jam-packed with default parameters and it's still
going to have a very RETURNS SETOF TEXT output.
6. The convenience-function DESCRIBE / SHOW COLUMNS commands strive to have
a tabular format with following: column name, a user-readable datatype,
null/notnull flag, indicator that the column participates in a PK, in a
unique index, in any other sort of index, without naming the index, and
options for showing default values, and comments
7. People coming from other databases have expectations of a command like
DESCRIBE existing, and those expectations are reasonable.
8. The commands we do make should strive to have a stable result-set format
to allow their use in situations where the results can easily be handled
via JDBC/DBD/DBI, and maybe somewhere down the road the commands
themselves can be used as a subquery the same way that TABLE foo is an
alias for SELECT * FROM foo.
9. Since the relevant columns for describing a table are different than
those for a view or an index or another object, they should be separate
commands.
I'm interested to hear what other people thing, and which of these goals
are most worth pursuing in the near term to make postgres more usable to
newbies and veterans alike.
On 2019-Aug-01, Corey Huinker wrote:
From all this, I have so far concluded:
1. There is real demand to be able to easily see the basic structure of
tables, views, and indexes in a way that strikes a balance between detail
and clutter.
That's great. That said, I'm not opposed to a DESCRIBE server-side
command, and others have shown some interest too. However, the thread
and commitfest entry at hand refer to a new psql command \describe,
which is completely unrelated. So I suggest we should close this CF
entry as Returned with Feedback, and wait until Corey comes back with a
server-side patch for DESCRIBE. I don't see the point of keeping a
\describe item alive if the patch we ultimately end up doing is
something completely different.
--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services