In this section and the following ones, we describe all the statement types that are explicitly understood by PL/pgSQL . Anything not recognized as one of these statement types is presumed to be an SQL command and is sent to the main database engine to execute, as described in Section 43.5.2.
An assignment of a value to a PL/pgSQL variable is written as:
variable< := | = >expression;
As explained previously, the expression in such a statement is evaluated by means of an SQL SELECT command sent to the main database engine. The expression must yield a single value (possibly a row value, if the variable is a row or record variable). The target variable can be a simple variable (optionally qualified with a block name), a field of a row or record target, or an element or slice of an array target. Equal ( = ) can be used instead of PL/SQL-compliant := .
If the expression's result data type doesn't match the variable's data type, the value will be coerced as though by an assignment cast (see Section 10.4). If no assignment cast is known for the pair of data types involved, the PL/pgSQL interpreter will attempt to convert the result value textually, that is by applying the result type's output function followed by the variable type's input function. Note that this could result in run-time errors generated by the input function, if the string form of the result value is not acceptable to the input function.
tax := subtotal * 0.06; my_record.user_id := 20; my_array[j] := 20; my_array[1:3] := array[1,2,3]; complex_array[n].realpart = 12.3;
In general, any SQL command that does not return rows can be executed within a PL/pgSQL function just by writing the command. For example, you could create and fill a table by writing
CREATE TABLE mytable (id int primary key, data text); INSERT INTO mytable VALUES (1,'one'), (2,'two');
If the command does return rows (for example SELECT , or INSERT / UPDATE / DELETE with RETURNING ), there are two ways to proceed. When the command will return at most one row, or you only care about the first row of output, write the command as usual but add an INTO clause to capture the output, as described in Section 43.5.3. To process all of the output rows, write the command as the data source for a FOR loop, as described in Section 43.6.6.
Usually it is not sufficient just to execute statically-defined SQL commands. Typically you'll want a command to use varying data values, or even to vary in more fundamental ways such as by using different table names at different times. Again, there are two ways to proceed depending on the situation.
PL/pgSQL variable values can be automatically inserted into optimizable SQL commands, which are SELECT , INSERT , UPDATE , DELETE , MERGE , and certain utility commands that incorporate one of these, such as EXPLAIN and CREATE TABLE . AS SELECT . In these commands, any PL/pgSQL variable name appearing in the command text is replaced by a query parameter, and then the current value of the variable is provided as the parameter value at run time. This is exactly like the processing described earlier for expressions; for details see Section 43.11.1.
When executing an optimizable SQL command in this way, PL/pgSQL may cache and re-use the execution plan for the command, as discussed in Section 43.11.2.
Non-optimizable SQL commands (also called utility commands) are not capable of accepting query parameters. So automatic substitution of PL/pgSQL variables does not work in such commands. To include non-constant text in a utility command executed from PL/pgSQL , you must build the utility command as a string and then EXECUTE it, as discussed in Section 43.5.4.
EXECUTE must also be used if you want to modify the command in some other way than supplying a data value, for example by changing a table name.
Sometimes it is useful to evaluate an expression or SELECT query but discard the result, for example when calling a function that has side-effects but no useful result value. To do this in PL/pgSQL , use the PERFORM statement:
PERFORM query;
This executes query and discards the result. Write the query the same way you would write an SQL SELECT command, but replace the initial keyword SELECT with PERFORM . For WITH queries, use PERFORM and then place the query in parentheses. (In this case, the query can only return one row.) PL/pgSQL variables will be substituted into the query just as described above, and the plan is cached in the same way. Also, the special variable FOUND is set to true if the query produced at least one row, or false if it produced no rows (see Section 43.5.5).
One might expect that writing SELECT directly would accomplish this result, but at present the only accepted way to do it is PERFORM . An SQL command that can return rows, such as SELECT , will be rejected as an error unless it has an INTO clause as discussed in the next section.
PERFORM create_mv('cs_session_page_requests_mv', my_query);