3.9. Functions

v0.9.4 Summary Chapter 3: Database objects 3. Introduction 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`.

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 modes`IN` and `OUT` can be combined in order to create a parameter that serves as input and output.

3.9. Functions

3.9.1. Parameters