Online Documentation for SQL Manager for PostgreSQL

Creating/editing function


Use the Function tab of Function Editor to create/edit a function and specify its definition.

 

Name

Select a schema and enter a name for the new function, or modify the name of the function being edited.

 

Function Editor - Editing function definition

 

Returns

Use this options to define what data is to return by the function:

RadioButton Single value - the function returns a single item; to define its type use the drop-down list.

RadioButton Set of values - the function returns a set of items; define its type using the drop-down list.

RadioButton Table -  the function returns table; define its type using the Return table column (for Postgres 9.1 and higher).

RadioButton Nothing - the function returns void.

RadioButton Trigger - the function returns a trigger.

 

The Arguments list provides the following attributes of each argument used in the function:

Name

Type (the argument data type)

Mode (in, out, in/out)

Default value

 

Set the function arguments by right-clicking in the list and selecting iconPlus Add Argument. Once the argument is added, select its type from the Argument Type drop-down list. To delete the existing argument, select iconMinus Delete Argument from the popup menu. To reorder the arguments within the list, use the btnArrowUp Move Up and btnArrowDown Move Down items or the corresponding Ctrl+Up/Ctrl+Down shortcuts.

It is possible to set the argument names for server version 8.0 and higher.

Hint: You can reorder arguments in the list using the Move Up and Move Down popup menu items.

 

Language

Select the language that the function is implemented in: SQL, C, internal, or the name of a user-defined procedural language.

 

CheckBox Returns NULL on NULL input

This option indicates that the function always returns null whenever any of its arguments are null. If this parameter is specified, the function is not executed when there are null arguments; instead a null result is assumed automatically.

 

CheckBox Execute with definer's privileges

This option specifies that the function is to be executed with the privileges of the user that created it. Otherwise, the function is to be executed with the privileges of the user that calls it.

 

CheckBox Is window function

This option indicates that the function is a window function rather than a plain function. A window function performs a calculation across a set of table rows that are somehow related to the current row.

 

CheckBox Is leakproof function

This option indicates that the function has no side effects. It reveals no information about its arguments other than by its return value. For example, a function which throws an error message for some argument values but not others, or which includes the argument values in any error message, is not leakproof.

 

Optimization

Select the behaviour of the function:

IMMUTABLE indicates that the function cannot modify the database and always returns the same result when given the same argument values.

STABLE indicates that the function cannot modify the database, and that within a single table scan it will consistently return the same result for the same argument values, but that its result could change across SQL statements.

VOLATILE indicates that the function value can change even within a single table scan, so no optimizations can be made.

 

Parallel

Set whether the function can be executed in the parallel mode:

UNSAFE value prohibits parallel mode.

RESTRICTED indicates that the function can be executed in parallel mode, but the execution is restricted to parallel group leader.

SAFE value indicates that the function can be run in parallel mode.

 

Planner options

This group is available for server version 8.3 and higher. The spinner controls allow you to specify Estimated execution cost and Estimated number of rows.

 

Definition

This area allows you to set the function definition (body).

 

For your convenience the syntax highlight, code folding, using macro and a number of other features for efficient SQL editing are implemented. For details see Working with Query Data area, Using the context menu and .

 

The possibility to use macros is also implemented.

To start recording a macro, click the btnRecordMacro Record button available in the status bar area, or use the Shift+Ctrl+R shortcut.

To stop recording, click the btnStopRecordMacro Stop button, or use the Shift+Ctrl+R shortcut.

To call the recorded macro, use the btnPlayMacro Play button, or use the Shift+Ctrl+P shortcut.

 

To execute the function, you can use the btnExecuteQuery Execute item available within the Navigation bar or toolbar.