This will work whether or not the particular argument was declared with a name. In the older numeric approach, arguments are referenced using the syntax $ n: $1 refers to the first input argument, $2 to the second, and so on. You can avoid the ambiguity by choosing a different alias for the table within the SQL command.) (If this would conflict with a qualified column name, again the column name wins. To override this, qualify the argument name with the name of the function itself, that is function_name. If the argument name is the same as any column name in the current SQL command within the function, the column name will take precedence. To use a name, declare the function argument as having a name, and then just write that name in the function body. Also, procedures are SQL standard whereas returning void is a PostgreSQL extension.Īrguments of an SQL function can be referenced in the function body using either names or numbers. However, procedures offer additional functionality such as transaction control that is not available in functions. In simple cases like this, the difference between a function returning void and a procedure is mostly stylistic. You can also write this as a procedure, thus avoiding the issue of the return type. For example, this function removes rows with negative salaries from the emp table:ĬREATE FUNCTION clean_emp() RETURNS void AS ' Alternatively, if you want to define an SQL function that performs actions but has no useful value to return, you can define it as returning void. (You cannot use transaction control commands, e.g., COMMIT, SAVEPOINT, and some utility commands, e.g., VACUUM, in SQL functions.) However, the final command must be a SELECT or have a RETURNING clause that returns whatever is specified as the function's return type. Besides SELECT queries, the commands can include data modification queries ( INSERT, UPDATE, DELETE, and MERGE), as well as other SQL commands. Unless the function is declared to return void, the last statement must be a SELECT, or an INSERT, UPDATE, or DELETE that has a RETURNING clause.Īny collection of commands in the SQL language can be packaged together and defined as a function. A semicolon after the last statement is optional. The body of an SQL function must be a list of SQL statements separated by semicolons. In this case all rows of the last query's result are returned. (Bear in mind that “ the first row” of a multirow result is not well-defined unless you use ORDER BY.) If the last query happens to return no rows at all, the null value will be returned.Īlternatively, an SQL function can be declared to return a set (that is, multiple rows) by specifying the function's return type as SETOF sometype, or equivalently by declaring it as RETURNS TABLE( columns). In the simple (non-set) case, the first row of the last query's result will be returned. SQL functions execute an arbitrary list of SQL statements, returning the result of the last query in the list. SQL Functions with Default Values for Arguments 38.5.8. SQL Functions with Variable Numbers of Arguments 38.5.7. SQL Procedures with Output Parameters 38.5.6. SQL Functions with Output Parameters 38.5.5.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |