syntax.sgml

PostgreSQL7.4.6 for Linux

      <row>
       <entry><token>&lt;</token> <token>&gt;</token></entry>
       <entry></entry>
       <entry>less than, greater than</entry>
      </row>

      <row>
       <entry><token>=</token></entry>
       <entry>right</entry>
       <entry>equality, assignment</entry>
      </row>

      <row>
       <entry><token>NOT</token></entry>
       <entry>right</entry>
       <entry>logical negation</entry>
      </row>

      <row>
       <entry><token>AND</token></entry>
       <entry>left</entry>
       <entry>logical conjunction</entry>
      </row>

      <row>
       <entry><token>OR</token></entry>
       <entry>left</entry>
       <entry>logical disjunction</entry>
      </row>
     </tbody>
    </tgroup>
   </table>

   <para>
    Note that the operator precedence rules also apply to user-defined
    operators that have the same names as the built-in operators
    mentioned above.  For example, if you define a
    <quote>+</quote> operator for some custom data type it will have
    the same precedence as the built-in <quote>+</quote> operator, no
    matter what yours does.
   </para>

   <para>
    When a schema-qualified operator name is used in the
    <literal>OPERATOR</> syntax, as for example in
<programlisting>
SELECT 3 OPERATOR(pg_catalog.+) 4;
</programlisting>
    the <literal>OPERATOR</> construct is taken to have the default precedence
    shown in <xref linkend="sql-precedence-table"> for <quote>any other</> operator.  This is true no matter
    which specific operator name appears inside <literal>OPERATOR()</>.
   </para>
  </sect2>
 </sect1>

 <sect1 id="sql-expressions">
  <title>Value Expressions</title>

  <indexterm zone="sql-expressions">
   <primary>expression</primary>
   <secondary>syntax</secondary>
  </indexterm>

  <indexterm zone="sql-expressions">
   <primary>value expression</primary>
  </indexterm>

  <indexterm>
   <primary>scalar</primary>
   <see>expression</see>
  </indexterm>

  <para>
   Value expressions are used in a variety of contexts, such
   as in the target list of the <command>SELECT</command> command, as
   new column values in <command>INSERT</command> or
   <command>UPDATE</command>, or in search conditions in a number of
   commands.  The result of a value expression is sometimes called a
   <firstterm>scalar</firstterm>, to distinguish it from the result of
   a table expression (which is a table).  Value expressions are
   therefore also called <firstterm>scalar expressions</firstterm> (or
   even simply <firstterm>expressions</firstterm>).  The expression
   syntax allows the calculation of values from primitive parts using
   arithmetic, logical, set, and other operations.
  </para>

  <para>
   A value expression is one of the following:

   <itemizedlist>
    <listitem>
     <para>
      A constant or literal value.
     </para>
    </listitem>

    <listitem>
     <para>
      A column reference.
     </para>
    </listitem>

    <listitem>
     <para>
      A positional parameter reference, in the body of a function definition
      or prepared statement.
     </para>
    </listitem>

    <listitem>
     <para>
      A subscripted expression.
     </para>
    </listitem>

    <listitem>
     <para>
      A field selection expression.
     </para>
    </listitem>

    <listitem>
     <para>
      An operator invocation.
     </para>
    </listitem>

    <listitem>
     <para>
      A function call.
     </para>
    </listitem>

    <listitem>
     <para>
      An aggregate expression.
     </para>
    </listitem>

    <listitem>
     <para>
      A type cast.
     </para>
    </listitem>

    <listitem>
     <para>
      A scalar subquery.
     </para>
    </listitem>

    <listitem>
     <para>
      An array constructor.
     </para>
    </listitem>

    <listitem>
     <para>
      Another value expression in parentheses, useful to group
      subexpressions and override
      precedence.<indexterm><primary>parenthesis</></>
     </para>
    </listitem>
   </itemizedlist>
  </para>

  <para>
   In addition to this list, there are a number of constructs that can
   be classified as an expression but do not follow any general syntax
   rules.  These generally have the semantics of a function or
   operator and are explained in the appropriate location in <xref
   linkend="functions">.  An example is the <literal>IS NULL</literal>
   clause.
  </para>

  <para>
   We have already discussed constants in <xref
   linkend="sql-syntax-constants">.  The following sections discuss
   the remaining options.
  </para>

  <sect2>
   <title>Column References</title>

   <indexterm>
    <primary>column reference</primary>
   </indexterm>

   <para>
    A column can be referenced in the form
<synopsis>
<replaceable>correlation</replaceable>.<replaceable>columnname</replaceable>
</synopsis>
   </para>

   <para>
    <replaceable>correlation</replaceable> is the name of a
    table (possibly qualified with a schema name), or an alias for a table
    defined by means of a <literal>FROM</literal> clause, or one of
    the key words <literal>NEW</literal> or <literal>OLD</literal>.
    (<literal>NEW</literal> and <literal>OLD</literal> can only appear in rewrite rules,
    while other correlation names can be used in any SQL statement.)
    The correlation name and separating dot may be omitted if the column name
    is unique across all the tables being used in the current query.  (See also <xref linkend="queries">.)
   </para>
  </sect2>

  <sect2>
   <title>Positional Parameters</title>

   <indexterm>
    <primary>parameter</primary>
    <secondary>syntax</secondary>
   </indexterm>

   <indexterm>
    <primary>$</primary>
   </indexterm>

   <para>
    A positional parameter reference is used to indicate a value
    that is supplied externally to an SQL statement.  Parameters are
    used in SQL function definitions and in prepared queries.  Some
    client libraries also support specifying data values separately
    from the SQL command string, in which case parameters are used to
    refer to the out-of-line data values.
    The form of a parameter reference is:
<synopsis>
$<replaceable>number</replaceable>
</synopsis>
   </para>

   <para>
    For example, consider the definition of a function,
    <function>dept</function>, as

<programlisting>
CREATE FUNCTION dept(text) RETURNS dept
    AS 'SELECT * FROM dept WHERE name = $1'
    LANGUAGE SQL;
</programlisting>

    Here the <literal>$1</literal> will be replaced by the first
    function argument when the function is invoked.
   </para>
  </sect2>

  <sect2>
   <title>Subscripts</title>

   <indexterm>
    <primary>subscript</primary>
   </indexterm>

   <para>
    If an expression yields a value of an array type, then a specific
    element of the array value can be extracted by writing
<synopsis>
<replaceable>expression</replaceable>[<replaceable>subscript</replaceable>]
</synopsis>
    or multiple adjacent elements (an <quote>array slice</>) can be extracted
    by writing
<synopsis>
<replaceable>expression</replaceable>[<replaceable>lower_subscript</replaceable>:<replaceable>upper_subscript</replaceable>]
</synopsis>
    (Here, the brackets <literal>[ ]</literal> are meant to appear literally.)
    Each <replaceable>subscript</replaceable> is itself an expression,
    which must yield an integer value.
   </para>

   <para>
    In general the array <replaceable>expression</replaceable> must be
    parenthesized, but the parentheses may be omitted when the expression
    to be subscripted is just a column reference or positional parameter.
    Also, multiple subscripts can be concatenated when the original array
    is multi-dimensional.
    For example,

<programlisting>
mytable.arraycolumn[4]
mytable.two_d_column[17][34]
$1[10:42]
(arrayfunction(a,b))[42]
</programlisting>

    The parentheses in the last example are required.
    See <xref linkend="arrays"> for more about arrays.
   </para>
  </sect2>

  <sect2>
   <title>Field Selection</title>

   <indexterm>
    <primary>field selection</primary>
   </indexterm>

   <para>
    If an expression yields a value of a composite type (row type), then a
    specific field of the row can be extracted by writing
<synopsis>
<replaceable>expression</replaceable>.<replaceable>fieldname</replaceable>
</synopsis>
   </para>

   <para>
    In general the row <replaceable>expression</replaceable> must be
    parenthesized, but the parentheses may be omitted when the expression
    to be selected from is just a table reference or positional parameter.
    For example,

<programlisting>
mytable.mycolumn
$1.somecolumn
(rowfunction(a,b)).col3
</programlisting>

    (Thus, a qualified column reference is actually just a special case
    of the field selection syntax.)
   </para>
  </sect2>

  <sect2>
   <title>Operator Invocations</title>

   <indexterm>
    <primary>operator</primary>
    <secondary>invocation</secondary>
   </indexterm>

   <para>
    There are three possible syntaxes for an operator invocation:
    <simplelist>
     <member><replaceable>expression</replaceable> <replaceable>operator</replaceable> <replaceable>expression</replaceable> (binary infix operator)</member>
     <member><replaceable>operator</replaceable> <replaceable>expression</replaceable> (unary prefix operator)</member>
     <member><replaceable>expression</replaceable> <replaceable>operator</replaceable> (unary postfix operator)</member>
    </simplelist>
    where the <replaceable>operator</replaceable> token follows the syntax
    rules of <xref linkend="sql-syntax-operators">, or is one of the
    key words <token>AND</token>, <token>OR</token>, and
    <token>NOT</token>, or is a qualified operator name in the form
<synopsis>
<literal>OPERATOR(</><replaceable>schema</><literal>.</><replaceable>operatorname</><literal>)</>
</synopsis>
    Which particular operators exist and whether
    they are unary or binary depends on what operators have been
    defined by the system or the user.  <xref linkend="functions">
    describes the built-in operators.
   </para>
  </sect2>

  <sect2>
   <title>Function Calls</title>

   <indexterm>
    <primary>function</primary>
    <secondary>invocation</secondary>
   </indexterm>

   <para>
    The syntax for a function call is the name of a function
    (possibly qualified with a schema name), followed by its argument list
    enclosed in parentheses:

<synopsis>
<replaceable>function</replaceable> (<optional><replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ... </optional></optional> )
</synopsis>
   </para>

   <para>
    For example, the following computes the square root of 2:
<programlisting>
sqrt(2)