psql.1

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

\fBPORT\fR
The database server port to which you are currently connected.
This is set every time you connect to a database (including
program start-up), but can be unset.
.TP
\fBPROMPT1\fR
.TP
\fBPROMPT2\fR
.TP
\fBPROMPT3\fR
These specify what the prompts \fBpsql\fR
issues should look like. See Prompting [\fBpsql\fR(1)] below.
.TP
\fBQUIET\fR
This variable is equivalent to the command line option
\fB-q\fR. It is probably not too useful in
interactive mode.
.TP
\fBSINGLELINE\fR
This variable is equivalent to the command line option
\fB-S\fR.
.TP
\fBSINGLESTEP\fR
This variable is equivalent to the command line option
\fB-s\fR.
.TP
\fBUSER\fR
The database user you are currently connected as. This is set
every time you connect to a database (including program
start-up), but can be unset.
.TP
\fBVERBOSITY\fR
This variable can be set to the values default,
verbose, or terse to control the verbosity
of error reports.
.SS "SQL INTERPOLATION"
.PP
An additional useful feature of \fBpsql\fR
variables is that you can substitute (``interpolate'')
them into regular SQL statements. The syntax for
this is again to prepend the variable name with a colon
(:).
.sp
.nf
testdb=> \fB\\set foo 'my_table'\fR
testdb=> \fBSELECT * FROM :foo;\fR
.sp
.fi
would then query the table my_table. The value of
the variable is copied literally, so it can even contain unbalanced
quotes or backslash commands. You must make sure that it makes sense
where you put it. Variable interpolation will not be performed into
quoted SQL entities.
.PP
A popular application of this facility is to refer to the last
inserted OID in subsequent statements to build a
foreign key scenario. Another possible use of this mechanism is to
copy the contents of a file into a table column. First load the file into a
variable and then proceed as above.
.sp
.nf
testdb=> \fB\\set content '''' `cat my_file.txt` ''''\fR
testdb=> \fBINSERT INTO my_table VALUES (:content);\fR
.sp
.fi
One problem with this approach is that \fImy_file.txt\fR
might contain single quotes. These need to be escaped so that
they don't cause a syntax error when the second line is processed. This
could be done with the program \fBsed\fR:
.sp
.nf
testdb=> \fB\\set content '''' `sed -e "s/'/''/g" < my_file.txt` ''''\fR
.sp
.fi
If you are using non-standard-conforming strings then you'll also need
to double backslashes. This is a bit tricky:
.sp
.nf
testdb=> \fB\\set content '''' `sed -e "s/'/''/g" -e 's/\\\\/\\\\\\\\/g' < my_file.txt` ''''\fR
.sp
.fi
Note the use of different shell quoting conventions so that neither
the single quote marks nor the backslashes are special to the shell.
Backslashes are still special to \fBsed\fR, however, so
we need to double them. (Perhaps
at one point you thought it was great that all Unix commands use the
same escape character.)
.PP
Since colons may legally appear in SQL commands, the following rule
applies: the character sequence
``:name'' is not changed unless ``name'' is the name
of a variable that is currently set. In any case you can escape
a colon with a backslash to protect it from substitution. (The
colon syntax for variables is standard SQL for
embedded query languages, such as \fBECPG\fR.
The colon syntax for array slices and type casts are
PostgreSQL extensions, hence the
conflict.)
.SS "PROMPTING"
.PP
The prompts \fBpsql\fR issues can be customized
to your preference. The three variables PROMPT1,
PROMPT2, and PROMPT3 contain strings
and special escape sequences that describe the appearance of the
prompt. Prompt 1 is the normal prompt that is issued when
\fBpsql\fR requests a new command. Prompt 2 is
issued when more input is expected during command input because the
command was not terminated with a semicolon or a quote was not closed.
Prompt 3 is issued when you run an SQL
\fBCOPY\fR command and you are expected to type in the
row values on the terminal.
.PP
The value of the selected prompt variable is printed literally,
except where a percent sign (%) is encountered.
Depending on the next character, certain other text is substituted
instead. Defined substitutions are:
.TP
\fB%M\fR
The full host name (with domain name) of the database server,
or [local] if the connection is over a Unix
domain socket, or
[local:\fI/dir/name\fR], 
if the Unix domain socket is not at the compiled in default
location.
.TP
\fB%m\fR
The host name of the database server, truncated at the
first dot, or [local] if the connection is
over a Unix domain socket.
.TP
\fB%>\fR
The port number at which the database server is listening.
.TP
\fB%n\fR
The database session user name. (The expansion of this
value might change during a database session as the result
of the command \fBSET SESSION
AUTHORIZATION\fR.)
.TP
\fB%/\fR
The name of the current database.
.TP
\fB%~\fR
Like %/, but the output is ~
(tilde) if the database is your default database.
.TP
\fB%#\fR
If the session user is a database superuser, then a
#, otherwise a >.
(The expansion of this value might change during a database
session as the result of the command \fBSET SESSION
AUTHORIZATION\fR.)
.TP
\fB%R\fR
In prompt 1 normally =, but ^ if
in single-line mode, and ! if the session is
disconnected from the database (which can happen if
\fB\\connect\fR fails). In prompt 2 the sequence is
replaced by -, *, a single quote,
a double quote, or a dollar sign, depending on whether
\fBpsql\fR expects more input because the
command wasn't terminated yet, because you are inside a
/* ... */ comment, or because you are inside
a quoted or dollar-escaped string. In prompt 3 the sequence doesn't
produce anything.
.TP
\fB%x\fR
Transaction status: an empty string when not in a transaction
block, or * when in a transaction block, or
! when in a failed transaction block, or ?
when the transaction state is indeterminate (for example, because
there is no connection).
.TP
\fB%\fIdigits\fB\fR
The character with the indicated octal code is substituted.
.TP
\fB%:\fIname\fB:\fR
The value of the \fBpsql\fR variable
\fIname\fR. See the
section Variables [\fBpsql\fR(1)] for details.
.TP
\fB%`\fIcommand\fB`\fR
The output of \fIcommand\fR, similar to ordinary
``back-tick'' substitution.
.TP
\fB%[ ... %]\fR
Prompts may contain terminal control characters which, for
example, change the color, background, or style of the prompt
text, or change the title of the terminal window. In order for
the line editing features of \fBReadline\fR to work properly, these
non-printing control characters must be designated as invisible
by surrounding them with %[ and
%]. Multiple pairs of these may occur within
the prompt. For example,
.sp
.nf
testdb=> \\set PROMPT1 '%[%033[1;33;40m%]%n@%/%R%[%033[0m%]%# '
.sp
.fi
results in a boldfaced (1;) yellow-on-black
(33;40) prompt on VT100-compatible, color-capable
terminals.
.PP
To insert a percent sign into your prompt, write
%%. The default prompts are
\&'%/%R%# ' for prompts 1 and 2, and
\&'>> ' for prompt 3.
.sp
.RS
.B "Note:"
This feature was shamelessly plagiarized from
\fBtcsh\fR.
.RE
.sp
.SS "COMMAND-LINE EDITING"
.PP
\fBpsql\fR supports the \fBReadline\fR
library for convenient line editing and retrieval. The command
history is automatically saved when \fBpsql\fR
exits and is reloaded when
\fBpsql\fR starts up. Tab-completion is also
supported, although the completion logic makes no claim to be an
SQL parser. If for some reason you do not like the tab completion, you
can turn it off by putting this in a file named
\fI\&.inputrc\fR in your home directory:
.sp
.nf
$if psql
set disable-completion on
$endif
.sp
.fi
(This is not a \fBpsql\fR but a
\fBReadline\fR feature. Read its documentation
for further details.)
.SH "ENVIRONMENT"
.TP
\fBPAGER\fR
If the query results do not fit on the screen, they are piped
through this command. Typical values are
more or less. The default
is platform-dependent. The use of the pager can be disabled by
using the \fB\\pset\fR command.
.TP
\fBPGDATABASE\fR
Default connection database
.TP
\fBPGHOST\fR
.TP
\fBPGPORT\fR
.TP
\fBPGUSER\fR
Default connection parameters
.TP
\fBPSQL_EDITOR\fR
.TP
\fBEDITOR\fR
.TP
\fBVISUAL\fR
Editor used by the \fB\\e\fR command. The variables
are examined in the order listed; the first that is set is used.
.TP
\fBSHELL\fR
Command executed by the \fB\\!\fR command.
.TP
\fBTMPDIR\fR
Directory for storing temporary files. The default is
\fI/tmp\fR.
.PP
This utility, like most other PostgreSQL utilities,
also uses the environment variables supported by \fBlibpq\fR
(see in the documentation).
.PP
.SH "FILES"
.TP 0.2i
\(bu
Before starting up, \fBpsql\fR attempts to
read and execute commands from the system-wide
\fIpsqlrc\fR file and the user's
\fI~/.psqlrc\fR file.
(On Windows, the user's startup file is named
\fI%APPDATA%\\postgresql\\psqlrc.conf\fR.)
See \fIPREFIX/share/psqlrc.sample\fR
for information on setting up the system-wide file. It could be used
to set up the client or the server to taste (using the \fB\\set
\fRand \fBSET\fR commands).
.TP 0.2i
\(bu
Both the system-wide \fIpsqlrc\fR file and the user's
\fI~/.psqlrc\fR file can be made version-specific
by appending a dash and the PostgreSQL
release number, for example \fI~/.psqlrc-8.2.6\fR.
A matching version-specific file will be read in preference to a
non-version-specific file.
.TP 0.2i
\(bu
The command-line history is stored in the file
\fI~/.psql_history\fR, or
\fI%APPDATA%\\postgresql\\psql_history\fR on Windows.
.SH "NOTES"
.TP 0.2i
\(bu
In an earlier life \fBpsql\fR allowed the
first argument of a single-letter backslash command to start
directly after the command, without intervening whitespace. For
compatibility this is still supported to some extent,
but we are not going to explain the details here as this use is
discouraged. If you get strange messages, keep this in mind.
For example
.sp
.nf
testdb=> \fB\\foo\fR
Field separator is "oo".
.sp
.fi
which is perhaps not what one would expect.
.TP 0.2i
\(bu
\fBpsql\fR only works smoothly with servers
of the same version. That does not mean other combinations will
fail outright, but subtle and not-so-subtle problems might come
up. Backslash commands are particularly likely to fail if the
server is of a different version.
.SH "NOTES FOR WINDOWS USERS"
.PP
\fBpsql\fR is built as a ``console
application''. Since the Windows console windows use a different
encoding than the rest of the system, you must take special care
when using 8-bit characters within \fBpsql\fR.
If \fBpsql\fR detects a problematic
console code page, it will warn you at startup. To change the
console code page, two things are necessary:
.TP 0.2i
\(bu
Set the code page by entering \fBcmd.exe /c chcp
1252\fR. (1252 is a code page that is appropriate for
German; replace it with your value.) If you are using Cygwin,
you can put this command in \fI/etc/profile\fR.
.TP 0.2i
\(bu
Set the console font to Lucida Console, because the
raster font does not work with the ANSI code page.
.PP
.SH "EXAMPLES"
.PP
The first example shows how to spread a command over several lines of
input. Notice the changing prompt:
.sp
.nf
testdb=> \fBCREATE TABLE my_table (\fR
testdb(> \fB first integer not null default 0,\fR
testdb(> \fB second text)\fR
testdb-> \fB;\fR
CREATE TABLE
.sp
.fi
Now look at the table definition again:
.sp
.nf
testdb=> \fB\\d my_table\fR
             Table "my_table"
 Attribute |  Type   |      Modifier
-----------+---------+--------------------
 first     | integer | not null default 0
 second    | text    |
.sp
.fi
Now we change the prompt to something more interesting:
.sp
.nf
testdb=> \fB\\set PROMPT1 '%n@%m %~%R%# '\fR
peter@localhost testdb=>
.sp
.fi
Let's assume you have filled the table with data and want to take a
look at it:
.sp
.nf
peter@localhost testdb=> SELECT * FROM my_table;
 first | second
-------+--------
     1 | one
     2 | two
     3 | three
     4 | four
(4 rows)
.sp
.fi
You can display tables in different ways by using the
\fB\\pset\fR command:
.sp
.nf
peter@localhost testdb=> \fB\\pset border 2\fR
Border style is 2.
peter@localhost testdb=> \fBSELECT * FROM my_table;\fR
+-------+--------+
| first | second |
+-------+--------+
|     1 | one    |
|     2 | two    |
|     3 | three  |
|     4 | four   |
+-------+--------+
(4 rows)

peter@localhost testdb=> \fB\\pset border 0\fR
Border style is 0.
peter@localhost testdb=> \fBSELECT * FROM my_table;\fR
first second
----- ------
    1 one
    2 two
    3 three
    4 four
(4 rows)

peter@localhost testdb=> \fB\\pset border 1\fR
Border style is 1.
peter@localhost testdb=> \fB\\pset format unaligned\fR
Output format is unaligned.
peter@localhost testdb=> \fB\\pset fieldsep ","\fR
Field separator is ",".
peter@localhost testdb=> \fB\\pset tuples_only\fR
Showing only tuples.
peter@localhost testdb=> \fBSELECT second, first FROM my_table;\fR
one,1
two,2
three,3
four,4
.sp
.fi
Alternatively, use the short commands:
.sp
.nf
peter@localhost testdb=> \fB\\a \\t \\x\fR
Output format is aligned.
Tuples only is off.
Expanded display is on.
peter@localhost testdb=> \fBSELECT * FROM my_table;\fR
-[ RECORD 1 ]-
first  | 1
second | one
-[ RECORD 2 ]-
first  | 2
second | two
-[ RECORD 3 ]-
first  | 3
second | three
-[ RECORD 4 ]-
first  | 4
second | four
.sp
.fi