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 query, and is sent to the main database engine to execute (after substitution for any PL/pgSQL variables used in the statement). Thus, for example, SQL INSERT, UPDATE, and DELETE commands may be considered to be statements of PL/pgSQL. But they are not specifically listed here.
An assignment of a value to a variable or row/record field is written as:
identifier := expression;
As explained above, 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.
If the expression's result data type doesn't match the variable's data type, or the variable has a specific size/precision (as for char(20)), the result value will be implicitly converted by the PL/pgSQL interpreter using the result type's output-function and the variable type's input-function. Note that this could potentially result in runtime errors generated by the input function, if the string form of the result value is not acceptable to the input function.
Examples:
user_id := 20; tax := subtotal * 0.06;
The result of a SELECT command yielding multiple columns (but only one row) can be assigned to a record variable, rowtype variable, or list of scalar variables. This is done by:
SELECT INTO target expressions FROM ...;
where target can be a record variable, a row variable, or a comma-separated list of simple variables and record/row fields. Note that this is quite different from PostgreSQL's normal interpretation of SELECT INTO, which is that the INTO target is a newly created table. (If you want to create a table from a SELECT result inside a PL/pgSQL function, use the syntax CREATE TABLE ... AS SELECT.)
If a row or a variable list is used as target, the selected values must exactly match the structure of the target(s), or a runtime error occurs. When a record variable is the target, it automatically configures itself to the rowtype of the query result columns.
Except for the INTO clause, the SELECT statement is the same as a normal SQL SELECT query and can use the full power of SELECT.
If the SELECT query returns zero rows, NULLs are assigned to the target(s). If the SELECT query returns multiple rows, the first row is assigned to the target(s) and the rest are discarded. (Note that "the first row" is not well-defined unless you've used ORDER BY.)
At present, the INTO clause can appear almost anywhere in the SELECT query, but it is recommended to place it immediately after the SELECT keyword as depicted above. Future versions of PL/pgSQL may be less forgiving about placement of the INTO clause.
There is a special variable named FOUND of type boolean that can be used immediately after a SELECT INTO to check if an assignment had success (that is, at least one row was returned by the SELECT). For example,
SELECT INTO myrec * FROM EMP WHERE empname = myname;
IF NOT FOUND THEN
RAISE EXCEPTION ''employee % not found'', myname;
END IF;Alternatively, you can use the IS NULL (or ISNULL) conditional to test for NULLity of a RECORD/ROW result. Note that there is no way to tell whether any additional rows might have been discarded.
DECLARE
users_rec RECORD;
full_name varchar;
BEGIN
SELECT INTO users_rec * FROM users WHERE user_id=3;
IF users_rec.homepage IS NULL THEN
-- user entered no homepage, return "http://"
RETURN ''http://'';
END IF;
END;
Sometimes one wishes to evaluate an expression or query but discard the result (typically because one is calling a function that has useful side-effects but no useful result value). To do this in PL/pgSQL, use the PERFORM statement:
PERFORM query;
This executes a SELECT query and discards the result. PL/pgSQL variables are substituted into the query as usual.
Note: One might expect that SELECT with no INTO clause would accomplish this result, but at present the only accepted way to do it is PERFORM.
An example:
PERFORM create_mv(''cs_session_page_requests_mv'',''
SELECT session_id, page_id, count(*) AS n_hits,
sum(dwell_time) AS dwell_time, count(dwell_time) AS dwell_count
FROM cs_fact_table
GROUP BY session_id, page_id '');
Oftentimes you will want to generate dynamic queries inside your PL/pgSQL functions, that is, queries that will involve different tables or different datatypes each time they are executed. PL/pgSQL's normal attempts to cache plans for queries will not work in such scenarios. To handle this sort of problem, the EXECUTE statement is provided:
EXECUTE query-string;
where query-string is an expression yielding a string (of type text) containing the query to be executed. This string is fed literally to the SQL engine.
Note in particular ustar root root
UPDATE [ ONLY ] table SET col = expression [, ...]
[ FROM fromlist ]
[ WHERE condition ]
The name of an existing table.
The name of a column in table.
A valid expression or value to assign to column.
A PostgreSQL non-standard extension to allow columns from other tables to appear in the WHERE condition.
Refer to the SELECT statement for a further description of the WHERE clause.
UPDATE changes the values of the columns specified for all rows which satisfy condition. Only the columns to be modified need appear as columns in the statement.
Array references use the same syntax found in SELECT. That is, either single array elements, a range of array elements or the entire array may be replaced with a single query.
You must have write access to the table in order to modify it, as well as read access to any table whose values are mentioned in the WHERE condition.
By default UPDATE will update tuples in the table specified and all its sub-tables. If you wish to only update the specific table mentioned, you should use the ONLY clause.
Change word Drama with Dramatic on column kind:
UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama'; SELECT * FROM films WHERE kind = 'Dramatic' OR kind = 'Drama'; code | title | did | date_prod | kind | len -------+---------------+-----+------------+----------+------- BL101 | The Third Man | 101 | 1949-12-23 | Dramatic | 01:44 P_302 | Becket | 103 | 1964-02-03 | Dramatic | 02:28 M_401 | War and Peace | 104 | 1967-02-12 | Dramatic | 05:57 T_601 | Yojimbo | 106 | 1961-06-16 | Dramatic | 01:50 DA101 | Das Boot | 110 | 1981-11-11 | Dramatic | 02:29