psql.1

来自「PostgreSQL 8.2中增加了很多企业用户所需要的功能和性能上的提高,其开」· 1 代码 · 共 1,822 行 · 第 1/4 页

\&'\\t'. The default field separator is
\&'|' (a vertical bar).
.TP
\fBfooter\fR
Toggles the display of the default footer (x
rows).
.TP
\fBnumericlocale\fR
Toggles the display of a locale-aware character to separate groups
of digits to the left of the decimal marker. It also enables
a locale-aware decimal marker.
.TP
\fBrecordsep\fR
Specifies the record (line) separator to use in unaligned
output mode. The default is a newline character.
.TP
\fBtuples_only (or t)\fR
Toggles between tuples only and full display. Full display may
show extra information such as column headers, titles, and
various footers. In tuples only mode, only actual table data
is shown.
.TP
\fBtitle [ \fItext\fB ]\fR
Sets the table title for any subsequently printed tables. This
can be used to give your output descriptive tags. If no
argument is given, the title is unset.
.TP
\fBtableattr (or T) [ \fItext\fB ]\fR
Allows you to specify any attributes to be placed inside the
HTML table tag. This
could for example be cellpadding or
bgcolor. Note that you probably don't want
to specify border here, as that is already
taken care of by \\pset border.
.TP
\fBpager\fR
Controls use of a pager for query and \fBpsql\fR
help output. If the environment variable \fBPAGER\fR
is set, the output is piped to the specified program.
Otherwise a platform-dependent default (such as
\fImore\fR) is used.

When the pager is off, the pager is not used. When the pager
is on, the pager is used only when appropriate, i.e. the
output is to a terminal and will not fit on the screen.
(\fBpsql\fR does not do a perfect job of estimating
when to use the pager.) \\pset pager turns the
pager on and off. Pager can also be set to always,
which causes the pager to be always used.
.RE
.PP

Illustrations on how these different formats look can be seen in
the Examples [\fBpsql\fR(1)] section.
.sp
.RS
.B "Tip:"
There are various shortcut commands for \fB\\pset\fR. See
\fB\\a\fR, \fB\\C\fR, \fB\\H\fR,
\fB\\t\fR, \fB\\T\fR, and \fB\\x\fR.
.RE
.sp
.sp
.RS
.B "Note:"
It is an error to call \fB\\pset\fR without
arguments. In the future this call might show the current status
of all printing options.
.RE
.sp
.TP
\fB\\q\fR
Quits the \fBpsql\fR program.
.TP
\fB\\qecho \fItext\fB [ ... ]\fR
This command is identical to \fB\\echo\fR except
that the output will be written to the query output channel, as
set by \fB\\o\fR.
.TP
\fB\\r\fR
Resets (clears) the query buffer.
.TP
\fB\\s [ \fIfilename\fB ]\fR
Print or save the command line history to \fIfilename\fR. If \fIfilename\fR is omitted, the history
is written to the standard output. This option is only available
if \fBpsql\fR is configured to use the
GNU \fBReadline\fR library.
.TP
\fB\\set [ \fIname\fB [ \fIvalue\fB [ ... ] ] ]\fR
Sets the internal variable \fIname\fR to \fIvalue\fR or, if more than one value
is given, to the concatenation of all of them. If no second
argument is given, the variable is just set with no value. To
unset a variable, use the \fB\\unset\fR command.

Valid variable names can contain characters, digits, and
underscores. See the section Variables [\fBpsql\fR(1)] below for details.
Variable names are case-sensitive.

Although you are welcome to set any variable to anything you
want, \fBpsql\fR treats several variables
as special. They are documented in the section about variables.
.sp
.RS
.B "Note:"
This command is totally separate from the SQL
command SET [\fBset\fR(7)].
.RE
.sp
.TP
\fB\\t\fR
Toggles the display of output column name headings and row count
footer. This command is equivalent to \\pset
tuples_only and is provided for convenience.
.TP
\fB\\T \fItable_options\fB\fR
Allows you to specify attributes to be placed within the
table tag in HTML tabular
output mode. This command is equivalent to \\pset
tableattr \fItable_options\fR.
.TP
\fB\\timing\fR
Toggles a display of how long each SQL statement takes, in milliseconds.
.TP
\fB\\w {\fIfilename\fB | \fI|command\fB}\fR
Outputs the current query buffer to the file \fIfilename\fR or pipes it to the Unix
command \fIcommand\fR.
.TP
\fB\\x\fR
Toggles expanded table formatting mode. As such it is equivalent to
\\pset expanded.
.TP
\fB\\z [ \fIpattern\fB ]\fR
Produces a list of all available tables, views and sequences with their
associated access privileges.
If a \fIpattern\fR is
specified, only tables,views and sequences whose names match the pattern are listed.

The GRANT [\fBgrant\fR(7)] and
REVOKE [\fBrevoke\fR(7)]
commands are used to set access privileges.

This is an alias for \fB\\dp\fR (``display
privileges'').
.TP
\fB\\! [ \fIcommand\fB ]\fR
Escapes to a separate Unix shell or executes the Unix command
\fIcommand\fR. The
arguments are not further interpreted, the shell will see them
as is.
.TP
\fB\\?\fR
Shows help information about the backslash commands.
.PP
.SS "PATTERNS"

.PP
The various \\d commands accept a \fIpattern\fR parameter to specify the
object name(s) to be displayed. In the simplest case, a pattern
is just the exact name of the object. The characters within a
pattern are normally folded to lower case, just as in SQL names;
for example, \\dt FOO will display the table named
foo. As in SQL names, placing double quotes around
a pattern stops folding to lower case. Should you need to include
an actual double quote character in a pattern, write it as a pair
of double quotes within a double-quote sequence; again this is in
accord with the rules for SQL quoted identifiers. For example,
\\dt "FOO""BAR" will display the table named
FOO"BAR (not foo"bar). Unlike the normal
rules for SQL names, you can put double quotes around just part
of a pattern, for instance \\dt FOO"FOO"BAR will display
the table named fooFOObar.
.PP
Within a pattern, * matches any sequence of characters
(including no characters) and ? matches any single character.
(This notation is comparable to Unix shell file name patterns.)
For example, \\dt int* displays all tables whose names
begin with int. But within double quotes, *
and ? lose these special meanings and are just matched
literally.
.PP
A pattern that contains a dot (.) is interpreted as a schema
name pattern followed by an object name pattern. For example,
\\dt foo*.bar* displays all tables whose table name
starts with bar that are in schemas whose schema name
starts with foo. When no dot appears, then the pattern
matches only objects that are visible in the current schema search path.
Again, a dot within double quotes loses its special meaning and is matched
literally.
.PP
Advanced users can use regular-expression notations such as character
classes, for example [0-9] to match any digit. All regular
expression special characters work as specified in
in the documentation, except for . which
is taken as a separator as mentioned above, * which is
translated to the regular-expression notation .*, and
? which is translated to .. You can emulate
these pattern characters at need by writing
? for .,
(\fIR\fR+|) for
\fIR\fR*, or
(\fIR\fR|) for
\fIR\fR?.
Remember that the pattern must match the whole name, unlike the usual
interpretation of regular expressions; write * at the beginning
and/or end if you don't wish the pattern to be anchored.
Note that within double quotes, all regular expression special characters
lose their special meanings and are matched literally. Also, the regular
expression special characters are matched literally in operator name
patterns (i.e., the argument of \\do).
.PP
Whenever the \fIpattern\fR parameter
is omitted completely, the \\d commands display all objects
that are visible in the current schema search path \(em this is
equivalent to using the pattern *.
To see all objects in the database, use the pattern *.*.
.SS "ADVANCED FEATURES"
.SS "VARIABLES"
.PP
\fBpsql\fR provides variable substitution
features similar to common Unix command shells.
Variables are simply name/value pairs, where the value
can be any string of any length. To set variables, use the
\fBpsql\fR meta-command
\fB\\set\fR:
.sp
.nf
testdb=> \fB\\set foo bar\fR
.sp
.fi
sets the variable foo to the value
bar. To retrieve the content of the variable, precede
the name with a colon and use it as the argument of any slash
command:
.sp
.nf
testdb=> \fB\\echo :foo\fR
bar
.sp
.fi
.sp
.RS
.B "Note:"
The arguments of \fB\\set\fR are subject to the same
substitution rules as with other commands. Thus you can construct
interesting references such as \\set :foo
\&'something' and get ``soft links'' or
``variable variables'' of Perl
or PHP fame,
respectively. Unfortunately (or fortunately?), there is no way to do
anything useful with these constructs. On the other hand,
\\set bar :foo is a perfectly valid way to copy a
variable.
.RE
.sp
.PP
If you call \fB\\set\fR without a second argument, the
variable is set, with an empty string as value. To unset (or delete) a
variable, use the command \fB\\unset\fR.
.PP
\fBpsql\fR's internal variable names can
consist of letters, numbers, and underscores in any order and any
number of them. A number of these variables are treated specially
by \fBpsql\fR. They indicate certain option
settings that can be changed at run time by altering the value of
the variable or represent some state of the application. Although
you can use these variables for any other purpose, this is not
recommended, as the program behavior might grow really strange
really quickly. By convention, all specially treated variables
consist of all upper-case letters (and possibly numbers and
underscores). To ensure maximum compatibility in the future, avoid
using such variable names for your own purposes. A list of all specially
treated variables follows.

.TP
\fBAUTOCOMMIT\fR
When on (the default), each SQL command is automatically
committed upon successful completion. To postpone commit in this
mode, you must enter a \fBBEGIN\fR or \fBSTART
TRANSACTION\fR SQL command. When off or unset, SQL
commands are not committed until you explicitly issue
\fBCOMMIT\fR or \fBEND\fR. The autocommit-off
mode works by issuing an implicit \fBBEGIN\fR for you, just
before any command that is not already in a transaction block and
is not itself a \fBBEGIN\fR or other transaction-control
command, nor a command that cannot be executed inside a transaction
block (such as \fBVACUUM\fR).
.sp
.RS
.B "Note:"
In autocommit-off mode, you must explicitly abandon any failed
transaction by entering \fBABORT\fR or \fBROLLBACK\fR.
Also keep in mind that if you exit the session
without committing, your work will be lost.
.RE
.sp
.sp
.RS
.B "Note:"
The autocommit-on mode is PostgreSQL's traditional
behavior, but autocommit-off is closer to the SQL spec. If you
prefer autocommit-off, you may wish to set it in the system-wide
\fIpsqlrc\fR file or your
\fI~/.psqlrc\fR file.
.RE
.sp
.TP
\fBDBNAME\fR
The name of the database you are currently connected to. This is
set every time you connect to a database (including program
start-up), but can be unset.
.TP
\fBECHO\fR
If set to all, all lines
entered from the keyboard or from a script are written to the standard output
before they are parsed or executed. To select this behavior on program
start-up, use the switch \fB-a\fR. If set to
queries,
\fBpsql\fR merely prints all queries as
they are sent to the server. The switch for this is
\fB-e\fR.
.TP
\fBECHO_HIDDEN\fR
When this variable is set and a backslash command queries the
database, the query is first shown. This way you can study the
PostgreSQL internals and provide
similar functionality in your own programs. (To select this behavior
on program start-up, use the switch \fB-E\fR.) If you set
the variable to the value noexec, the queries are
just shown but are not actually sent to the server and executed.
.TP
\fBENCODING\fR
The current client character set encoding.
.TP
\fBFETCH_COUNT\fR
If this variable is set to an integer value > 0,
the results of \fBSELECT\fR queries are fetched
and displayed in groups of that many rows, rather than the
default behavior of collecting the entire result set before
display. Therefore only a
limited amount of memory is used, regardless of the size of
the result set. Settings of 100 to 1000 are commonly used
when enabling this feature.
Keep in mind that when using this feature, a query may
fail after having already displayed some rows.
.sp
.RS
.B "Tip:"
Although you can use any output format with this feature,
the default aligned format tends to look bad
because each group of FETCH_COUNT rows
will be formatted separately, leading to varying column
widths across the row groups. The other output formats work better.
.RE
.sp
.TP
\fBHISTCONTROL\fR
If this variable is set to ignorespace,
lines which begin with a space are not entered into the history
list. If set to a value of ignoredups, lines
matching the previous history line are not entered. A value of
ignoreboth combines the two options. If
unset, or if set to any other value than those above, all lines
read in interactive mode are saved on the history list.
.sp
.RS
.B "Note:"
This feature was shamelessly plagiarized from
\fBBash\fR.
.RE
.sp
.TP
\fBHISTFILE\fR
The file name that will be used to store the history list. The default
value is \fI~/.psql_history\fR. For example, putting
.sp
.nf
\\set HISTFILE ~/.psql_history- :DBNAME
.sp
.fi
in \fI~/.psqlrc\fR will cause
\fBpsql\fR to maintain a separate history for
each database.
.sp
.RS
.B "Note:"
This feature was shamelessly plagiarized from
\fBBash\fR.
.RE
.sp
.TP
\fBHISTSIZE\fR
The number of commands to store in the command history. The
default value is 500.
.sp
.RS
.B "Note:"
This feature was shamelessly plagiarized from
\fBBash\fR.
.RE
.sp
.TP
\fBHOST\fR
The database server host you are currently connected to. This is
set every time you connect to a database (including program
start-up), but can be unset.
.TP
\fBIGNOREEOF\fR
If unset, sending an EOF character (usually
\fBControl\fR+\fBD\fR)
to an interactive session of \fBpsql\fR
will terminate the application. If set to a numeric value,
that many EOF characters are ignored before the
application terminates. If the variable is set but has no
numeric value, the default is 10.
.sp
.RS
.B "Note:"
This feature was shamelessly plagiarized from
\fBBash\fR.
.RE
.sp
.TP
\fBLASTOID\fR
The value of the last affected OID, as returned from an
\fBINSERT\fR or \fBlo_insert\fR
command. This variable is only guaranteed to be valid until
after the result of the next SQL command has
been displayed.

.TP
\fBON_ERROR_ROLLBACK\fR
When on, if a statement in a transaction block
generates an error, the error is ignored and the transaction
continues. When interactive, such errors are only
ignored in interactive sessions, and not when reading script
files. When off (the default), a statement in a
transaction block that generates an error aborts the entire
transaction. The on_error_rollback-on mode works by issuing an
implicit \fBSAVEPOINT\fR for you, just before each command
that is in a transaction block, and rolls back to the savepoint
on error.
.TP
\fBON_ERROR_STOP\fR
By default, if non-interactive scripts encounter an error, such
as a malformed SQL command or internal
meta-command, processing continues. This has been the
traditional behavior of \fBpsql\fR but it
is sometimes not desirable. If this variable is set, script
processing will immediately terminate. If the script was called
from another script it will terminate in the same fashion. If
the outermost script was not called from an interactive
\fBpsql\fR session but rather using the
\fB-f\fR option, \fBpsql\fR will
return error code 3, to distinguish this case from fatal error
conditions (error code 1).
.TP