|PostgreSQL 8.0.26 Documentation|
|Prev||Fast Backward||Chapter 35. PL/pgSQL - SQL Procedural Language||Fast Forward||Next|
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 (after substitution of any PL/pgSQL variables used in the statement). Thus, for example, the SQL commands INSERT, UPDATE, and DELETE 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 (like 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 run-time errors generated by the input function, if the string form of the result value is not acceptable to the input function.
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, row-type variable, or list of scalar variables. This is done by:
SELECT INTO target select_expressions FROM ...;
where target can be a record variable, a row variable, or a comma-separated list of simple variables and record/row fields. The select_expressions and the remainder of the command are the same as in regular SQL.
Note that this is quite different from PostgreSQL's normal interpretation of SELECT INTO, where 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, or a run-time error occurs. When a record variable is the target, it automatically configures itself to the row type of the query result columns.
Except for the INTO clause, the SELECT statement is the same as a normal SQL SELECT command and can use its full power.
The INTO clause can appear almost anywhere in the SELECT statement. Customarily it is written either just after SELECT as shown above, or just before FROM — that is, either just before or just after the list of select_expressions.
If the query returns zero rows, null values are assigned to the target(s). If the 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.)
You can check the special FOUND variable (see Section 35.6.6) after a SELECT INTO statement to determine whether the assignment was successful, that is, at least one row was was returned by the query. For example:
SELECT INTO myrec * FROM emp WHERE empname = myname; IF NOT FOUND THEN RAISE EXCEPTION 'employee % not found', myname; END IF;
To test for whether a record/row result is null, you can use the IS NULL conditional. There is, however, no way to tell whether any additional rows might have been discarded. Here is an example that handles the case where no rows have been returned:
DECLARE users_rec RECORD; 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:
This executes query and discards the result. Write the query the same way as you would in an SQL SELECT command, but replace the initial keyword SELECT with PERFORM. PL/pgSQL variables will be substituted into the query as usual. Also, the special variable FOUND is set to true if the query produced at least one row or false if it produced no rows.
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.
PERFORM create_mv('cs_session_page_requests_mv', my_query);
Sometimes a placeholder statement that does nothing is useful. For example, it can indicate that one arm of an if/then/else chain is deliberately empty. For this purpose, use the NULL statement:
For example, the following two fragments of code are equivalent:
BEGIN y := x / 0; EXCEPTION WHEN division_by_zero THEN NULL; -- ignore the error END;
BEGIN y := x / 0; EXCEPTION WHEN division_by_zero THEN -- ignore the error END;
Which is preferable is a matter of taste.
Note: In Oracle's PL/SQL, empty statement lists are not allowed, and so NULL statements are required for situations such as this. PL/pgSQL allows you to just write nothing, instead.
Oftentimes you will want to generate dynamic commands inside your PL/pgSQL functions, that is, commands that will involve different tables or different data types each time they are executed. PL/pgSQL's normal attempts to cache plans for commands will not work in such scenarios. To handle this sort of problem, the EXECUTE statement is provided:
where command-string is an expression yielding a string (of type text) containing the command to be executed. This string is fed literally to the SQL engine.
Note in particular that no substitution of PL/pgSQL variables is done on the command string. The values of variables must be inserted in the command string as it is constructed.
Unlike all other commands in PL/pgSQL, a command run by an EXECUTE statement is not prepared and saved just once during the life of the session. Instead, the command is prepared each time the statement is run. The command string can be dynamically created within the function to perform actions on different tables and columns.
The results from SELECT commands are discarded by EXECUTE, and SELECT INTO is not currently supported within EXECUTE. So there is no way to extract a result from a dynamically-created SELECT using the plain EXECUTE command. There are two other ways to do it, however: one is to use the FOR-IN-EXECUTE loop form described in Section 35.7.4, and the other is to use a cursor with OPEN-FOR-EXECUTE, as described in Section 35.8.2.
When working with dynamic commands you will often have to handle escaping of single quotes. The recommended method for quoting fixed text in your function body is dollar quoting. (If you have legacy code that does not use dollar quoting, please refer to the overview in Section 35.2.1, which can save you some effort when translating said code to a more reasonable scheme.)
Dynamic values that are to be inserted into the constructed query require special handling since they might themselves contain quote characters. An example (this assumes that you are using dollar quoting for the function as a whole, so the quote marks need not be doubled):
EXECUTE 'UPDATE tbl SET ' || quote_ident(colname) || ' = ' || quote_literal(newvalue) || ' WHERE key = ' || quote_literal(keyvalue);
This example shows use of the functions
safety, variables containing column and table identifiers
should be passed to function
quote_ident. Variables containing values that
should be literal strings in the constructed command should be
take the appropriate steps to return the input text enclosed in
double or single quotes respectively, with any embedded special
characters properly escaped.
Note that dollar quoting is only useful for quoting fixed text. It would be a very bad idea to try to do the above example as
EXECUTE 'UPDATE tbl SET ' || quote_ident(colname) || ' = $$' || newvalue || '$$ WHERE key = ' || quote_literal(keyvalue);
because it would break if the contents of newvalue happened to contain $$. The same objection would apply to any other
dollar-quoting delimiter you might pick. So, to safely quote
text that is not known in advance, you must use
A much larger example of a dynamic command and EXECUTE can be seen in Example 35-6, which builds and executes a CREATE FUNCTION command to define a new function.
There are several ways to determine the effect of a command. The first method is to use the GET DIAGNOSTICS command, which has the form:
GET DIAGNOSTICS variable = item [ , ... ] ;
This command allows retrieval of system status indicators. Each item is a key word identifying a state value to be assigned to the specified variable (which should be of the right data type to receive it). The currently available status items are ROW_COUNT, the number of rows processed by the last SQL command sent down to the SQL engine, and RESULT_OID, the OID of the last row inserted by the most recent SQL command. Note that RESULT_OID is only useful after an INSERT command.
GET DIAGNOSTICS integer_var = ROW_COUNT;
The second method to determine the effects of a command is to check the special variable named FOUND, which is of type boolean. FOUND starts out false within each PL/pgSQL function call. It is set by each of the following types of statements:
A SELECT INTO statement sets FOUND true if it returns a row, false if no row is returned.
A PERFORM statement sets FOUND true if it produces (and discards) a row, false if no row is produced.
UPDATE, INSERT, and DELETE statements set FOUND true if at least one row is affected, false if no row is affected.
A FETCH statement sets FOUND true if it returns a row, false if no row is returned.
A FOR statement sets FOUND true if it iterates one or more times, else false. This applies to all three variants of the FOR statement (integer FOR loops, record-set FOR loops, and dynamic record-set FOR loops). FOUND is set this way when the FOR loop exits; inside the execution of the loop, FOUND is not modified by the FOR statement, although it may be changed by the execution of other statements within the loop body.
FOUND is a local variable within each PL/pgSQL function; so any changes to it affect only the current function.
Beware during an : EXECUTE command, if you are using things like
query_string='UPDATE customers set name=\''||var_name||'\';'
FOR record in EXECUTE query
You have to take care of propper quoting, in this example, of the var_name variable since it may be a text type in the actual customers table. I was bitten by this and was a bit hard to debug.
To store the results of a dynamic select querey, use 'CREATE [TEMP] TABLE '||table_name||' AS SELECT ...'. It's a way to "simulate" the not implemented "SELECT INTO" statement.
I don't know if I should really have to warn people of this, but beware EXECUTE in a SECURITY DEFINER function. ALWAYS use the quote_ident and quote_literal function on variables, or something as simple as the following function could be a disaster:
CREATE FUNCTION dosomething(a_var char(10)) RETURNS boolean AS $$
EXECUTE 'PERFORM * FROM ' ||
'WHERE somerow = 1;';
$$ LANGUAGE plpgsql SECURITY DEFINER;
if the definer is a a superuser, then:
SELECT dosomething('a_table; create user pflarr with createuser; perform * from a_table');
creates a new superuser with no password.
On PERFORM and FOUND - probably obvious, except when it it's not :), but PERFORM will itself set FOUND to true or false, just like a SELECT would - obviously. Maybe not so obvious is that, for example, doing "PERFORM function_with_a_select_inside();" will set FOUND depending on the number of rows returned by the function. Hence, in case of scalar functions, FOUND will be set to true no matter what the returned value is, or whatever the select inside the function returned. You cannot use FOUND 'outside the function' (nor ROW_COUNT) to check the status of the select 'inside it'.