3.9. Functions

Functions are objects which store a collection of commands (routines) that can be reused in different ways from within the database system itself to external languages or programs. In PostgreSQL, these powerful objects are also used to create other objects like operators, triggers, casts, conversions, user-defined languages, and many others.

Attribute Description
Language The language in which the function is written. There are four built-in languages available when a new model is created in pgModeler: c, internal, sql, and plpgsql. It's also possible to use user-defined languages in the function's definition.
Function Type Indicates the type of the function. This attribute informs to PostgreSQL query optimizer about the behavior of the function. Three values are accepted by this field: VOLATILE, STABLE, and IMMUTABLE. The first one, VOLATILE, tells that the function's return values can change even within a table scan thus no optimizations can be made. The second value, STABLE, indicates that the function cannot modify the database and that within a table scan it will return the same result for the same argument values. Finally, the IMMUTABLE indicates that the function cannot modify the database and always returns the same values when the same arguments are passed to it.
Security This attribute indicates the security of a function when called. Two values are accepted: SECURITY INVOKER and SECURITY DEFINER. The first one indicates that the function should be executed with the privileges of the caller user. The second value indicates that the function should be called with the definer's privileges.
Behavior Indicates the behavior when calling the function with null arguments. Three values are valid: CALLED ON NULL INPUT, RETURNS NULL ON NULL INPUT, and STRICT. The first indicates that function should be called normally even if there is some null argument. The second one indicates that the function will always return null when some argument is null. In practice, the function is not executed instead a null result is returned automatically. The last value is a synonym of RETURNS NULL ON NULL INPUT and is present only in conformance to PostgreSQL documentation.
Rows Returned Specifies an estimated row amount to be returned by the function that the PostgreSQL query planner can expect. This attribute is only allowed for functions that return a set of results.
Execution Cost Specifies an estimated execution cost of the function that the PostgreSQL query planner can expect.
Parallel Defines the parallelism settings for the function. The possible values are PARALLEL USAFE, PARALLEL SAFE, PARALLEL RESTRICTED. PARALLEL UNSAFE, the default value, indicates that the function can't be executed in parallel mode and the presence of such a function in an SQL statement forces a serial execution plan. PARALLEL RESTRICTED indicates that the function can be executed in parallel mode, but the execution is restricted to parallel group leader. PARALLEL SAFE indicates that the function is safe to run in parallel mode without restriction.
Window Marks the function as a window one. A window function acts like an aggregate by processing a set of rows in a table but there are some differences: unlike aggregates, the rows aren't grouped in a single result instead they retain their separated identity. You can get more details about window functions on PostgreSQL documentation.
Leakproof Indicates that the functions have no security leaks or side effects, that is, it reveals no information about its arguments other than its return value.
Return Method Defines the returning mode of the function. Three modes are accepted: Simple, Set, and Table. The first indicates that the function returns a simple value of the type specified in the Data Type field. The second tells that the function returns a set of values. The last one, Table, indicates that the function will return a table with different columns and data types instead of a simple value. This option will enable users to specify the column names and their data types through the Return Table widget.
Data Type Configures the data type returned by the function. This is used only for Simple and Set return modes.
Return Table Configures the set of columns returned by the function when the Table returning mode is checked. The user will be asked to configure a new column every time it activates the add button. Since the returned table has no complex syntax, demanding only a name and data type for each column, the parameter editing form is used instead of the column editing form.
Parameters This tab is used to configure all parameters available for the function. See the subsection Parameters to get details about this kind of object.
Transform types This tab lists which transforms a call to the function should apply.
Definition This tab is used to configure the function's core definition. The fields visible in this tab may vary according to the language selected. For c language two fields will be visible: Symbol and Library. For any other language, the field Source Code will be visible.
Symbol The symbol (function name) that defines the function in a shared library. Details about the symbol and C-based functions can be seen on PostgreSQL documentation.
Library The shared library file where the function can be found. The value used here must be the path to the shared object file on the destination server where the function will be created.
Source Code The complete set of commands that the function must execute. This field is language-dependent which means that you cannot mix commands from different languages other than the one defined on the field Language.
Configuration This tab is used to define runtime configuration parameters for the functions through SET configuration_parameter = value.

3.9.1. Parameters

Parameters are defined by a separated object and assigned to functions. This kind of object is quite similar to columns but some of the attributes present on the latter like NOT NULL and Sequence are absent and one additional attribute exists as detailed below.

Attribute Description
Mode Specifies the mode of the parameter. IN indicates that the parameter is an input one (the default). The OUT indicates that the parameter is used as the result of the function. The VARIADIC indicates that the function will accept a variable number of arguments. The modesIN and OUT can be combined in order to create a parameter that serves as input and output.

Jan 13, 2022 at 16:57