![]() ![]() 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.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |