Write the query the same way you would write an SQL SELECT command, but replace the initial keyword SELECT with PERFORM.

This executes query and discards the result. To do this in PL/pgSQL, use the PERFORM statement: 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 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.ĮXECUTE 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. So automatic substitution of PL/pgSQL variables does not work in such commands. Non-optimizable SQL commands (also called utility commands) are not capable of accepting query parameters. 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. This is exactly like the processing described earlier for expressions for details see Section 43.11.1. 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. 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. Again, there are two ways to proceed depending on the situation. 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. Usually it is not sufficient just to execute statically-defined SQL commands.

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. 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. If the command does return rows (for example SELECT, or INSERT/ UPDATE/ DELETE with RETURNING), there are two ways to proceed. INSERT INTO mytable VALUES (1,'one'), (2,'two') For example, you could create and fill a table by writingĬREATE TABLE mytable (id int primary key, data text) In general, any SQL command that does not return rows can be executed within a PL/pgSQL function just by writing the command. 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. 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. 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). Equal ( =) can be used instead of PL/SQL-compliant :=. 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. The expression must yield a single value (possibly a row value, if the variable is a row or record variable). An assignment of a value to a PL/pgSQL variable is written as: variable expression Īs explained previously, the expression in such a statement is evaluated by means of an SQL SELECT command sent to the main database engine.

Postgresql commands 9.5