The Stored Routine Editor is provided for altering stored procedures/functions. All the parameters of stored procedure are valid for stored function, so you can switch between them easily.

To open the the Stored Routine Editor, simply double-click a stored procedure or function on the diagram or select the Properties item from the context menu.

The Stored Routine Editor consists of several tabs, each of which will be described below.

General

This tab allows you to tune the basic properties of a stored procedure or function.

Routine Name

The name of the stored routine must be unique within a schema. To check your diagram for the uniqueness of names use the Check Diagram tool.

Routine Schema

A name of a database schema where the stored routine will be placed.

Routine Owner

A role which will be owner of the object, or a role which will execute CREATE script in case of emty input field.

Language Name

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

Comment

A native database comment for your routine.

Generate

Set this option off to exclude the routine from the default selection in the Database Generation and Database Modification tools.

Execution

Evaluation Type

This option informs the system if it is safe to replace multiple evaluations of the function with a single evaluation, for run-time optimization. At most one choice may be specified. If none of these appear, VOLATILE is the default assumption.

IMMUTABLE indicates that the function always returns the same result when given the same argument values.

STABLE indicates that within a single table scan the function 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.

Execution Cost

A positive number giving the estimated execution cost for the function, in units of cpu_operator_cost configuration parameter. If the function returns a set, this is the cost per returned row. If the cost is not specified, 1 unit is assumed for C-language and internal functions, and 100 units for functions in all other languages. Larger values cause the planner to try to avoid evaluating the function more often than necessary.

Result Rows

A positive number giving the estimated number of rows that the planner should expect the function to return. This is only allowed when the function is declared to return a set (see Parameters tab). If function return only one row value is ignored. The default assumption is 1000 rows.

Execute under DEFINER permissions

This option can be used to specify whether the routine should be executed using the permissions of the user who creates the routine or the user who invokes it. The default value is DEFINER. The creator or invoker must have permission to access the database with which the routine is associated.

On NULL Arguments behavior

Determines how the function works with null input values. Set RETURNS NULL ON NULL INPUT or STRICT if you want the function always returns null whenever any of its arguments are null.

Parameters

The Parameters tab allows you to choose the routine result type and determine the list of parameters.

Return type

It sets the type of variable being returned by the function. If there are at least two OUT parameters, then changes return type to RECORD.

Parameters

This grid contains the list of all procedure parameters. Buttons pane below the parameters list work in the same way as the Column Editor one.

Definition

The Definition tab allows you to edit routine text. It will be visible only if the Language Name is set to SQL or to one of the user-defined procedural languages, e.g. PlPgSQL.

Function Body Brackets

Dollar-Quoted string surrounding function body text, or quote character.

Routine Body Memo

This text editor represents the routine text, which appears between BEGIN and END keywords in CREATE FUNCTION statement.

Location

The Location tab used for dynamically loadable C language functions or functions which language is defined as internal. In other cases tab will be invisible.

Function File

Specifies the name of the file containing the dynamically loadable object.

Function Name (link symbol)

Specifies the function link symbol, i.e. the name of the function in the C language source code. If the link symbol is omitted, it is assumed to be the same as the name of the SQL function being defined.

Script

This tab allows you to set SQL statements, which will be executed before (use Begin tab) and after (use End tab) generation of the stored routine.

Preview

The Preview tab displays the SQL statement, which will be executed during the database generation. This statement is made up according to the changes you have made using previous tabs. Note, that the text within the editor is read-only.

Notes

The Note tab allows you to define a description and an annotation for the edited stored routine. This properties will not affect the physical database, but they can be useful for your diagram development.

Format

These options allow you to set the routine line and fill color for displaying on the diagram, different from the default colors.