From 78bc83fedf7f865534c61dd14067fc80d580ed29 Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Fri, 26 Nov 1999 04:24:17 +0000 Subject: * Includes tab completion. It's not magic, but it's very cool. At any rate it's better than what used to be there. MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit * Does proper SQL "host variable" substitution as pointed out by Andreas Zeugwetter (thanks): select * from :foo; Also some changes in how ':' and ';' are treated (escape with \ to send to backend). This does _not_ affect the '::' cast operator, but perhaps others that contain : or ; (but there are none right now). * To show description with a listing, append '?' to command name, e.g., \df?. This seemed to be the convenient and logical solution. Or append a '+' to see more useless information, e.g., \df+. * Fixed fflush()'ing bug pointed out by Jan during the regression test discussion. * Added LastOid variable. This ought to take care of TODO item "Add a function to return the last inserted oid, for use in psql scripts" (under CLIENTS) E.g., insert into foo values(...); insert into bar values(..., :LastOid); \echo $LastOid * \d command shows constraints, rules, and triggers defined on the table (in addition to indices) * Various fixes, optimizations, corrections * Documentation update as well Note: This now requires snprintf(), which, if necessary, is taken from src/backend/port. This is certainly a little weird, but it should suffice until a source tree cleanup is done. Enjoy. -- Peter Eisentraut Sernanders väg 10:115 --- doc/src/sgml/ref/psql-ref.sgml | 332 ++++++++++++++++++++--------------------- 1 file changed, 163 insertions(+), 169 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml index 1dbc930eba9..b24acf0e4f7 100644 --- a/doc/src/sgml/ref/psql-ref.sgml +++ b/doc/src/sgml/ref/psql-ref.sgml @@ -1,5 +1,5 @@ @@ -90,6 +90,11 @@ Postgres documentation to be run at the start of every session. + + psql can be used in a pipe sequence, and + automatically detects when it is not used interactively. + + 1998-09-26 @@ -104,7 +109,7 @@ Postgres documentation libpq client library, upon which psql is built, will choose defaults. (This will usually mean the environment variables PGDATABASE, - PGHOST, PGPORT, PQUSER, + PGHOST, PGPORT, PGUSER, respectively, if they are set. Otherwise the default host is the local host via Unix domain sockets, the default port is decided at compile time, the default user is the system user name, and the default database is @@ -129,7 +134,7 @@ Postgres documentation In normal operation, psql provides a prompt with the name of the database that psql is currently connected to followed by the string "=>". For example, - + $ psql testdb Welcome to psql, the PostgreSQL interactive terminal. @@ -140,7 +145,7 @@ Type: \copyright for distribution terms \q to quit testdb=> - + @@ -156,11 +161,6 @@ testdb=> and . - - - psql can be used in a pipe sequence, and - automatically detects when it is not used interactively. - @@ -343,17 +343,17 @@ testdb=> - Shows all column of relation + Shows all columns of relation (which could be a table, view, index, or sequence), their types, and any special attributes such as NOT NULL or defaults, if any. + If the relation is, in fact, a table, any defined indices are also listed. + If the relation is a view, the view definition is also shown. - If the relation is, in fact, a table, any defined indices are also listed. - If the relation is a view, the view definition is also shown. - If the variable description is set, any comments associated - with a table columns are shown as well. + The command form \d? is identical, but any comments + associated with the table columns are shown as well. @@ -375,8 +375,10 @@ testdb=> Lists all available aggregate functions, together with the data type they operate on. If pattern (a regular expression) is specified, only matching aggregates are shown. - If the variable description is set, comments are listed for - each function as well. + If the alternative command form \da? is used, + comments are listed for each function as well. The command form + \da+ will show more information about each aggregate + function, which is usually not of general interest. @@ -389,14 +391,14 @@ testdb=> (which can be a regular expression), or of all objects if no argument is given. (Object covers aggregates, functions, operators, types, relations (tables, views, indices, sequences, large objects), rules, and triggers.) For example: - + => \dd version Object descriptions Name | What | Description ---------+----------+--------------------------- version | function | PostgreSQL version string (1 row) - + @@ -423,8 +425,9 @@ testdb=> Lists available functions, together with their argument and return types. If pattern (a regular expression) is specified, only matching functions are shown. - If the variable description is set, comments are listed for - each function as well. + If the form \df+ is used, additional information about + each function is shown. Comments for each function can be shown with + the \df? form. @@ -444,7 +447,7 @@ testdb=> If pattern is specified, it is a regular expression restricts the listing to those objects - whose name matches. If the variable description is set, + whose name matches. If one appends a ? to the command name, each object is listed with its associated description, if any. @@ -462,18 +465,19 @@ testdb=> - \do [ name ] + \do [ pattern ] Lists available operators with their operand and return types. - If name + If pattern is specified, only operators with that name will be shown. - (Note that, unlike with similar commands, this is not a regular expression - because operator names were likely to interfere with regular expression - meta-characters.) + (Since this is a regular expression, be sure to quote all special + characters in you operator name with backslashes. To prevent + interpretation of the backslash as a new command, you might also + wish to quote the argument.) - If the variable description is set, comments are listed for + If the form \do? is used, comments are listed for each operator. @@ -495,9 +499,9 @@ testdb=> \dT [ pattern ] - List all data types or only those that match pattern. - If the variable description is set, each type is listed with - its associated description. + Lists all data types or only those that match pattern. + The command forms \dT+ and \dT? show extra information + and the associated descriptions of the types, respectively. @@ -519,8 +523,8 @@ testdb=> The new query buffer is then re-parsed according to the normal rules of psql, where the whole buffer is treated as a single line. (Thus you cannot make scripts this way, - use \i for that.) In particular, this means that - if the query ends (or rather contains) a semicolon, it is immediately + use \i for that.) This means also that + if the query ends with (or rather contains) a semicolon, it is immediately executed. In other cases it will merely wait in the query buffer. @@ -542,10 +546,10 @@ testdb=> Prints the arguments to the standard output. This can be useful to intersperse information in the output of scripts. For example: - + => \echo `date` Tue Oct 26 21:40:57 CEST 1999 - + @@ -579,7 +583,7 @@ Tue Oct 26 21:40:57 CEST 1999 Sends the current query input buffer to the backend and optionally saves the output in filename or pipes the output into a separate Unix shell to execute - command. A blank \g + command. A bare \g is virtually equivalent to a semicolon. A \g with argument is a one-shot alternative to the \o command. @@ -591,8 +595,7 @@ Tue Oct 26 21:40:57 CEST 1999 Give syntax help on the specified SQL command. - If command is not a defined SQL command - or if command is not specified, + If command is not specified, then psql will list all the commands for which syntax help is available. If command @@ -628,7 +631,7 @@ Tue Oct 26 21:40:57 CEST 1999 Reads input from the file filename - and executes it as though it has been typed on the keyboard. + and executes it as though it had been typed on the keyboard. @@ -644,9 +647,9 @@ Tue Oct 26 21:40:57 CEST 1999 \l (or \list) - List all the databases in the server as well as their owners. If the - variable description is set, any descriptions for - the databases are shown as well. If your PostgreSQL + List all the databases in the server as well as their owners. Append a + ? (question mark) to the command name to see any descriptions + for the databases as well. If your PostgreSQL installation was compiled with multibyte encoding support, the encoding scheme of each database is shown as well. @@ -688,15 +691,15 @@ Tue Oct 26 21:40:57 CEST 1999 Stores the file into a PostgreSQL large object. Optionally, it associates the given comment with the object. Example: - + foo=> \lo_import '/home/me/pictures/photo.xcf' 'a picture of me' lo_import 152801 - + The response indicates that the large object received object id 152801 - which one ought to remember if one wants to access the object every again. + which one ought to remember if one wants to access the object ever again. For that reason it is recommended to always associate a human-readable comment with every object. Those can then be seen with the - \lo_list command. + \lo_list? command. @@ -720,8 +723,8 @@ lo_import 152801 Shows a list of all PostgreSQL large objects currently stored in the database along with their owners. - If the variable description is set, the associated - comments are shown as well. + Append a question mark to the command name (\lo_list?) to + see the the associated comments as well. @@ -863,7 +866,7 @@ lo_import 152801 The second argument is a string that should be printed whenever a field is null. The default is not to print anything, which can easily be mistaken - for, say, an empty string. There one might choose to write + for, say, an empty string. Thus, one might choose to write \pset null "(null)". @@ -1015,7 +1018,7 @@ lo_import 152801 - As of psql version 6.6 it is no longer + As of psql version 7.0 it is no longer necessary, in fact, to save the command history as that will be done automatically on program termination. The history is then also automatically loaded every time psql @@ -1034,17 +1037,14 @@ lo_import 152801 Sets the internal variable name to value. If no second argument is given, the variable is unset (which is different from setting it to, - for example, and empty string: \set foo ''). If no + for example, an empty string: \set foo ''). If no arguments are given, all currently defined variables are listed with their values. - Valid variable names can contain lower-case characters, digits, and - underscores. In particular, no upper-case characters are allowed, as - those are reserved for certain magic variables and - environment variables. See the section about psql - variables for details. + Valid variable names can contain characters, digits, and underscores. + See the section about psql variables for details. @@ -1119,14 +1119,14 @@ lo_import 152801 - + test=> \z Access permissions for database "test" Relation | Access permissions ----------+------------------------------------- my_table | {"=r","joe=arwR", "group staff=ar"} (1 row ) - + Read this as follows: @@ -1170,7 +1170,10 @@ Access permissions for database "test" Escapes to a separate Unix shell or executes the Unix command - command. + command. The arguments + are not further interpreted, the shell will see them as is. If you wish + to capture the output of a shell command and/or use psql's + variable substitution features, use the backticks (`). @@ -1290,7 +1293,7 @@ Access permissions for database "test" Use the file filename as the source of queries instead of reading queries interactively. - After the file is processed, terminates. + After the file is processed, psql terminates. This in many ways equivalent to the internal command \i. @@ -1514,12 +1517,12 @@ Access permissions for database "test" The output looks similar to this: - + ~$ psql -V Server: PostgreSQL 6.5.2 on i586-pc-linux-gnu, compiled by egcs psql 6.6.0 on i586-pc-linux-gnu, compiled by gcc 2.8.1 (Oct 27 1999 15:15:04), long options, readline, history, locale, assert checks - + The Server line is identical to the one returned by the backend function version() and thus might vary if you query different servers by using different connection @@ -1553,7 +1556,7 @@ readline, history, locale, assert checks - As of version 6.6, psql automatically issues a + As of version 7.0, psql automatically issues a password prompt whenever the backend requests password authentication. Because this is currently based on a hack the automatic recognition might mysteriously fail, hence this option to force a prompt. @@ -1606,37 +1609,38 @@ readline, history, locale, assert checks psql provides variable substitution features - similar to common Unix command shells. Variables are simply name/values + 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 psql meta-command \set: - + testdb=> \set foo bar - + sets the variable foo to the value bar. To retrieve the content of the variable, precede the name with a dollar-sign and use it as the argument of any slash command: - + testdb=> \echo $foo bar - + Alternatively, the value can also be interpolated into a double-quoted (or backtick-quoted) string, like so: - + testdb=> \echo "foo is now ${foo}." foo is now bar. - + (The curly braces are required. This is not Perl.) No variable substitution will be performed in single-quoted strings or in any of the backslash commands - that have special parsing rules (\copy, \help). + that have special parsing rules (e.g., \copy). The arguments of \set are subject to the same substitution rules as with other commands. Thus you can construct interesting references - such as \set "${foo}bar" 'something' and get variable - variables of Perl or PHP - fame. Unfortunately (or fortunately?), there is not way to do anything useful + such as \set "${foo}bar" 'something' and get soft + links or variable variables of Perl + or PHP fame, respectively. + Unfortunately (or fortunately?), there is not way to do anything useful with these constructs. (\echo ${${foo}} doesn't work.) On the other hand, \set bar $foo is a perfectly valid way to copy a variable. @@ -1645,14 +1649,21 @@ foo is now bar. psql's internal variable names can consist of - lower-case letters, numbers, and underscores in any order and any number of - them. Upper-case letters are not allowed. (There is a reason for that. Keep reading.) - If you attempt to refer to a variable that does not consist of those - characters psql first checks if it is the name of - one of several defined magic variables. Those variables you cannot - set but they always have a value. By convention they all start with an - upper-case letter. Finally, if no match is found that way, the value of - the respective environment variable is substituted. + letters, numbers, and underscores in any order and any number of them. + It is recommended, however, that you stick to lower-case letters and do not + begin with a digit. The partial rationale for this follows. + + + + If you attempt to refer to a variable that is not set, + psql first checks if it is the name of one of + several defined magic variables. Those variables are + maintained internally and always have a value (at least when their semantics + permit it). By convention they all start with an upper-case letter. You can + set those variables manually, but that will shadow their + special meaning, until you unset your personal copy. Finally, if no match is + found that way, the value of the respective environment variable is + substituted. @@ -1660,7 +1671,10 @@ foo is now bar. Version which contains a string with the version of psql; Database, Host, Port, User are the currently active - connection options. + connection options. LastOid contains the oid that was the + result of the last INSERT or \lo_import + command. If the last command was not one of those two, the value + is undefined. @@ -1673,19 +1687,6 @@ foo is now bar. only care whether or not are they set, not what to. A list of all specially treated variables follows. - - description - - - If set, the various \d* commands as well as - \l and \lo_list show object - descriptions along with the normal information. (Except for - \dd which always shows descriptions as this - is its very purpose.) - - - - die_on_error @@ -1731,25 +1732,29 @@ foo is now bar. lo_transaction - If you use the PostgreSQL large object interface to store - data that does not fit into one tuple specially all the operations must be contained - in a transaction block. (See the documentation of the large object interface for - more information.) Since psql has no way to keep track if - you already have a transaction in progress when you call one of its internal commands - \lo_export, \lo_import, \lo_unlink - it must take some arbitrary action. This action could either be to roll back any transaction - that might already be in progress, or to commit any such transaction, or to do nothing - at all. In the latter case you must provide you own BEGIN/END - block or the results are unpredictable (usually resulting in the desired action not being - performed anyway). + If you use the PostgreSQL large object + interface to specially store data that does not fit into one tuple, + all the operations must be contained in a transaction block. (See the + documentation of the large object interface for more information.) Since + psql has no way to keep track if you already + have a transaction in progress when you call one of its internal + commands \lo_export, \lo_import, + \lo_unlink it must take some arbitrary action. This + action could either be to roll back any transaction that might already + be in progress, or to commit any such transaction, or to do nothing at + all. In the latter case you must provide you own + BEGIN TRANSACTION/COMMIT block or + the results will be unpredictable (usually resulting in the desired + action not being performed anyway). To choose what you want to do you set this variable to one of - rollback, commit, or nothing. The default is - to roll back the transaction. If you just want to load one or a few objects this is fine. - However, if you intend to transfer many large objects, it might be advisable to - provide one explicit transaction block around all commands. + rollback, commit, or nothing. + The default is to roll back the transaction. If you just want to load one + or a few objects this is fine. However, if you intend to transfer many + large objects, it might be advisable to provide one explicit transaction + block around all commands. @@ -1794,16 +1799,6 @@ foo is now bar. - - - sql_interpol - - - The escape character for SQL variable interpolation. See below. - - - - @@ -1817,48 +1812,37 @@ foo is now bar. An additional useful feature of psql variables is that you can substitute (interpolate) them into - regular SQL statements. In order not to break existing - SQL statements, you must choose your own special - character that tells psql that you wish to - interpolate the value of a variable here. You do this by setting the - variable sql_interpol. Only the first character will be - looked at. You can set this variable to anything you want but, for instance, - letters, numbers, semicolons, or backslashes will not make your life easier. - Reasonable choices include the dollar ($) and pound - (#) signs. - -testdb=> \set sql_interpol '#' - - - - - Once this is set up, whenever psql sees the - magic character where it would expect a query, it will continue scanning - until it sees the same character again and will interpret anything in - between as a variable name. - + regular SQL statements. The syntax for this is to prepend + the variable name with a colon (:). + testdb=> \set foo 'my_table' -testdb=> SELECT * FROM #foo#; - +testdb=> SELECT * FROM :foo; + 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. - One possible application of this mechanism is to copy the contents of a file + A popular application of this facility is to refer to the last inserted + OID in subsequent statement to build a foreign key + scenario. + Another possible use of this mechanism is to copy the contents of a file into a field. First load the file into a variable and then proceed as above. - + testdb=> \set content `cat my_file.txt` -testdb=> INSERT INTO my_table VALUES ('#content#'); - +testdb=> \set content "'${content}'" +testdb=> INSERT INTO my_table VALUES (:content); + One possible problem with this approach is that my_file.txt 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 + they don't cause a syntax error when the third line is processed. This could be done with the program sed: - + testdb=> \set content `sed -e "s/'/\\\\\\'/g" < my_file.txt` - + Observe the correct number of backslashes (6)! You can resolve it this way: After psql has parsed this line, it passes sed -e "s/'/\\\'/g" < my_file.txt to the shell. The shell @@ -2003,15 +1987,15 @@ testdb=> \set content `sed -e "s/'/\\\\\\'/g" < my_file.txt` The first example shows how to spread a query over several lines of input. Notice the changing prompt. - + testdb=> CREATE TABLE my_table ( testdb-> first int4 not null default 0, testdb-> second text testdb-> ); CREATE - + Now look at the table definition again: - + testdb=> \d my_table Table "my_table" Attribute | Type | Info @@ -2019,15 +2003,15 @@ testdb=> \d my_table first | int4 | not null default 0 second | text | - + At this point you decide to change the prompt to something more interesting: - + testdb=> \set prompt1 '%n@%m %~%R%# ' peter@localhost testdb=> - + Let's assume you have filled the table with data and want to take a look at it: - + peter@localhost testdb=> SELECT * FROM my_table; first | second -------+-------- @@ -2037,11 +2021,11 @@ peter@localhost testdb=> SELECT * FROM my_table; 4 | four (4 rows) - + Notice how the int4 colums in right aligned while the text column in left aligned. You can make this table look differently by using the \pset command. - + peter@localhost testdb=> \pset border 2 Border style is 2. peter@localhost testdb=> SELECT * FROM my_table; @@ -2079,9 +2063,9 @@ one,1 two,2 three,3 four,4 - + Alternatively, use the short commands: - + peter@localhost testdb=> \a \t \x Output format is aligned. Tuples only is off. @@ -2099,7 +2083,7 @@ second | three -[ RECORD 4 ]- first | 4 second | four - + @@ -2123,10 +2107,10 @@ second | four compatibility this is still supported to some extent but I am not going to explain the details here as this use is discouraged. But if you get strange messages, keep this in mind. For example - + testdb=> \foo Field separator is "oo". - + is perhaps not what one would expect. @@ -2138,7 +2122,8 @@ Field separator is "oo". and attempting to get along with each other. Sometimes they do, sometimes they don't. An excellent example of this can be seen in section . - Changing this situation, however, is beyond feasability. + There are vague dreams of using flex in the future, + but it won't happen soon. @@ -2151,6 +2136,15 @@ Field separator is "oo". these limits sooner rather than later. + + + + The number of options for a backslash command is limited, probably to 16. + You can easily change this in the source code, and perhaps I will get around + to fixing this one day (see previous item). Not that there is any command + that actually uses that many options though. + + @@ -2169,9 +2163,9 @@ Field separator is "oo". The present version is the result of a major clean-up and re-write in 1999 by Peter Eisentraut in preparation for release 7.0. - Many people had again contributed their ideas. The author would also like - to recognize the influence of tcsh at a number - of places. + Many people had again contributed their ideas. A bunch of features were stolen + from various shells (in case you hadn't noticed), in particular + tcsh. @@ -2197,9 +2191,9 @@ Field separator is "oo". readline/history.h) in appropriate directories. If you have the library and header files installed in an obscure place you must tell configure about them, for example: - + $ ./configure --with-includes=/opt/gnu/include --with-libraries=/opt/gnu/lib ... - + Then you have to recompile psql (not necessarily the entire code tree). -- cgit v1.2.3