Expressions

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.

Parameters

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';
     

Functional Expressions

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)
     

Aggregate Expressions

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 ( * )

where aggregate_name is a previously defined aggregate, and expression is any expression that doesn't itself contain an aggregate expression.

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.

Target List

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

a_expr [ AS result_attname ]
     

where result_attname is the name of the attribute to be created (or an already existing attribute name in the case of update statements.) If result_attname is not present, then a_expr must contain only one attribute name which is assumed to be the name of the result field. In Postgres default naming is only used if a_expr is an attribute.

Qualification

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

NOT

AND

OR

A clause is an a_expr that evaluates to a boolean over a set of instances.

From List

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

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

where class_reference is of the form

class_name [ * ]
     

The "from expression" defines one or more instance variables to range over the class indicated in class_reference. One can also request the instance variable to range over all classes that are beneath the indicated class in the inheritance hierarchy by postpending the designator asterisk ("*").