Creating a Function

1. In the navigation panel, go to Databases.

2. (Optional) To display system databases, turn on Show system databases.

3. Click the name of the database.

4. Click the name of the schema.

5. Select Functions.

6. In the top-right corner of the page, click Create function.

7. Enter 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:

     1. Click Add argument +.

     2. Enter 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:

     1. Click Add return value +.

     2. Enter 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.

       Note

       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().

       Note

       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.

8. Click Create.

Viewing Functions

1. In the navigation panel, go to Databases.

2. (Optional) To display system databases, turn on Show system databases.

3. Click the name of the database.

4. Click the name of the schema.

5. Select Functions.

Editing a Function

1. In the navigation panel, go to Databases.

2. (Optional) To display system databases, turn on Show system databases.

3. Click the name of the database.

4. Click the name of the schema.

5. Select Functions.

6. Click next to the function.

7. Edit function parameters.

8. Click Save.

Deleting a Function

To delete a function:

1. In the navigation panel, go to Databases.

2. (Optional) To display system databases, turn on Show system databases.

3. Click the name of the database.

4. Click the name of the schema.

5. Select Functions.

6. Click next to the function.

7. Click Delete.