Creating and Editing Functions
Go Up to Creating and Editing Database Dependent Objects
Contents
Functions are subroutines that you define. Functions can be written in a programming language such as C, COBOL, or Java that returns a scalar value or a complete table, or they can call another function, or they can be written in SQL and return a scalar value. Functions are useful for reusable application logic. You can use functions to determine the best methods for controlling access and manipulation of the underlying data contained in an object. Functions accept a number of parameters and pass a single value back to the calling program. Functions can be used in the database to check the validity of the data being entered. For example, functions can be used to validate zip codes. By invoking a routine with the zip code, the function can return a true or false value based on if the zip code is valid.
You can create a reusable function Function SQL Editor where you can create a function and bind attachments to it. You can also add a Function through the Table Editor - Dependencies tab. They are not associated with a particular table; they are stored in the database.
The following database platforms support SQL functions:
- Amazon Redshift
- IBM DB2 for LUW 5.x, 6.x, 7.x, 8.x, 9.x, 10.x
- Microsoft SQL Server 2000 - 2017
- Oracle 7.x, 8.x, 9i, 10g, 11g, 12c
- PostgreSQL 10.x, 11.x, 12.x
- Snowflake
- Sybase ASA 6.0, 7.0, 8.0, 9.0, 10.0, Adaptive Server IQ 12, 15, 16
Create a Stored SQL Function
- In the Data Model Explorer, expand the Physical model, right-click the Functions node, and then click New Function.
- Complete the Function SQL Editor and then click OK to create the function.
- Once you have created the function, you can edit it by right-clicking the function you want to change, and then selecting Edit Function.
Description tab
Enter or edit a definition for the function. If the target database supports it, ER/Studio Data Architect adds this definition as a table comment when generating SQL code.
Permissions tab
Sets access roles and user permissions for the function. Keep in mind that the more specific permissions are, the more time you may have to spend maintaining and updating them. The Roles and Users must be previously assigned to appear in the Function Editor. For more information, see Creating and Editing Database Roles and Creating and Editing Database Users.
Attachment Bindings tab
Bind an external piece of information or attachment to the function. You can also remove an attachment from an object, override an attachment binding's default value, or change the position of a bound attachment. To override the value of the attachment you have moved to the Selected Attachments grid, double-click the Value field of the target attachment. ER/Studio Data Architect opens the Value Override Editor or a list depending on the attachment datatype. Attachments are created in the Attachments folder of the Data Dictionary. For more information, see Attaching External Documents to the Data Model.
Notes
- You can choose to launch your preferred SQL editor when editing functions and procedures. Using the Tools tab of the Options Editor, you can enter the name and location of the SQL editor that will launch and manage the SQL ER/Studio Data Architect outputs. Choose Tools > Options > Tools > ISQL Path .
- For all new models you can choose:
- To display the schema object description or its associated symbol, choose Tools > Options > Display and then select the desired display mode.
- To hide invalid schema objects, choose Tools > Options > Schema Objects and then select Hide Invalid Schema Object Bitmap.
- For the current data model, you can choose:
- To display the schema objects or objects relationships, and object definition or object symbol; choose View > Diagram And Object Display Options > Schema Object and then select the desired display options.
- What to display when you mouse over a schema object; choose Tools > Options > Diagram > Schema Object Display and choose to display the object description or its associated DDL.