18th October 2018: PostgreSQL 11 Released!

Unsupported versions:
7.0

SQL92 allows *expressions* to transform data in tables.
Expressions may contain operators (see Operators for more details) and functions
(Functions has more
information).

An expression is one of the following:

( a_expr ) |

constant |

attribute |

a_expr binary_operator a_expr |

a_expr right_unary_operator |

left_unary_operator
a_expr |

parameter |

functional expression |

aggregate expression |

We have already discussed constants and attributes. The three kinds of operator expressions indicate respectively binary (infix), right-unary (suffix) and left-unary (prefix) operators. The following sections discuss the remaining options.

A *parameter* is used to indicate a
parameter in a SQL function. Typically this is used in SQL function
definition statement. The form of a parameter is:

$number

For example, consider the definition of a function, `dept`, as

CREATE FUNCTION dept (name) RETURNS dept AS 'select * from dept where name=$1' LANGUAGE 'sql';

A *functional expression* is the name of
a legal SQL function, followed by its argument list enclosed in
parentheses:

function(a_expr[,a_expr... ] )

For example, the following computes the square root of an employee salary:

sqrt(emp.salary)

An *aggregate expression* represents the
application of an aggregate function across the rows selected by a
query. An aggregate function reduces multiple inputs to a single
output value, such as the sum or average of the inputs. The syntax
of an aggregate expression is one of the following:

aggregate_name (expression) |

aggregate_name (ALL
expression) |

aggregate_name (DISTINCT
expression) |

aggregate_name ( * ) |

The first form of aggregate expression invokes the aggregate across all input rows for which the given expression yields a non-null value. The second form is the same as the first, since ALL is the default. The third form invokes the aggregate for all distinct non-null values of the expression found in the input rows. The last form invokes the aggregate once for each input row regardless of null or non-null values; since no particular input value is specified, it is generally only useful for the count() aggregate.

For example, count(*) yields the total number of input rows; count(f1) yields the number of input rows in which f1 is non-null; count(distinct f1) yields the number of distinct non-null values of f1.

A *target list* is a parenthesized,
comma-separated list of one or more elements, each of which must be
of the form:

wherea_expr[ ASresult_attname]

A *qualification* consists of any number
of clauses connected by the logical operators:

NOT |

AND |

OR |

The *from list* is a comma-separated list
of *from expressions*. Each "from
expression" is of the form:

[whereclass_reference]instance_variable{, [class_ref]instance_variable... }

The "from expression" defines one or more instance variables to range over the class indicated inclass_name[ * ]