psql-ref.sgml

PostgreSQL7.4.6 for Linux

	arguments. In the future this call might show the current status
	of all printing options.
	</para>
	</note>

	</listitem>
      </varlistentry>


      <varlistentry>
        <term><literal>\q</literal></term>
        <listitem>
        <para>
        Quits the <application>psql</application> program.
        </para>
        </listitem>
      </varlistentry>


      <varlistentry>
        <term><literal>\qecho</literal> <replaceable class="parameter">text</replaceable> [ ... ] </term>
        <listitem>
        <para>
	This command is identical to <command>\echo</command> except
	that all output will be written to the query output channel, as
	set by <command>\o</command>.
        </para>
        </listitem>
      </varlistentry>


      <varlistentry>
        <term><literal>\r</literal></term>
        <listitem>
        <para>
        Resets (clears) the query buffer.
        </para>
        </listitem>
      </varlistentry>


      <varlistentry>
        <term><literal>\s</literal> [ <replaceable class="parameter">filename</replaceable> ]</term>
        <listitem>
        <para>
        Print or save the command line history to <replaceable
        class="parameter">filename</replaceable>. If <replaceable
        class="parameter">filename</replaceable> is omitted, the history
        is written to the standard output. This option is only available
        if <application>psql</application> is configured to use the
        <acronym>GNU</acronym> history library.
        </para>

	<note>
	<para>
	In the current version, it is no longer necessary to save the
	command history, since that will be done automatically on
	program termination. The history is also loaded automatically
	every time <application>psql</application> starts up.
	</para>
	</note>
        </listitem>
      </varlistentry>


      <varlistentry>
        <term><literal>\set</literal> [ <replaceable class="parameter">name</replaceable> [ <replaceable class="parameter">value</replaceable> [ ... ]]]</term>

	<listitem>
	<para>
	Sets the internal variable <replaceable
	class="parameter">name</replaceable> to <replaceable
	class="parameter">value</replaceable> 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 <command>\unset</command> command.
	</para>

	<para>
	Valid variable names can contain characters, digits, and
	underscores. See the section <xref
	linkend="APP-PSQL-variables"
	endterm="APP-PSQL-variables-title"> below for details.
	</para>

	<para>
	Although you are welcome to set any variable to anything you
	want, <application>psql</application> treats several variables
	as special. They are documented in the section about variables.
	</para>

	<note>
	<para>
	This command is totally separate from the <acronym>SQL</acronym>
	command <xref linkend="SQL-SET" endterm="SQL-SET-title">.
	</para>
	</note>
	</listitem>
      </varlistentry>


      <varlistentry>
        <term><literal>\t</literal></term>
        <listitem>
        <para>
        Toggles the display of output column name headings and row count
        footer. This command is equivalent to <literal>\pset
        tuples_only</literal> and is provided for convenience.
        </para>
        </listitem>
      </varlistentry>


      <varlistentry>
        <term><literal>\T</literal> <replaceable class="parameter">table_options</replaceable></term>
        <listitem>
        <para>
        Allows you to specify attributes to be placed within the
        <sgmltag>table</sgmltag> tag in <acronym>HTML</acronym> tabular
        output mode. This command is equivalent to <literal>\pset
        tableattr <replaceable
        class="parameter">table_options</replaceable></literal>.
        </para>
        </listitem>
      </varlistentry>


      <varlistentry>
       <term><literal>\timing</literal></term>
        <listitem>
        <para>
         Toggles a display of how long each SQL statement takes, in milliseconds.
        </para>
       </listitem>
      </varlistentry>


      <varlistentry>
        <term><literal>\w</literal> {<replaceable class="parameter">filename</replaceable> | <replaceable class="parameter">|command</replaceable>}</term>
        <listitem>
        <para>
        Outputs the current query buffer to the file <replaceable
        class="parameter">filename</replaceable> or pipes it to the Unix
        command <replaceable class="parameter">command</replaceable>.
        </para>
        </listitem>
      </varlistentry>


      <varlistentry>
        <term><literal>\x</literal></term>
        <listitem>
        <para>
        Toggles extended table formatting mode. As such it is equivalent to
	<literal>\pset expanded</literal>.
       </para>
       </listitem>
      </varlistentry>


      <varlistentry>
        <term><literal>\z</literal> [ <replaceable class="parameter">pattern</replaceable> ]</term>
        <listitem>
        <para>
        Produces a list of all available tables with their
        associated access privileges.
	If a <replaceable class="parameter">pattern</replaceable> is
	specified, only tables whose names match the pattern are listed.
	</para>

	<para>
	The commands <xref linkend="SQL-GRANT"> and
	<xref linkend="SQL-REVOKE">
	are used to set access privileges.  See <xref linkend="SQL-GRANT">
	for more information.
	</para>

	<para>
	This is an alias for <command>\dp</command> (<quote>display
	privileges</quote>).
        </para>
        </listitem>
      </varlistentry>


      <varlistentry>
        <term><literal>\!</literal> [ <replaceable class="parameter">command</replaceable> ]</term>
        <listitem>
        <para>
        Escapes to a separate Unix shell or executes the Unix command
        <replaceable class="parameter">command</replaceable>. The
        arguments are not further interpreted, the shell will see them
        as is.
        </para>
        </listitem>
      </varlistentry>


      <varlistentry>
        <term><literal>\?</literal></term>
        <listitem>
        <para>
        Shows help information about the backslash commands.
        </para>
        </listitem>
      </varlistentry>

    </variablelist>
  </para>

  <para>
   The various <literal>\d</> commands accept a <replaceable
   class="parameter">pattern</replaceable> parameter to specify the
   object name(s) to be displayed.  <literal>*</> means <quote>any
   sequence of characters</> and <literal>?</> means <quote>any single
   character</>.  (This notation is comparable to Unix shell file name
   patterns.)  Advanced users can also use regular-expression
   notations such as character classes, for example <literal>[0-9]</>
   to match <quote>any digit</>.  To make any of these
   pattern-matching characters be interpreted literally, surround it
   with double quotes.
  </para>

  <para>
   A pattern that contains an (unquoted) dot is interpreted as a schema
   name pattern followed by an object name pattern.  For example,
   <literal>\dt foo*.bar*</> displays all tables in schemas whose name
   starts with <literal>foo</> and whose table name 
   starts with <literal>bar</>.  If no dot appears, then the pattern
   matches only objects that are visible in the current schema search path.
  </para>

  <para>
   Whenever the <replaceable class="parameter">pattern</replaceable> parameter
   is omitted completely, the <literal>\d</> commands display all objects
   that are visible in the current schema search path.  To see all objects
   in the database, use the pattern <literal>*.*</>.
  </para>
 </refsect2>

 <refsect2>
  <title>Advanced features</title>

   <refsect3 id="APP-PSQL-variables">
    <title id="APP-PSQL-variables-title">Variables</title>

    <para>
    <application>psql</application> 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
    <application>psql</application> meta-command
    <command>\set</command>:
<programlisting>
testdb=> <userinput>\set foo bar</userinput>
</programlisting>
    sets the variable <literal>foo</literal> to the value
    <literal>bar</literal>. To retrieve the content of the variable, precede
    the name with a colon and use it as the argument of any slash
    command:
<programlisting>
testdb=> <userinput>\echo :foo</userinput>
bar
</programlisting>
    </para>

    <note>
    <para>
    The arguments of <command>\set</command> are subject to the same
    substitution rules as with other commands. Thus you can construct
    interesting references such as <literal>\set :foo
    'something'</literal> and get <quote>soft links</quote> or
    <quote>variable variables</quote> of <productname>Perl</productname>
    or <productname><acronym>PHP</acronym></productname> fame,
    respectively. Unfortunately (or fortunately?), there is no way to do
    anything useful with these constructs. On the other hand,
    <literal>\set bar :foo</literal> is a perfectly valid way to copy a
    variable.
    </para>
    </note>

    <para>
    If you call <command>\set</command> without a second argument, the
    variable is set, with an empty string as value. To unset (or delete) a
    variable, use the command <command>\unset</command>.
    </para>

    <para>
    <application>psql</application>'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 <application>psql</application>. 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.
   </para>

    <variablelist>
      <varlistentry>
        <term><varname>AUTOCOMMIT</varname></term>
	<listitem>
	<para>
	When <literal>on</> (the default), each SQL command is automatically
	committed upon successful completion.  To postpone commit in this
	mode, you must enter a <command>BEGIN</> or <command>START
	TRANSACTION</> SQL command.  When <literal>off</> or unset, SQL
	commands are not committed until you explicitly issue
	<command>COMMIT</> or <command>END</>.  The autocommit-off
	mode works by issuing an implicit <command>BEGIN</> for you, just
	before any command that is not already in a transaction block and
	is not itself a <command>BEGIN</> or other transaction-control
	command.
	</para>

	<note>
	<para>
	 In autocommit-off mode, you must explicitly abandon any failed
	 transaction by entering <command>ABORT</> or <command>ROLLBACK</>.
	 Also keep in mind that if you exit the session
	 without committing, your work will be lost.
	</para>
	</note>

	<note>
	<para>
	 The autocommit-on mode is <productname>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
	 your <filename>.psqlrc</filename> file.
	</para>
	</note>
	</listitem>
      </varlistentry>

      <varlistentry>
        <term><varname>DBNAME</varname></term>
	<listitem>
	<para>
        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.
	</para>
	</listitem>
      </varlistentry>

      <varlistentry>
        <term><varname>ECHO</varname></term>
	<listitem>
	<para>
	If set to <literal>all</literal>, all lines
	entered 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 <option>-a</option>. If set to
	<literal>queries</literal>,
	<application>psql</application> merely prints all queries as
	they are sent to the server. The switch for this is
	<option>-e</option>.
	</para>
	</listitem>
      </varlistentry>

      <varlistentry>
        <term><varname>ECHO_HIDDEN</varname></term>
	<listitem>
	<para>
	When this variable is set and a backslash command queries the
	database, the query is first shown. This