3.9. Functions
Functions are objects which store a collection of SQL 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 possible to use user-defined languages in function's definition but this is an advanced topic and will not be covered by this document. |
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. |
Window Function |
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 has 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 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 user 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. |
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 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 a 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. |
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 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. User can combine IN and OUT to create a parameter that serves as input and output. |
** 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
Jan 3, 2020 at 12:33