3.9. Functions

Functions are objects that 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.

12345678910111213

Attributes
#	Field	Group box	Description
1	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 is also possible to use the user-defined languages of the model.
2	Security		Indicates the security context in which the function is executed. Two values are accepted: SECURITY INVOKER and SECURITY DEFINER. The first one indicates that the function is executed with the privileges of the user that calls it. The second one indicates that the function is executed with the privileges of the user that owns (defined) it.
3	Return method		Defines the returning mode of the function. Three modes are accepted: Simple, Set, and Table. Simple indicates that the function returns a single value of the type specified in the Return type field. Set indicates that the function returns a set of values of that type. Table indicates that the function returns a table with different columns and data types instead of a simple value; in this mode, the columns and their types are configured in the list Return table.
4	Function type	Settings	Indicates the volatility of the function. This attribute informs the PostgreSQL query optimizer about the behavior of the function. Three values are accepted: VOLATILE, STABLE, and IMMUTABLE. VOLATILE indicates that the function's result can change even within a single table scan, so no optimizations can be made. STABLE indicates that the function cannot modify the database and that, within a single table scan, it returns the same result for the same argument values. IMMUTABLE indicates that the function cannot modify the database and always returns the same result when called with the same arguments.
5	Parallelism type	Settings	Defines the parallelism settings of the function. The possible values are PARALLEL UNSAFE, PARALLEL SAFE, and 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 only by the parallel group leader. PARALLEL SAFE indicates that the function is safe to run in parallel mode without restrictions.
6	Execution cost	Settings	Specifies an estimated execution cost of the function (COST), which the PostgreSQL query planner can use.
7	Behavior type	Settings	Indicates the behavior when the function is called with null arguments. Two values are accepted: CALLED ON NULL INPUT and RETURNS NULL ON NULL INPUT. The first one indicates that the function is called normally even if there is a null argument. The second one indicates that the function always returns null when any argument is null: in practice, the function is not executed and a null result is returned automatically.
8	Rows returned	Settings	Specifies the estimated number of rows returned by the function, which the PostgreSQL query planner can expect. It is used (ROWS) only for functions that return a set of results.
9	Leakproof	Settings	Indicates that the function has no side effects and reveals no information about its arguments other than its return value.
10	Window	Settings	Marks the function as a window function (WINDOW). A window function acts like an aggregate by processing a set of rows of a table, but, unlike aggregates, the rows are not grouped in a single result: they retain their separate identity. More details about window functions can be found in the PostgreSQL documentation.
11	Type	Return type	The data type. Besides the built-in types, the list also holds the user-defined types, domains and other types of the model.
12	Dimension	Return type	Number of dimensions when the type is an array (type[]). 0 means that the type is not an array.
13	Format	Return type	Read-only preview of the data type as it is written in the SQL code, with length, precision, dimension and other modifiers applied.

1234

Transform types
#	Field	Group box	Description
1	Type	Data type	The data type. Besides the built-in types, the list also holds the user-defined types, domains and other types of the model.
2	Dimension	Data type	Number of dimensions when the type is an array (type[]). 0 means that the type is not an array.
3	Format	Data type	Read-only preview of the data type as it is written in the SQL code, with length, precision, dimension and other modifiers applied.
4	Transform types		The transforms that a call to the routine should apply. Select the type in the group above and use the add button to include it in the list.

1

Definition
#	Field	Description
1	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: only the language selected in the field Language can be used. When the language is c, this field is replaced by the fields Library and Symbol.

1

Configuration
#	Field	Description
1	Configuration parameters	Runtime configuration parameters of the routine (SET configuration_parameter = value). Type the parameter and the value, then use the add button to include them in the list.

When the return method is Table, the tab Attributes also shows a list with the set of columns returned by the function. The user is asked to configure a new column every time the add button is activated; 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.

The fields of the tab Definition vary according to the selected language. For the c language, the fields Symbol (the function name that defines the function in a shared library) and Library (the path to the shared object file on the destination server) are shown instead of the source code field.

Parameters

Parameters are defined by a separate 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.

1

Parameters
#	Field	Description
1	Parameters	The parameters of the function. Each one is configured in the parameter form described in the next section.

Each parameter is configured in the following form:

12345678

Parameter
#	Field	Group box	Description
1	Name		The name of the object. It follows the PostgreSQL rules for identifiers (up to 63 bytes in a default installation). Leaving it empty raises an error when the form is applied.
2	Default value		The default value of the parameter, used when the argument is omitted in the call.
3	IN	Mode	The parameter is an input one (the default). It can be combined with OUT to create a parameter that is both input and output.
4	OUT	Mode	The parameter is used as a result of the function. It can be combined with IN.
5	VARIADIC	Mode	The function accepts a variable number of arguments of the parameter's type, received as an array. It must be the last parameter. This option is exclusive with IN and OUT: it is enabled only when both are unchecked and, when checked, they are disabled.
6	Type	Data type	The data type. Besides the built-in types, the list also holds the user-defined types, domains and other types of the model.
7	Dimension	Data type	Number of dimensions when the type is an array (type[]). 0 means that the type is not an array.
8	Format	Data type	Read-only preview of the data type as it is written in the SQL code, with length, precision, dimension and other modifiers applied.

References

Function DDL
https://www.postgresql.org/docs/current/static/sql-createfunction.html

Window functions
https://www.postgresql.org/docs/current/static/tutorial-window.html

Function parameters
https://www.postgresql.org/docs/current/static/xfunc-sql.html

Runtime settings
https://www.postgresql.org/docs/current/runtime-config.html