| 41.5. Basic Statements | ||||
|---|---|---|---|---|
| Prev | Up | Chapter 41. PL/pgSQL —SQL Procedural Language | Home | Next |
41.5. Basic Statements#
In this section and the following ones, we describe all the statement types that are explicitly understood byPL/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 inSection 41.5.2.
41.5.1. Assignment#
An assignment of a value to aPL/pgSQL variable is written as:
variable{ := | = }expression;
As explained previously, the expression in such a statement is evaluated by means of an SQLSELECT 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 (seeSection 10.4). If no assignment cast is known for the pair of data types involved, thePL/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.
Examples:
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;
41.5.2. Executing SQL Commands#
In general, any SQL command that does not return rows can be executed within aPL/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 exampleSELECT, orINSERT/UPDATE/DELETE/MERGE withRETURNING), 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 anINTO clause to capture the output, as described inSection 41.5.3. To process all of the output rows, write the command as the data source for aFOR loop, as described inSection 41.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 areSELECT,INSERT,UPDATE,DELETE,MERGE, and certain utility commands that incorporate one of these, such asEXPLAIN andCREATE TABLE ... AS SELECT. In these commands, anyPL/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 seeSection 41.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 inSection 41.11.2.
Non-optimizable SQL commands (also called utility commands) are not capable of accepting query parameters. So automatic substitution ofPL/pgSQL variables does not work in such commands. To include non-constant text in a utility command executed fromPL/pgSQL, you must build the utility command as a string and thenEXECUTE it, as discussed inSection 41.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 orSELECT query but discard the result, for example when calling a function that has side-effects but no useful result value. To do this inPL/pgSQL, use thePERFORM statement:
PERFORMquery; This executesquery and discards the result. Write thequery the same way you would write an SQLSELECT command, but replace the initial keywordSELECT withPERFORM. ForWITH queries, usePERFORM 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 variableFOUND is set to true if the query produced at least one row, or false if it produced no rows (seeSection 41.5.5).
Note
One might expect that writingSELECT directly would accomplish this result, but at present the only accepted way to do it isPERFORM. An SQL command that can return rows, such asSELECT, will be rejected as an error unless it has anINTO clause as discussed in the next section.
An example:
PERFORM create_mv('cs_session_page_requests_mv', my_query);41.5.3. Executing a Command with a Single-Row Result#
The result of an SQL command yielding a single row (possibly of multiple columns) can be assigned to a record variable, row-type variable, or list of scalar variables. This is done by writing the base SQL command and adding anINTO clause. For example,
SELECTselect_expressionsINTO [STRICT]targetFROM ...;INSERT ... RETURNINGexpressionsINTO [STRICT]target;UPDATE ... RETURNINGexpressionsINTO [STRICT]target;DELETE ... RETURNINGexpressionsINTO [STRICT]target;MERGE ... RETURNINGexpressionsINTO [STRICT]target;
wheretarget can be a record variable, a row variable, or a comma-separated list of simple variables and record/row fields.PL/pgSQL variables will be substituted into the rest of the command (that is, everything but theINTO clause) just as described above, and the plan is cached in the same way. This works forSELECT,INSERT/UPDATE/DELETE/MERGE withRETURNING, and certain utility commands that return row sets, such asEXPLAIN. Except for theINTO clause, the SQL command is the same as it would be written outsidePL/pgSQL.
Tip
Note that this interpretation ofSELECT withINTO is quite different fromPostgres Pro's regularSELECT INTO command, wherein theINTO target is a newly created table. If you want to create a table from aSELECT result inside aPL/pgSQL function, use the syntaxCREATE TABLE ... AS SELECT.
If a row variable or a variable list is used as target, the command's result columns must exactly match the structure of the target as to number and data types, or else a run-time error occurs. When a record variable is the target, it automatically configures itself to the row type of the command's result columns.
TheINTO clause can appear almost anywhere in the SQL command. Customarily it is written either just before or just after the list ofselect_expressions in aSELECT command, or at the end of the command for other command types. It is recommended that you follow this convention in case thePL/pgSQL parser becomes stricter in future versions.
IfSTRICT is not specified in theINTO clause, thentarget will be set to the first row returned by the command, or to nulls if the command returned no rows. (Note that“the first row” is not well-defined unless you've usedORDER BY.) Any result rows after the first row are discarded. You can check the specialFOUND variable (seeSection 41.5.5) to determine whether a row was returned:
SELECT * INTO myrec FROM emp WHERE empname = myname;IF NOT FOUND THEN RAISE EXCEPTION 'employee % not found', myname;END IF;
If theSTRICT option is specified, the command must return exactly one row or a run-time error will be reported, eitherNO_DATA_FOUND (no rows) orTOO_MANY_ROWS (more than one row). You can use an exception block if you wish to catch the error, for example:
BEGIN SELECT * INTO STRICT myrec FROM emp WHERE empname = myname; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE EXCEPTION 'employee % not found', myname; WHEN TOO_MANY_ROWS THEN RAISE EXCEPTION 'employee % not unique', myname;END;
Successful execution of a command withSTRICT always setsFOUND to true.
ForINSERT/UPDATE/DELETE/MERGE withRETURNING,PL/pgSQL reports an error for more than one returned row, even whenSTRICT is not specified. This is because there is no option such asORDER BY with which to determine which affected row should be returned.
Ifprint_strict_params is enabled for the function, then when an error is thrown because the requirements ofSTRICT are not met, theDETAIL part of the error message will include information about the parameters passed to the command. You can change theprint_strict_params setting for all functions by settingplpgsql.print_strict_params, though only subsequent function compilations will be affected. You can also enable it on a per-function basis by using a compiler option, for example:
CREATE FUNCTION get_userid(username text) RETURNS intAS $$#print_strict_params onDECLAREuserid int;BEGIN SELECT users.userid INTO STRICT userid FROM users WHERE users.username = get_userid.username; RETURN userid;END;$$ LANGUAGE plpgsql;
On failure, this function might produce an error message such as
ERROR: query returned no rowsDETAIL: parameters: username = 'nosuchuser'CONTEXT: PL/pgSQL function get_userid(text) line 6 at SQL statement
Note
TheSTRICT option matches the behavior of Oracle PL/SQL'sSELECT INTO and related statements.
41.5.4. Executing Dynamic Commands#
Oftentimes you will want to generate dynamic commands inside yourPL/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 (as discussed inSection 41.11.2) will not work in such scenarios. To handle this sort of problem, theEXECUTE statement is provided:
EXECUTEcommand-string[ INTO [STRICT]target] [ USINGexpression[, ...]];
wherecommand-string is an expression yielding a string (of typetext) containing the command to be executed. The optionaltarget is a record variable, a row variable, or a comma-separated list of simple variables and record/row fields, into which the results of the command will be stored. The optionalUSING expressions supply values to be inserted into the command.
No substitution ofPL/pgSQL variables is done on the computed command string. Any required variable values must be inserted in the command string as it is constructed; or you can use parameters as described below.
Also, there is no plan caching for commands executed viaEXECUTE. Instead, the command is always planned each time the statement is run. Thus the command string can be dynamically created within the function to perform actions on different tables and columns.
TheINTO clause specifies where the results of an SQL command returning rows should be assigned. If a row variable or variable list is provided, it must exactly match the structure of the command's results; if a record variable is provided, it will configure itself to match the result structure automatically. If multiple rows are returned, only the first will be assigned to theINTO variable(s). If no rows are returned, NULL is assigned to theINTO variable(s). If noINTO clause is specified, the command results are discarded.
If theSTRICT option is given, an error is reported unless the command produces exactly one row.
The command string can use parameter values, which are referenced in the command as$1,$2, etc. These symbols refer to values supplied in theUSING clause. This method is often preferable to inserting data values into the command string as text: it avoids run-time overhead of converting the values to text and back, and it is much less prone to SQL-injection attacks since there is no need for quoting or escaping. An example is:
EXECUTE 'SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= $2' INTO c USING checked_user, checked_date;
Note that parameter symbols can only be used for data values — if you want to use dynamically determined table or column names, you must insert them into the command string textually. For example, if the preceding query needed to be done against a dynamically selected table, you could do this:
EXECUTE 'SELECT count(*) FROM ' || quote_ident(tabname) || ' WHERE inserted_by = $1 AND inserted <= $2' INTO c USING checked_user, checked_date;
A cleaner approach is to useformat()'s%I specification to insert table or column names with automatic quoting:
EXECUTE format('SELECT count(*) FROM %I ' 'WHERE inserted_by = $1 AND inserted <= $2', tabname) INTO c USING checked_user, checked_date;(This example relies on the SQL rule that string literals separated by a newline are implicitly concatenated.)
Another restriction on parameter symbols is that they only work in optimizable SQL commands (SELECT,INSERT,UPDATE,DELETE,MERGE, and certain commands containing one of these). In other statement types (generically called utility statements), you must insert values textually even if they are just data values.
AnEXECUTE with a simple constant command string and someUSING parameters, as in the first example above, is functionally equivalent to just writing the command directly inPL/pgSQL and allowing replacement ofPL/pgSQL variables to happen automatically. The important difference is thatEXECUTE will re-plan the command on each execution, generating a plan that is specific to the current parameter values; whereasPL/pgSQL may otherwise create a generic plan and cache it for re-use. In situations where the best plan depends strongly on the parameter values, it can be helpful to useEXECUTE to positively ensure that a generic plan is not selected.
SELECT INTO is not currently supported withinEXECUTE; instead, execute a plainSELECT command and specifyINTO as part of theEXECUTE itself.
Note
ThePL/pgSQLEXECUTE statement is not related to theEXECUTE SQL statement supported by thePostgres Pro server. The server'sEXECUTE statement cannot be used directly withinPL/pgSQL functions (and is not needed).
Example 41.1. Quoting Values in Dynamic Queries
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 inSection 41.12.1, which can save you some effort when translating said code to a more reasonable scheme.)
Dynamic values require careful handling since they might contain quote characters. An example usingformat() (this assumes that you are dollar quoting the function body so quote marks need not be doubled):
EXECUTE format('UPDATE tbl SET %I = $1 ' 'WHERE key = $2', colname) USING newvalue, keyvalue;It is also possible to call the quoting functions directly:
EXECUTE 'UPDATE tbl SET ' || quote_ident(colname) || ' = ' || quote_literal(newvalue) || ' WHERE key = ' || quote_literal(keyvalue);
This example demonstrates the use of thequote_ident andquote_literal functions (seeSection 9.4). For safety, expressions containing column or table identifiers should be passed throughquote_ident before insertion in a dynamic query. Expressions containing values that should be literal strings in the constructed command should be passed throughquote_literal. These functions take the appropriate steps to return the input text enclosed in double or single quotes respectively, with any embedded special characters properly escaped.
Becausequote_literal is labeledSTRICT, it will always return null when called with a null argument. In the above example, ifnewvalue orkeyvalue were null, the entire dynamic query string would become null, leading to an error fromEXECUTE. You can avoid this problem by using thequote_nullable function, which works the same asquote_literal except that when called with a null argument it returns the stringNULL. For example,
EXECUTE 'UPDATE tbl SET ' || quote_ident(colname) || ' = ' || quote_nullable(newvalue) || ' WHERE key = ' || quote_nullable(keyvalue);
If you are dealing with values that might be null, you should usually usequote_nullable in place ofquote_literal.
As always, care must be taken to ensure that null values in a query do not deliver unintended results. For example theWHERE clause
'WHERE key = ' || quote_nullable(keyvalue)
will never succeed ifkeyvalue is null, because the result of using the equality operator= with a null operand is always null. If you wish null to work like an ordinary key value, you would need to rewrite the above as
'WHERE key IS NOT DISTINCT FROM ' || quote_nullable(keyvalue)
(At present,IS NOT DISTINCT FROM is handled much less efficiently than=, so don't do this unless you must. SeeSection 9.2 for more information on nulls andIS DISTINCT.)
Note that dollar quoting is only useful for quoting fixed text. It would be a very bad idea to try to write this example as:
EXECUTE 'UPDATE tbl SET ' || quote_ident(colname) || ' = $$' || newvalue || '$$ WHERE key = ' || quote_literal(keyvalue);
because it would break if the contents ofnewvalue 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, youmust usequote_literal,quote_nullable, orquote_ident, as appropriate.
Dynamic SQL statements can also be safely constructed using theformat function (seeSection 9.4.1). For example:
EXECUTE format('UPDATE tbl SET %I = %L ' 'WHERE key = %L', colname, newvalue, keyvalue);%I is equivalent toquote_ident, and%L is equivalent toquote_nullable. Theformat function can be used in conjunction with theUSING clause:
EXECUTE format('UPDATE tbl SET %I = $1 WHERE key = $2', colname) USING newvalue, keyvalue; This form is better because the variables are handled in their native data type format, rather than unconditionally converting them to text and quoting them via%L. It is also more efficient.
A much larger example of a dynamic command andEXECUTE can be seen inExample 41.10, which builds and executes aCREATE FUNCTION command to define a new function.
41.5.5. Obtaining the Result Status#
There are several ways to determine the effect of a command. The first method is to use theGET DIAGNOSTICS command, which has the form:
GET [ CURRENT] DIAGNOSTICSvariable{ = | := }item[ , ...];
This command allows retrieval of system status indicators.CURRENT is a noise word (but see alsoGET STACKED DIAGNOSTICS inSection 41.6.8.1). Eachitem is a key word identifying a status value to be assigned to the specifiedvariable (which should be of the right data type to receive it). The currently available status items are shown inTable 41.1. Colon-equal (:=) can be used instead of the SQL-standard= token. An example:
GET DIAGNOSTICS integer_var = ROW_COUNT;
Table 41.1. Available Diagnostics Items
| Name | Type | Description |
|---|---|---|
ROW_COUNT | bigint | the number of rows processed by the most recentSQL command |
PG_CONTEXT | text | line(s) of text describing the current call stack (seeSection 41.6.9) |
PG_ROUTINE_OID | oid | OID of the current function |
The second method to determine the effects of a command is to check the special variable namedFOUND, which is of typeboolean.FOUND starts out false within eachPL/pgSQL function call. It is set by each of the following types of statements:
A
SELECT INTOstatement setsFOUNDtrue if a row is assigned, false if no row is returned.A
PERFORMstatement setsFOUNDtrue if it produces (and discards) one or more rows, false if no row is produced.UPDATE,INSERT,DELETE, andMERGEstatements setFOUNDtrue if at least one row is affected, false if no row is affected.A
FETCHstatement setsFOUNDtrue if it returns a row, false if no row is returned.A
MOVEstatement setsFOUNDtrue if it successfully repositions the cursor, false otherwise.A
FORorFOREACHstatement setsFOUNDtrue if it iterates one or more times, else false.FOUNDis set this way when the loop exits; inside the execution of the loop,FOUNDis not modified by the loop statement, although it might be changed by the execution of other statements within the loop body.RETURN QUERYandRETURN QUERY EXECUTEstatements setFOUNDtrue if the query returns at least one row, false if no row is returned.
OtherPL/pgSQL statements do not change the state ofFOUND. Note in particular thatEXECUTE changes the output ofGET DIAGNOSTICS, but does not changeFOUND.
FOUND is a local variable within eachPL/pgSQL function; any changes to it affect only the current function.
41.5.6. Doing Nothing At All#
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 theNULL statement:
NULL;
For example, the following two fragments of code are equivalent:
BEGIN y := x / 0;EXCEPTION WHEN division_by_zero THEN NULL; -- ignore the errorEND;
BEGIN y := x / 0;EXCEPTION WHEN division_by_zero THEN -- ignore the errorEND;
Which is preferable is a matter of taste.
Note
In Oracle's PL/SQL, empty statement lists are not allowed, and soNULL statements arerequired for situations such as this.PL/pgSQL allows you to just write nothing, instead.