EPUB | CHM | PDF

Stored Routine Editor

Top Previous Next

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

editor-SP-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 empty 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

editor-SP-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.

Parallel Mode

UNSAFE indicates that the function can't be executed in parallel mode and the presence of such a function in an SQL statement forces a serial execution plan. This is the default.

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

SAFE indicates that the function is safe to run in parallel mode without restriction.

Functions should be labeled parallel unsafe if they modify any database state, or if they make changes to the transaction such as using sub-transactions, or if they access sequences or attempt to make persistent changes to settings (e.g. setval). They should be labeled as parallel restricted if they access temporary tables, client connection state, cursors, prepared statements, or miscellaneous backend-local state which the system cannot synchronize in parallel mode (e.g. setseed cannot be executed other than by the group leader because a change made by another process would not be reflected in the leader). In general, if a function is labeled as being safe when it is restricted or unsafe, or if it is labeled as being restricted when it is in fact unsafe, it may throw errors or produce wrong answers when used in a parallel query. C-language functions could in theory exhibit totally undefined behavior if mislabeled, since there is no way for the system to protect itself against arbitrary C code, but in most likely cases the result will be no worse than for any other function. If in doubt, functions should be labeled as UNSAFE, which is the default.

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.

Window Function

Indicates that the function is a window function rather than a plain function. This is currently only useful for functions written in C. The WINDOW attribute cannot be changed when replacing an existing function definition.

LeakProof Function

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. The query planner may push leakproof functions (but not others) into views created with the security_barrier option. This option can only be set by the superuser.

Parameters

editor-SP-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

editor-SP-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

editor-SP-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.