Tutorial 5 - Create SQL function using wizard

Step 0 - Start Wizard

This tutorial show, how to create new function using wizard in KPoGre. Wizard is not able to handle all possible requirements, but it is suitable for most of common situations. I expect, that you are basicaly familiar with CREATE FUNCTION statement.

In left tree view, select Database or Schema and choose menu command Database -> Wizard. Select function icon from displayied dialog.

Or select Functions folder and make right button click on it to display object menu.

KPoGre displays wizard for create new function. This wizard have 7 steps.

In this tutorial we create function in SQL language. This function return rows from table. Imagine, that we have following table named employee:

We create function, that return eployee's with given first_name.

Step 1 - Function name

If you start this wizard when Database is selected, choose schema for new function. When you run wizard from Schema or Tables folder, actual schema is used.

Type name for new function. Select language.

Press Next button.

Step 2 - Return and argument types

Choose Return type. You may choose any basic data type, or OPAQUE or any composite or base type. In this sample, we choose employee type - this is based on employee table. Check Return a set, because function will return set of employee table rows.

Choose function Arguments. In this sample, we add varchar(25) - as first name.

Press Next button.

Step 3 - Function properties

In this step, choose function properties.

Type

These attributes inform the system whether it is safe to replace multiple evaluations of the function with a single evaluation, for run-time optimization. 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.

NULL arguments behavior

• Called on NULL input (the default) indicates that the function will be called normally when some of its arguments are null. It is then the function author's responsibility to check for null values if necessary and respond appropriately.
• Strict 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.

Security

• Security invoker indicates that the function is to be executed with the privileges of the user that calls it. That is the default.
• Security definer specifies that the function is to be executed with the privileges of the user that created it. Note that you should be careful when creating function with SECURITY DEFINER option. This is potentially security hole.

After defining all properties press Next button.

Step 4 - Function definition

Enter function code here. You must be familiar with choosed language.

Step 5 - Grant execute privileges

This step allow grant EXECUTE privilege on function to one or more users or groups of users. These privileges are added to those already granted, if any.

Select users or groups (multiple select allowed) or check PUBLIC for grant to all users.

Step 6 - Review SQL statement

In this step, SQL statement for create function is displayied for your check. You may modify it, if you understand, what do you do.

When you press Next button command will be send to server.

Step 7 - Check operation result

Finally, check operation result. If you get any error, you may go Back and try make changes and retry step 6.

Press Finish button and test newly created function.