![]() ![]() ![]() So there is no way to extract a result from a dynamically-created SELECT using the plain EXECUTE command. The results from SELECT commands are discarded by EXECUTE, and SELECT INTO is not currently supported within EXECUTE. The command string can be dynamically created within the function to perform actions on different tables and columns. Instead, the command is prepared each time the statement is run. 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. The values of variables must be inserted in the command string as it is constructed. Note in particular that no substitution of PL/pgSQL variables is done on the command string. This string is fed literally to the SQL engine. Where command-string is an expression yielding a string (of type text) containing the command to be executed. To handle this sort of problem, the EXECUTE statement is provided: EXECUTE command-string PL/pgSQL's normal attempts to cache plans for commands will not work in such scenarios. ![]() 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. 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 " RETURN ' END IF END There is, however, no way to tell whether any additional rows might have been discarded. To test for whether a record/row result is null, you can use the IS NULL conditional. For example: SELECT INTO myrec * FROM emp WHERE empname = myname IF NOT FOUND THEN RAISE EXCEPTION 'employee % not found', myname END IF 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. (Note that "the first row" is not well-defined unless you've used ORDER BY.) If the query returns multiple rows, the first row is assigned to the target(s) and the rest are discarded. If the query returns zero rows, null values are assigned to the target(s). 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. ![]() The INTO clause can appear almost anywhere in the SELECT statement. When a record variable is the target, it automatically configures itself to the row type of the query result columns.Įxcept for the INTO clause, the SELECT statement is the same as a normal SQL SELECT command and can use its full power. 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. If you want to create a table from a SELECT result inside a PL/pgSQL function, use the syntax CREATE TABLE. Note that this is quite different from PostgreSQL's normal interpretation of SELECT INTO, where the INTO target is a newly created table. The select_expressions and the remainder of the command are the same as in regular SQL. Where target can be a record variable, a row variable, or a comma-separated list of simple variables and record/row fields. This is done by: SELECT INTO target select_expressions FROM. 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. ![]()
0 Comments
Leave a Reply. |