Skip to content

Commit 039cb47

Browse files
tglsfdctglsfdc
committed
psql backslash commands are schema-aware. Pattern matching behavior
follows recent pghackers discussion. This commit includes all the relevant fixes from Greg Mullane's patch of 24-June.
1 parent 6ce4a4e commit 039cb47

File tree

7 files changed

+946
-519
lines changed

7 files changed

+946
-519
lines changed

‎doc/src/sgml/ref/grant.sgml

Lines changed: 9 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/ref/grant.sgml,v 1.26 2002/05/14 18:47:58 tgl Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/ref/grant.sgml,v 1.27 2002/08/10 03:56:23 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -249,16 +249,17 @@ GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
249249
</para>
250250

251251
<para>
252-
Use <xref linkend="app-psql">'s <command>\z</command> command
252+
Use <xref linkend="app-psql">'s <command>\dp</command> command
253253
to obtain information about existing privileges, for example:
254254
<programlisting>
255-
lusitania=> \z mytable
256-
Access privileges for database "lusitania"
257-
Table | Access privileges
258-
---------+---------------------------------------
259-
mytable | {=r,miriam=arwdRxt,"group todos=arw"}
255+
lusitania=> \dp mytable
256+
Access privileges for database "lusitania"
257+
Schema | Table | Access privileges
258+
--------+---------+---------------------------------------
259+
public | mytable | {=r,miriam=arwdRxt,"group todos=arw"}
260+
(1 row)
260261
</programlisting>
261-
The entries shown by <command>\z</command> are interpreted thus:
262+
The entries shown by <command>\dp</command> are interpreted thus:
262263
<programlisting>
263264
=xxxx -- privileges granted to PUBLIC
264265
uname=xxxx -- privileges granted to a user

‎doc/src/sgml/ref/psql-ref.sgml

Lines changed: 109 additions & 94 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/ref/psql-ref.sgml,v 1.69 2002/07/28 15:22:21 petere Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/ref/psql-ref.sgml,v 1.70 2002/08/10 03:56:23 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -538,7 +538,7 @@ testdb=>
538538
</para>
539539

540540
<para>
541-
To include whitespace into an argument you must quote it with a
541+
To include whitespace into an argument you may quote it with a
542542
single quote. To include a single quote into such an argument,
543543
precede it by a backslash. Anything contained in single quotes is
544544
furthermore subject to C-like substitutions for
@@ -551,25 +551,24 @@ testdb=>
551551

552552
<para>
553553
If an unquoted argument begins with a colon (<literal>:</literal>),
554-
it is taken as a variable and the value of the variable is taken as
555-
the argument instead.
554+
it is taken as a <application>psql</> variable and the value of the
555+
variable is used as the argument instead.
556556
</para>
557557

558558
<para>
559559
Arguments that are quoted in <quote>backticks</quote>
560560
(<literal>`</literal>) are taken as a command line that is passed to
561-
the shell. The output of the command (with a trailing newline
561+
the shell. The output of the command (with any trailing newline
562562
removed) is taken as the argument value. The above escape sequences
563563
also apply in backticks.
564564
</para>
565565

566566
<para>
567-
Some commands take the name of an <acronym>SQL</acronym> identifier
567+
Some commands take an <acronym>SQL</acronym> identifier
568568
(such as a table name) as argument. These arguments follow the
569569
syntax rules of <acronym>SQL</acronym> regarding double quotes: an
570-
identifier without double quotes is coerced to lower-case. For all
571-
other commands double quotes are not special and will become part of
572-
the argument.
570+
identifier without double quotes is coerced to lower-case, while
571+
whitespace within double quotes is included in the argument.
573572
</para>
574573

575574
<para>
@@ -732,18 +731,17 @@ testdb=>
732731
</varlistentry>
733732

734733
<varlistentry>
735-
<term><literal>\d</literal> <replaceable class="parameter">relation</replaceable> </term>
734+
<term><literal>\d</literal> [ <replaceable class="parameter">pattern</replaceable> ]</term>
736735

737736
<listitem>
738737
<para>
739-
Shows all columns of <replaceable
740-
class="parameter">relation</replaceable> (which could be a
741-
table, view, index, or sequence), their types, and any special
738+
For each relation (table, view, index, or sequence) matching the
739+
<replaceable class="parameter">pattern</replaceable>, show all
740+
columns, their types, and any special
742741
attributes such as <literal>NOT NULL</literal> or defaults, if
743-
any. If the relation is, in fact, a table, any defined indices,
744-
primary keys, unique constraints and check constraints are also
745-
listed. If the relation is a view, the view definition is also
746-
shown.
742+
any. Associated indexes, constraints, rules, and triggers are
743+
also shown, as is the view definition if the relation is a view.
744+
(<quote>Matching the pattern</> is defined below.)
747745
</para>
748746

749747
<para>
@@ -753,7 +751,8 @@ testdb=>
753751

754752
<note>
755753
<para>
756-
If <command>\d</command> is called without any arguments, it is
754+
If <command>\d</command> is used without a
755+
<replaceable class="parameter">pattern</replaceable> argument, it is
757756
equivalent to <command>\dtvs</command> which will show a list of
758757
all tables, views, and sequences. This is purely a convenience
759758
measure.
@@ -776,34 +775,35 @@ testdb=>
776775
</varlistentry>
777776

778777
<varlistentry>
779-
<term><literal>\dd</literal> [ <replaceable class="parameter">object</replaceable> ]</term>
778+
<term><literal>\dd</literal> [ <replaceable class="parameter">pattern</replaceable> ]</term>
780779
<listitem>
781780
<para>
782-
Shows the descriptions of <replaceable
783-
class="parameter">object</replaceable> (which can be a regular
784-
expression), or of all objects if no argument is given.
781+
Shows the descriptions of objects matching the <replaceable
782+
class="parameter">pattern</replaceable>, or of all visible objects if
783+
no argument is given. But in either case, only objects that have
784+
a description are listed.
785785
(<quote>Object</quote> covers aggregates, functions, operators,
786786
types, relations (tables, views, indexes, sequences, large
787787
objects), rules, and triggers.) For example:
788788
<programlisting>
789789
=> <userinput>\dd version</userinput>
790-
Object descriptions
791-
Name | What | Description
792-
---------+----------+---------------------------
793-
version | function | PostgreSQL version string
790+
Object descriptions
791+
Schema | Name | Object | Description
792+
------------+---------+----------+---------------------------
793+
pg_catalog | version | function | PostgreSQL version string
794794
(1 row)
795795
</programlisting>
796796
</para>
797797

798798
<para>
799-
Descriptions for objects can be generated with the
799+
Descriptions for objects can be created with the
800800
<command>COMMENT ON</command> <acronym>SQL</acronym> command.
801801
</para>
802802

803803
<note>
804804
<para>
805805
<productname>PostgreSQL</productname> stores the object
806-
descriptions in the pg_description system table.
806+
descriptions in the <structname>pg_description</> system table.
807807
</para>
808808
</note>
809809

@@ -816,7 +816,7 @@ testdb=>
816816
<listitem>
817817
<para>
818818
Lists all available domains (derived types). If <replaceable
819-
class="parameter">pattern</replaceable> (a regular expression)
819+
class="parameter">pattern</replaceable>
820820
is specified, only matching domains are shown.
821821
</para>
822822
</listitem>
@@ -830,7 +830,7 @@ testdb=>
830830
<para>
831831
Lists available functions, together with their argument and
832832
return types. If <replaceable
833-
class="parameter">pattern</replaceable> (a regular expression)
833+
class="parameter">pattern</replaceable>
834834
is specified, only matching functions are shown. If the form
835835
<literal>\df+</literal> is used, additional information about
836836
each function, including language and description, is shown.
@@ -844,18 +844,17 @@ testdb=>
844844

845845
<listitem>
846846
<para>
847-
This is not the actual command name: The letters i, s, t, v, S
847+
This is not the actual command name: the letters i, s, t, v, S
848848
stand for index, sequence, table, view, and system table,
849-
respectively. You can specify any or all of them in any order to
850-
obtain a listing of them, together with who the owner is.
849+
respectively. You can specify any or all of these letters, in any
850+
order, to obtain a listing of all the matching objects.
851+
If <quote>+</quote> is appended to the command name, each object is
852+
listed with its associated description, if any.
851853
</para>
852854

853855
<para>
854-
If <replaceable class="parameter">pattern</replaceable> is
855-
specified, it is a regular expression that restricts the listing
856-
to those objects whose name matches. If one appends a
857-
<quote>+</quote> to the command name, each object is listed with
858-
its associated description, if any.
856+
If a <replaceable class="parameter">pattern</replaceable> is
857+
specified, only objects whose name matches the pattern are listed.
859858
</para>
860859
</listitem>
861860
</varlistentry>
@@ -873,12 +872,12 @@ testdb=>
873872

874873

875874
<varlistentry>
876-
<term><literal>\do [ <replaceable class="parameter">name</replaceable> ]</literal></term>
875+
<term><literal>\do [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
877876
<listitem>
878877
<para>
879878
Lists available operators with their operand and return types.
880-
If <replaceable class="parameter">name</replaceable> is
881-
specified, only operators with that name will be shown.
879+
If a <replaceable class="parameter">pattern</replaceable> is
880+
specified, only operators whose name matches the pattern are listed.
882881
</para>
883882
</listitem>
884883
</varlistentry>
@@ -888,9 +887,17 @@ testdb=>
888887
<term><literal>\dp</literal> [ <replaceable class="parameter">pattern</replaceable> ]</term>
889888
<listitem>
890889
<para>
891-
This is an alias for <command>\z</command> which was included
892-
for its greater mnemonic value (<quote>display
893-
permissions</quote>).
890+
Produces a list of all available tables with their
891+
associated access permissions.
892+
If a <replaceable class="parameter">pattern</replaceable> is
893+
specified, only tables whose name matches the pattern are listed.
894+
</para>
895+
896+
<para>
897+
The commands <xref linkend="SQL-GRANT"> and
898+
<xref linkend="SQL-REVOKE">
899+
are used to set access permissions. See <xref linkend="SQL-GRANT">
900+
for more information.
894901
</para>
895902
</listitem>
896903
</varlistentry>
@@ -912,7 +919,7 @@ testdb=>
912919
<term><literal>\du [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
913920
<listitem>
914921
<para>
915-
Lists all configured users or only those that match <replaceable
922+
Lists all database users, or only those that match <replaceable
916923
class="parameter">pattern</replaceable>.
917924
</para>
918925
</listitem>
@@ -1608,57 +1615,23 @@ lo_import 152801
16081615
<term><literal>\z</literal> [ <replaceable class="parameter">pattern</replaceable> ]</term>
16091616
<listitem>
16101617
<para>
1611-
Produces a list of all tables in the database with their
1612-
appropriate access permissions listed. If an argument is given
1613-
it is taken as a regular expression which limits the listing to
1614-
those tables which match it.
1615-
</para>
1616-
1617-
<para>
1618-
<programlisting>
1619-
test=&gt; <userinput>\z</userinput>
1620-
Access permissions for database "test"
1621-
Relation | Access permissions
1622-
----------+-------------------------------------
1623-
my_table | {"=r","joe=arwR", "group staff=ar"}
1624-
(1 row )
1625-
</programlisting>
1626-
Read this as follows:
1627-
1628-
<itemizedlist>
1629-
<listitem>
1630-
<para>
1631-
<literal>"=r"</literal>: <literal>PUBLIC</literal> has read
1632-
(<command>SELECT</command>) permission on the table.
1633-
</para>
1634-
</listitem>
1635-
1636-
<listitem>
1637-
<para>
1638-
<literal>"joe=arwR"</literal>: User <literal>joe</literal> has
1639-
read, write (<command>UPDATE</command>,
1640-
<command>DELETE</command>), <quote>append</quote>
1641-
(<command>INSERT</command>) permissions, and permission to
1642-
create rules on the table.
1643-
</para>
1644-
</listitem>
1645-
1646-
<listitem>
1647-
<para>
1648-
<literal>"group staff=ar"</literal>: Group
1649-
<literal>staff</literal> has <command>SELECT</command> and
1650-
<command>INSERT</command> permission.
1651-
</para>
1652-
</listitem>
1653-
</itemizedlist>
1618+
Produces a list of all available tables with their
1619+
associated access permissions.
1620+
If a <replaceable class="parameter">pattern</replaceable> is
1621+
specified, only tables whose name matches the pattern are listed.
16541622
</para>
16551623

16561624
<para>
16571625
The commands <xref linkend="SQL-GRANT"> and
16581626
<xref linkend="SQL-REVOKE">
1659-
are used to set access permissions.
1627+
are used to set access permissions. See <xref linkend="SQL-GRANT">
1628+
for more information.
16601629
</para>
16611630

1631+
<para>
1632+
This is an alias for <command>\dp</command> (<quote>display
1633+
permissions</quote>).
1634+
</para>
16621635
</listitem>
16631636
</varlistentry>
16641637

@@ -1688,6 +1661,46 @@ Access permissions for database "test"
16881661

16891662
</variablelist>
16901663
</para>
1664+
1665+
<para>
1666+
The various <literal>\d</> commands accept a <replaceable
1667+
class="parameter">pattern</replaceable> parameter to specify the
1668+
object name(s) to be displayed. Patterns are interpreted similarly
1669+
to SQL identifiers, in that unquoted letters are forced to lowercase,
1670+
while double quotes (<literal>"</>) protect letters from case conversion
1671+
and allow incorporation of whitespace into the identifier. Within
1672+
double quotes, paired double quotes reduce to a single double quote in
1673+
the resulting name. For example, <literal>FOO"BAR"BAZ</> is interpreted
1674+
as <literal>fooBARbaz</>, and <literal>"A weird"" name"</> becomes
1675+
<literal>A weird" name</>.
1676+
</para>
1677+
1678+
<para>
1679+
More interestingly, <literal>\d</> patterns allow the use of
1680+
<literal>*</> to mean <quote>any sequence of characters</>, and
1681+
<literal>?</> to mean <quote>any single character</>. (This notation
1682+
is comparable to Unix shell filename patterns.) Advanced users can
1683+
also use regular-expression notations such as character classes, for
1684+
example <literal>[0-9]</> to match <quote>any digit</>. To make any of
1685+
these pattern-matching characters be interpreted literally, surround it
1686+
with double quotes.
1687+
</para>
1688+
1689+
<para>
1690+
A pattern that contains an (unquoted) dot is interpreted as a schema
1691+
name pattern followed by an object name pattern. For example,
1692+
<literal> \dt foo*.bar*</> displays all tables in schemas whose name
1693+
starts with <literal>foo</> and whose table name
1694+
starts with <literal>bar</>. If no dot appears, then the pattern
1695+
matches only objects that are visible in the current schema search path.
1696+
</para>
1697+
1698+
<para>
1699+
Whenever the <replaceable class="parameter">pattern</replaceable> parameter
1700+
is omitted completely, the <literal>\d</> commands display all objects
1701+
that are visible in the current schema search path. To see all objects
1702+
in the database, use the pattern <literal>*.*</>.
1703+
</para>
16911704
</refsect2>
16921705

16931706
<refsect2>
@@ -2402,11 +2415,12 @@ $ ./configure --with-includes=/opt/gnu/include --with-libs=/opt/gnu/lib ...
24022415
<itemizedlist>
24032416
<listitem>
24042417
<para>
2405-
In some earlier life <application>psql</application> allowed the
2406-
first argument to start directly after the (single-letter)
2407-
command. For compatibility this is still supported to some extent
2418+
In an earlier life <application>psql</application> allowed the
2419+
first argument of a single-letter backslash command to start
2420+
directly after the command, without intervening whitespace. For
2421+
compatibility this is still supported to some extent,
24082422
but I am not going to explain the details here as this use is
2409-
discouraged. But if you get strange messages, keep this in mind.
2423+
discouraged. If you get strange messages, keep this in mind.
24102424
For example
24112425
<programlisting>
24122426
testdb=> <userinput>\foo</userinput>
@@ -2421,7 +2435,8 @@ Field separator is "oo",
24212435
<application>psql</application> only works smoothly with servers
24222436
of the same version. That does not mean other combinations will
24232437
fail outright, but subtle and not-so-subtle problems might come
2424-
up.
2438+
up. Backslash commands are particularly likely to fail if the
2439+
server is of a different version.
24252440
</para>
24262441
</listitem>
24272442

0 commit comments

Comments
 (0)