4.2. PostgreSQL-Value expressions

4.2. Value expressions

Value expressions are used in a variety of syntax environments, such as in the target list of theSELECTcommand, as new column values inINSERTorUPDATE, or in search conditions for many commands. We sometimes call the result of a value expression a scalar so that it differs from the result of a table expression (a table). Therefore, a value expression is also called a scalar expression (or simply an expression ). The expression syntax allows arithmetic, logic, collection, and other operations on numeric values from the base part.

A value expression is one of the following:

• A constant or literal value

• A field reference

• A positional parameter reference (in the body of the function declaration or in a pre-written statement)

• An subscript expression

• A field-selection expression

• An operator call

• A function call

• A Clustered expression

• A type conversion

• A scalar quantum query

• An array constructor

• A row constructor

• A value expression inside the parentheses (available for sub-expression grouping and overriding precedence)

In addition to this list, there are many constructs that can be categorized as expressions, but do not follow any common syntax rules. They usually have the semantics of a function or operator and are described in the appropriate position in Chapter 9. An example is the ISNULLclause.

We have already discussed the content in the Section 4.1.2. The following sections discuss the remaining options.

4.2.1. Field references

A field can be referenced in the following form:

correlation.ColumnName

correlationis the name of a table (which may have schema adornments), orthe alias of a table defined with a method such as the FROM clause, or thekeywordnewor old (newandold can only appear in one rewrite rule, and other related names can be used in any SQL statement. If the name of the field is unique in all tables used in the current query, then the associated name (correlation) and the separated point can be omitted (see Chapter 7).

4.2.2. Position parameters

Positional parameter references are used to identify parameters that are externally given to SQL statements. Parameters are used for SQL function definition statements and for pre-written queries. Some client libraries also support declaring data values outside of the SQL command string, in which case the arguments are used to reference data outside the line of the SQL string. The form of a parameter is as follows:

$ number

For exampledept, the definition of the following function

CREATE FUNCTION dept(text) RETURNS dept
    AS $$ SELECT * FROM dept WHERE name = $1 $$
    LANGUAGE SQL;

This will refer to the first parameter whenthe function is called.

4.2.3. Subscript

If an expression produces a numeric value of an array type, then we can extract the elements in the array by an expression like the following

expression[subscript]

If multiple adjacent elements ("array fragments") can be extracted using the following method ([]appear as literal text)

expression[lower_subscript:upper_subscript]

Eachsubscriptitself is an expression that must generate an integer value.

In general, arrayexpressionmust be surrounded by parentheses, but if it is just a field reference or a positional parameter, the parentheses can be omitted. Similarly, if the source array is multidimensional, then multiple subscripts can be concatenated together. Like what:

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

The parentheses in the last example are necessary. See section 8.10 For more information about arrays.

4.2.4. Field Selection

If an expression generates a composite type (row type), then the following method can be used to extract a specified field

expression.FieldName

Usually, the lineexpressionmust be surrounded by parentheses, but if the expression you want to select is just a table reference or positional parameter, you can omit the parentheses. Like what

Mytable.mycolumn$1.somecolumn (Rowfunction (A, B)). col3

Therefore, a full-name field reference is really just a special case of a field selection syntax.

4.2.5. Operator invocation

There are three types of syntax for operator invocation:

expressionoperatorexpression(Binocular infix operator)

operatorexpression(Single-current prefix operator)

expressionoperator(monocular suffix operator)

Here's operatorThe notation follows the grammatical rules of the section 4.1.3, or the notationand,OR,notOne. or a modified operator name.

OPERATOR (schema.Operatorname)

Which operators exist and whether they are monocular or binocular depends on what operators are defined by the system or user. Chapter 9 describes the built-in operators. And Http://www.infocool.net can refer to relevant information.

4.2.6. Function calls

The syntax for a function call is a valid function name (possibly a schema name decoration) followed by a parenthesis that contains a list of arguments:

function([expression[expression...]])

For example, the following code calculates the square root of 2:

sqrt (2)

The list of built-in functions is in Chapter 9. Other functions can be added by the user.

4.2.7. Aggregation expressions

A clustered expression represents the processing of a clustered function on the rows selected by the query. A aggregation function reduces multiple inputs to an output value, such as summing or averaging inputs. The syntax for a clustered expression is one of the following:

 
 
aggregate_name (expression [ , ... ] )
aggregate_name (ALL expression [ , ... ] )
aggregate_name (DISTINCT expression [ , ... ] )
aggregate_name ( * )

Theaggregate_namehere is the previously defined aggregation (which may be the full name of the pattern), and expression is an arbitrary value expression that does not itself contain a clustered expression.

The first form of the aggregation expression is called aggregation for all non-null input rows (in fact, ignoring NULL is determined by the aggregate function, but all standard aggregate functions ignore them). The second form is equivalent to the first (because all is the default value). The third form calls the aggregation for all the unique non-NULL values in all the input rows. The last form calls the aggregation for each input row, whether NULL or not, because no specific input values are declared. Usually it is used only forcount(*)aggregation functions.

For example,count (*)generates the total number of input rows; count (F1)generatesF1non-nullable input rows;count (distinct F1)generatesF1the number of unique and non-NULL rows.

The predefined aggregation functions are described in section 9.15. Other aggregation functions can be increased by the user.

A clustered expression can only appear in the result list or havingclause of theSELECTcommand. It is forbidden to appear in other clauses (such as aWHEREclause) because these clauses are logically calculated before the aggregation results are generated.

If a clustered expression appears in a subquery (see section 4.2.9 and section 9.16), aggregation is usually calculated in a subquery. However, the exception is if the clustered parameter contains only the variables of the outer query: This aggregation is the outermost query that is closest to him and is evaluated on that query. The aggregate expression is a reference to the subquery's external query as a whole, and it acts as a constant in each calculation of the subquery. The preceding limitation (the aggregation expression can only appear in the resultcolumn or the HAVING clause) applies only to the query layer to which the aggregation belongs.

"Attention" PostgreSQL does not currently supportDISTINCTwith multiple input expressions.

4.2.8. Type conversions

A type conversion declares a transformation from one data type to another. PostgreSQL accepts two types of equivalent type conversion syntax:

expressiontype)expression::type

TheCASTsyntax follows the SQL standard;::syntax is a PostgreSQL historical usage.

If you apply a transform to a value expression of a known type, it represents a run-time type conversion. The conversion succeeds only if the appropriate type conversion operation has already been defined. Note that this is slightly different from the conversion for constants (as shown in section 4.1.2.5). A transformation applied to a string literal represents the value given to the string literal to an initial type, so it succeeds for any type (if the contents of the string literal conform to the input syntax of that data type).

If the value of a value expression is not confusing for a type, then we can omit the explicit type conversion (for example, when assigning a value to a table field), and the system automatically performs the type conversion. However, the automatic conversion applies only to those conversion functions marked "OK to apply implicitly" in the system tables. Other conversion functions must be called with explicit conversion syntax. These restrictions are designed to avoid some weird conversions being automatically applied.

We can also declare a type conversion with the syntax of a function style:

TypeNameexpression)

However, this method can only be used for types whose type names are also valid function names. For example,double precisioncannot be used in this way, but the equivalentfloat8can. Similarly,interval,time, andtimestampcan only be used if you add double quotes, because there is a syntax conflict. Therefore, the type conversion of a function style causes inconsistencies, so use should be avoided. The function-like syntax is actually a function call. If you use two standard conversion syntaxes for run-time conversions, it will invoke a registered function to perform the conversion internally. Typically, this conversion function has the same name as their output type, but a program that can be ported cannot rely on this.

4.2.9. Scalar Quantum Query

A scalar subquery is a generalSELECTquery that is placed in parentheses and returns only one row (see Chapter 7 for information on writing queries). TheSELECTis executed, and its return value is used in the surrounding value expression. It is wrong to have a query that returns more than one row or more than a column for a scalar query. However, a subquery does not return a row but is not an error (a scalar result is considered NULL). Subqueries can reference variables of the perimeter query, which are used as constants in each subquery. See section 9.16 and http://www.infocool.net for additional expressions that contain subqueries.

For example, the following query finds cities with the largest population in each state:

Select Name, (select Max (pop) from cities WHERE cities.state = states.name) from    states;

4.2.10. Array Builder

An array constructor is an expression that constructs an array value from its own member element. A simple array constructor consists of a keywordarray, a left bracket[, one or more expressions that represent the value of an array element (separated by commas), and a right parenthesis]. Like what

SELECT ARRAY[1,2,3+4];
  array
---------
 {1,2,7}
(1 row)

The array element type is the public type of the member expression, which isdetermined using the same rules as theUNIONor case construct (see section 10.5).

Multidimensional array values can be made by means of nested arrays constructors. TheARRAYkeyword in the inner-layer constructor can be omitted. For example, the following two sentences produce the same result:

SELECT ARRAY[ARRAY[1,2], ARRAY[3,4]];
     array
---------------
 {{1,2},{3,4}}
(1 row)

SELECT ARRAY[[1,2],[3,4]];
     array
---------------
 {{1,2},{3,4}}
(1 row)

Because multidimensional arrays must be squares, the inner-layer constructors of the same layer must produce sub-arrays of the same dimension.

A multidimensional array constructor element can be anything that produces an appropriate array, not just a sub-arrayconstruct. Like what:

CREATE TABLE arr(f1 int[], f2 int[]);

INSERT INTO arr VALUES (ARRAY[[1,2],[3,4]], ARRAY[[5,6],[7,8]]);

SELECT ARRAY[f1, f2, ‘{{9,10},{11,12}}‘::int[]] FROM arr;
                     array
------------------------------------------------
 {{{1,2},{3,4}},{{5,6},{7,8}},{{9,10},{11,12}}}
(1 row)

We can also construct an array from the results of a subquery. At this point, the array constructor is the keywordarrayfollowed by a subquery surrounded by parentheses (not square brackets). Like what:

SELECT ARRAY(SELECT oid FROM pg_proc WHERE proname LIKE ‘bytea%‘);
                          ?column?
-------------------------------------------------------------
 {2011,1954,1948,1952,1951,1244,1950,2005,1949,1953,2006,31}
(1 row)

The subquery must return only a single field. The resulting one-dimensional array generates an element for each row of results in the subquery, and the element type matches the output field of the subquery.

Thearray index created with array is always from the beginning. For more information about arrays, see section 8.10.

4.2.11. Row constructors

A row constructor is an expression that constructs a row value (also called a compound type value) from the value of the member field provided to it. A row constructor consists of a keywordrow, a left parenthesis, 0 or more expressions that are the values of the row fields (separated by commas), and a right parenthesis. Like what:

SELECT ROW (1,2.5, ' This is a test ');

If there are multiple expressions in the list, the keywordROWis optional.

The row constructor can contain therowvalue. *syntax, which will be extended as a list of line-valued elements as if the. *syntax is used at the top level of aSELECTlist. For example, if the tableThasF1andF2two fields, the following two sentences are equivalent:

SELECT ROW(t.*, 42) FROM t;
SELECT ROW(t.f1, t.f2, 42) FROM t;

"Note" before PostgreSQL 8.2,. *syntax is not extended, so row(t.*)creates a two-field row whose first field is the value of another row. New behaviors are often more useful. If you need old-fashioned nested row values, write the inner row values as not included. *, such asrow (T, a).

By default, the value created by theROWexpression is an anonymous record type. If necessary, you can convert it to a named compound type (either a row type for a table or a composite type created with Createtypeas). There may be a need for a clear conversion to avoid ambiguity. Like what:

CREATE TABLE mytable (f1 int, f2 float, f3 text);

CREATE FUNCTION getf1 (mytable) RETURNS int AS ‘SELECT $ 1.f1’ LANGUAGE SQL;

-Because only one getf1 () exists, no type conversion is required
SELECT getf1 (ROW (1,2.5, ‘this is a test’));
  getf1
-------
      1
(1 row)

CREATE TYPE myrowtype AS (f1 int, f2 text, f3 numeric);

CREATE FUNCTION getf1 (myrowtype) RETURNS int AS ‘SELECT $ 1.f1’ LANGUAGE SQL;

-Now we need a type conversion to indicate which function is called:
SELECT getf1 (ROW (1,2.5, ‘this is a test’));
ERROR: function getf1 (record) is not unique

SELECT getf1 (ROW (1,2.5, ‘this is a test’) :: mytable);
  getf1
-------
      1
(1 row)

SELECT getf1 (CAST (ROW (11, ‘this is a test’, 2.5) AS myrowtype));
  getf1
-------
     11
(1 row)

A row constructor can be used to make a composite type value stored in a Compound Type field, or to a function that accepts a compound type parameter. Alternatively, we can use it to compare two rows of values or to test a row value with isnullor is notnull, for example:

SELECT ROW(1,2.5,‘this is a test‘) = ROW(1, 3, ‘not the same‘);

SELECT ROW(table.*) IS NULL FROM table;  -- detect all-null rows

For more details, see section 9.17. Row constructors can also be used to concatenate subqueries, which are discussed in detail in section 9.16.

4.2.12. Expression calculation rules

The order in which the sub-expressions are evaluated is undefined. In particular, the input of an operator or function is not necessarily calculated in the left-to-right order or in a particular order.

In addition, if the result of an expression can be obtained by judging only a portion of it, then the other sub-expressions can be completely non-computed. For example, if we write this

SELECT true OR somefunc ();

ThenSomeFunc ()will not be called at all (probably). Even if it's written like this.

SELECT SomeFunc () OR true;

Note that this is different from the left-to-right "short circuit" in some programming languages.

Therefore, it is unwise to take a function that has side effects as part of a complex expression. It is particularlydangerous to rely on side-effects in thewhereand having clauses, or to calculate the order, because these clauses are heavily re-processed as part of generating an execution plan. The Boolean expression in these clauses (the combinationof and/or/not) can be identified in any manner permitted by the Boolean algebra operation law.

If you need to force the calculation order,You can use the case construct (see section 9.13). For example, here is an unreliable method that attempts to avoid being removed by 0 in thewhereclause:

SELECT ... WHERE x <> 0 and y/x > 1.5;

For more information refer to http://www.infocool.net/PostgreSQL/index.htm

But the following is safe:

SELECT ... WHERE case is x <> 0 then y/x > 1.5 ELSE false END;

This style ofcase constructs blocks optimizations, so it should be used only when necessary. In this particular case, it's no wonder thaty > 1.5*xis better.

4.2. PostgreSQL-Value expressions