In the navigation panel, go to Databases.
(Optional) To display system databases, turn on Show system databases.
Click the name of the database.
Click the name of the schema.
Select Functions.
In the top-right corner of the page, click Create function.
Specify parameters of the new function (parameters marked with an asterisk are required):
Name.
Language: The procedural language of the function.
Arguments.
To add an argument:
Click Add argument +.
Specify parameters of the new argument (parameters marked with an asterisk are required):
Name.
Argument mode.
Possible values:
IN
OUT
INOUT
VARIADIC
Type: The argument data type.
Default value: The default value of the argument.
Function return table: Specifies whether the function returns a table.
If turn on this toggle, add a return value:
Click Add return value +.
Specify parameters of the new return value (parameters marked with an asterisk are required):
Name.
Type.
Return value type: The data type of the value that the function will return.
This parameter is available only if you turn off Function return table.
Function body: The body of the function in the selected procedural language.
Window: Specifies whether the function is a window function.
Optimizer: The attribute that informs the optimizer about the behavior of the function.
Possible values:
Default: The default value is Volatile.
Immutable: The function is immutable and it cannot modify the database and always returns the same result for specific arguments. It means that the function does not interact with the database and does not use the information that was not passed to the argument list.
If a function is immutable, any function call with constant arguments can be immediately replaced with the function value.
Stable: The function is stable and it cannot modify the database and always returns the same result for specific arguments within one table scan, but the result can differ for different SQL operators.
This is applicable to functions whose results depend on the
database contents and parameters, such as time zone. However,
this is not applicable to AFTER triggers
that are trying to read the rows changed by the current command.
The current_timestamp functions are
also considered stable, since their results do not change in the
transaction.
Volatile: The function is volatile and its result can change even within one table scan, so the function calls cannot be optimized.
Only a limited number of functions are volatile, for example,
random(), currval(),
and timeofday().
Any function that has side effects must be considered
volatile even if its results are predictable so that its calls
are not optimized. An example of such a function is
setval().
Strict: Specifies whether the function will
always return NULL if NULL is
passed in one of the arguments.
Leakproof: Specifies whether the function does not have any side effects and it does not reveal any information about its arguments and only returns the result.
Security: The privileges that will be used for calling and executing the function.
Possible values:
Default: The default value is Invoker.
Invoker: The function will be executed with the privileges of the user who called it.
Definer: The function will be executed with the privileges of the user who defined it.
Parallel: The parameters for calling the function in the parallel mode.
Possible values:
Default: The default value is Unsafe.
Unsafe: The function cannot be executed in the parallel mode and having such a function in an SQL operator will lead to selecting a sequential query plan.
Restricted: The function can be executed in the parallel mode, but only in the parallel group leader process.
Safe: The function can be safely executed in the parallel mode with no restrictions, including parallel worker processes.
lock_timeout, s: Abort any statement that waits longer than the specified time while attempting to acquire a lock on a table, index, row, or other database object. The time limit applies separately to each lock acquisition attempt.
For more information about this parameter, refer to the official Postgres Pro documentation.
Show SQL: Displays the SQL query for creating the function with the specified parameters.
Click Create.