If I call it with a null value, it doesn't update, there is no error message and no value is returned:. If this parameter is specified, the function is not executed when there are null arguments; instead a null result is assumed automatically.
You can also update the function to default to a value if null is supplied to allow for optional parameters. How are we doing? Please help us improve Stack Overflow. Take our short survey. Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. Asked 5 years, 11 months ago. Active 1 year, 8 months ago.
Viewed 14k times. Its data type is the actual return type of the function, as deduced from the actual input types see Section This allows the function to access its actual return type as shown in Section The same effect can be obtained by declaring one or more output parameters as polymorphic types. For example:.
The ALIAS syntax is more general than is suggested in the previous section: you can declare an alias for any variable, not just function parameters. The main practical use for this is to assign a different name for variables with predetermined names, such as NEW or OLD within a trigger procedure. It's best to use it only for the purpose of overriding predetermined names.
You can use this to declare variables that will hold database values. To declare a variable with the same data type as users. A variable of a composite type is called a row variable or row-type variable. The individual fields of the row value are accessed using the usual dot notation, for example rowvar. Parameters to a function can be composite types complete table rows.
Only the user-defined columns of a table row are accessible in a row-type variable, not the OID or other system columns because the row could be from a view. The fields of the row type inherit the table's field size or precision for data types such as char n. Here is an example of using composite types. Record variables are similar to row-type variables, but they have no predefined structure. In any case, the name of an output argument is significant, because it defines the column name in the result row type.
If you omit the name for an output argument, the system will choose a default column name. The data type s of the function's arguments optionally schema-qualified , if any. The argument types can be base, composite, or domain types, or can reference the type of a table column. Pseudo-types indicate that the actual argument type is either incompletely specified, or outside the set of ordinary SQL data types. Using this feature can sometimes help make a function independent of changes to the definition of a table.
An expression to be used as default value if the parameter is not specified. The expression has to be coercible to the argument type of the parameter. All input parameters following a parameter with a default value must have default values as well. The return data type optionally schema-qualified.
The return type can be a base, composite, or domain type, or can reference the type of a table column. If the function is not supposed to return a value, specify void as the return type.
If present, it must agree with the result type implied by the output parameters: RECORD if there are multiple output parameters, or the same type as the single output parameter.
The SETOF modifier indicates that the function will return a set of items, rather than a single item. The name of the language that the function is implemented in. It can be sql , c , internal , or the name of a user-defined procedural language, e. Enclosing the name in single quotes is deprecated and requires matching case. Lists which transforms a call to the function should apply.
Procedural language implementations usually have hardcoded knowledge of the built-in types, so those don't need to be listed here. If a procedural language implementation does not know how to handle a type and no transform is supplied, it will fall back to a default behavior for converting data types, but this depends on the implementation. This is currently only useful for functions written in C. These attributes inform the query optimizer about the behavior of the function.
At most one choice can be specified. IMMUTABLE indicates that the function cannot modify the database and always returns the same result when given the same argument values; that is, it does not do database lookups or otherwise use information not directly present in its argument list. If this option is given, any call of the function with all-constant arguments can be immediately replaced with the function value.
STABLE indicates that the function cannot modify the database, and that within a single table scan it will consistently return the same result for the same argument values, but that its result could change across SQL statements.
This is the appropriate selection for functions whose results depend on database lookups, parameter variables such as the current time zone , etc. Relatively few database functions are volatile in this sense; some examples are random , currval , timeofday. But note that any function that has side-effects must be classified volatile, even if its result is quite predictable, to prevent calls from being optimized away; an example is setval.
For additional details see Section It reveals no information about its arguments other than by its return value. For example, a function which throws an error message for some argument values but not others, or which includes the argument values in any error message, is not leakproof. The system will enforce conditions from security policies and security barrier views before any user-supplied conditions from the query itself that contain non-leakproof functions, in order to prevent the inadvertent exposure of data.
Functions and operators marked as leakproof are assumed to be trustworthy, and may be executed before conditions from security policies and security barrier views. In addition, functions which do not take arguments or which are not passed any arguments from the security barrier view or table do not have to be marked as leakproof to be executed before security conditions.
This option can only be set by the superuser. It is then the function author's responsibility to check for null values if necessary and respond appropriately. If this parameter is specified, the function is not executed when there are null arguments; instead a null result is assumed automatically.
That is the default. This is the default.
0コメント