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 FUNCTIONexternal link statement.

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

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

Starting wizard

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:

Employee table

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.

Function name

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.

Function return and argument types

Press Next button.

Step 3 - Function properties

In this step, choose function properties.


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

Function properties

After defining all properties press Next button.

Step 4 - Function definition

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

Function definition

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.

Define execute privileges

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.

Review SQL statement

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.

Check operation result

Press Finish button and test newly created function.

Test function

SourceForge Logo Valid XHTML 1.0!