Informix Guide to SQL: Syntax SQL Statements

Use the CREATE FUNCTION statement to create a user-defined function. With this statement, you can register an external function or write and register an SPL function.

Tip: If you are trying to create a function from text that is in a separate file, use the CREATE FUNCTION FROM statement.

Syntax

Usage

The database server supports user-defined functions written in the following languages:

• Stored Procedure Language (SPL functions)

An SPL function can return one or more values.

• One of the external languages (C or Java) that Dynamic Server supports (external functions)

An external function must return exactly one value.

For information on how the manual uses the terms UDR, function, and procedure as well as recommended usage, see Relationship Between Routines, Functions and Procedures and Using CREATE PROCEDURE Versus CREATE FUNCTION, respectively.

The entire length of a CREATE FUNCTION statement must be less than 64 kilobytes. This length is the literal length of the statement, including blank space and tabs.

You can use a CREATE FUNCTION statement only within a PREPARE statement. If you want to create a user-defined function for which the text is known at compile time, you must put the text in a file and specify this file with the CREATE FUNCTION FROM statement.

You must have the Resource privilege on a database to create a function within that database.

Before you can create an external function, you must also have the Usage privilege on the language in which you will write the function. For more information, see GRANT.

By default, the Usage privilege on SPL is granted to PUBLIC. You must also have at least the Resource privilege on a database to create an SPL function within that database.

The level of privilege necessary to execute a UDR depends on whether the UDR is created with the DBA keyword.

If you create a UDR with the DBA keyword, it is known as a DBA-privileged UDR. You need the DBA privilege to create or execute a DBA-privileged UDR.

If you do not use the DBA keyword, the UDR is known as an owner-privileged UDR.

If you create an owner-privileged UDR in an ANSI-compliant database, anyone can execute the UDR.

If you create an owner-privileged UDR in a database that is not ANSI compliant, the NODEFDAC environment variable prevents privileges on that UDR from being granted to PUBLIC. If this environment variable is set, the owner of a UDR must grant the Execute privilege for that UDR to other users.

If an external function has a negator function, you must grant the Execute privilege on both the external function and its negator function before users can execute the external function.

Because Dynamic Server offers routine overloading, you can define more than one function with the same name, but different parameter lists. You might want to overload functions in the following situations:

• You create a user-defined function with the same name as a built-in function (such as equal()) to process a new user-defined data type.

• You create type hierarchies, in which subtypes inherit data representation and functions from supertypes.
• You create distinct types, which are data types that have the same internal storage representation as an existing data type, but have different names and cannot be compared to the source type without casting. Distinct types inherit support functions from their source types.

For a brief description of the routine signature which uniquely identifies each user-defined function, see Routine Overloading and Naming UDRs with a Routine Signature.

You can specify a specific name for a user-defined function. A specific name is a name that is unique in the database. A specific name is useful when you are overloading a function.

The quoted string in the DOCUMENT clause provides a synopsis and description of the UDR. The string is stored in the sysprocbody system catalog table and is intended for the user of the UDR.

Anyone with access to the database can query the sysprocbody system catalog table to obtain a description of one or all of the UDRs stored in the database.

For example, the following query obtains a description of the SPL function update_by_pct, shown in SPL Functions:

SELECT data FROM sysprocbody b, sysprocedures p

WHERE b.procid = p.procid 
		--join between the two catalog tables

	AND p.procname = 'update_by_pct' 
		-- look for procedure named update_by_pct

	AND b.datakey  = 'D'	-- want user document;

The preceding query returns the following text:

USAGE: Update a price by a percentage
Enter an integer percentage from 1 - 100
and a part id number

A UDR or application program can query the system catalog tables to fetch the DOCUMENT clause and display it for a user.

You can use a DOCUMENT clause at the end of the CREATE FUNCTION statement, whether or not you use the END FUNCTION keywords.

The WITH LISTING IN clause specifies a filename where compile time warnings are sent. After you compile a UDR, this file holds one or more warning messages.

If you do not use the WITH LISTING IN clause, the compiler does not generate a list of warnings.

If you specify a filename but not a directory, this listing file is created in your home directory on the computer where the database resides. If you do not have a home directory on this computer, the file is created in the root directory (the directory named "/").

If you specify a filename but not a directory, this listing file is created in your current working directory if the database is on the local machine. Otherwise, the default directory is %INFORMIXDIR%\bin.

SPL functions are UDRs written in Stored Procedure Language (SPL) that return one or more values.

To write and register an SPL function, use a CREATE FUNCTION statement. Embed appropriate SQL and SPL statements between the CREATE FUNCTION and END FUNCTION keywords. You can also follow the function with the DOCUMENT and WITH FILE IN options.

SPL functions are parsed, optimized (as far as possible), and stored in the system catalog tables in executable format. The body of an SPL function is stored in the sysprocbody system catalog table. Other information about the function is stored in other system catalog tables, including sysprocedures, sysprocplan, and sysprocauth. For more information about these system catalog tables, see the discussion of the system catalog in the Informix Guide to SQL: Reference.

You must use the END FUNCTION keywords with an SPL function.

Place a semicolon after the clause that immediately precedes the statement block.

The following example creates an SPL function:

CREATE FUNCTION update_by_pct ( pct INT, pid CHAR(10))
	RETURNING INT;

	DEFINE n INT;

	UPDATE inventory SET price = price + price * (pct/100)
		WHERE part_id = pid;
	LET n = price;
	RETURN price;

END FUNCTION
	DOCUMENT "USAGE: Update a price by a percentage",
			"Enter an integer percentage from 1 - 100",
			"and a part id number"
	WITH LISTING IN '/tmp/warn_file'

For more information on writing SPL functions, see the Informix Guide to SQL: Tutorial.

External functions are functions you write in an external language that Dynamic Server supports.

1. Write the C function.
2. Compile the function and store the compiled code in a shared library (the shared-object file for C).
3. Register the function in the database server with the CREATE FUNCTION statement.

1. Write a Java static method, which can use the JDBC functions to interact with the database server.
2. Compile the Java source file and create a jar file (the shared-object file for Java).
3. Execute the install_jar() procedure with the EXECUTE PROCEDURE statement to install the jar file in the current database.
4. If the UDR uses user-defined types, create a map between SQL data types and Java classes.

Use the setUDTExtName() procedure that is explained in EXECUTE PROCEDURE.

5. Register the UDR with the CREATE FUNCTION statement.

Rather than storing the body of an external routine directly in the database, the database server stores only the pathname of the shared-object file that contains the compiled version of the routine. When the database server executes the external routine, the database server invokes the external object code.

The database server stores information about an external function in several system catalog tables, including sysprocbody and sysprocauth. For more information on these system catalog tables, see the Informix Guide to SQL: Reference.

Example of Registering a C User-Defined Function

The following example registers an external C user-defined function named equal() in the database. This function takes two arguments of the type basetype1 and returns a single Boolean value. The external routine reference name specifies the path to the C shared library where the function object code is actually stored. This library contains a C function basetype1_equal(), which is invoked during execution of the equal() function:

CREATE FUNCTION equal ( arg1 basetype1, arg2 basetype1)

RETURNING BOOLEAN;

EXTERNAL NAME 
"/usr/lib/basetype1/lib/libbtype1.so(basetype1_equal)"

LANGUAGE C

END FUNCTION

Example of Registering a User-Defined Function Written in Java

The following CREATE FUNCTION statement registers the user-defined function, sql_explosive_reaction(). This function is discussed in sqlj.install_jar:

CREATE FUNCTION sql_explosive_reaction(int) RETURNS int
	WITH (class="jvp")
	EXTERNAL NAME "course_jar:Chemistry.explosiveReaction" 
	LANGUAGE JAVA

This function returns a single value of type INTEGER. The EXTERNAL NAME clause specifies that the Java implementation of the sql_explosive_reaction() function is a method called explosiveReaction(), which resides in the Chemistry Java class that resides in the course_jar jar file.

The user who creates an owner-privileged UDR owns any database objects that are created by the UDR when the UDR is executed, unless another owner is specified for the created database object. In other words, the UDR owner, not the user who executes the UDR, is the owner of any database objects created by the UDR unless another owner is specified in the statement that creates the database object.

For example, assume that user mike creates the following user-defined function:

CREATE FUNCTION func1 () RETURNING INT;

	CREATE TABLE tab1 (colx INT);

	RETURN 1;
END FUNCTION

If user joan now executes function func1, user mike, not user joan, is the owner of the newly created table tab1.

However, in the case of a DBA-privileged UDR, the user who executes the UDR-not the UDR owner-owns any database objects created by the UDR, unless another owner is specified for the database object within the UDR.

For example, assume that user mike creates the following user-defined function:

CREATE DBA FUNCTION func2 () RETURNING INT;

	CREATE TABLE tab2 (coly INT);

	RETURN 1;
END FUNCTION

If user joan now executes function func2, user joan, not user mike, is the owner of the newly created table tab2.

Related statements: ALTER FUNCTION, ALTER ROUTINE, CREATE PROCEDURE, CREATE FUNCTION FROM, DROP FUNCTION, DROP ROUTINE, GRANT, EXECUTE FUNCTION, PREPARE, REVOKE, and UPDATE STATISTICS

For a discussion on creating and using SPL routines, see the Informix Guide to SQL: Tutorial.

For a discussion of how to create and use external routines, see Extending Informix Dynamic Server 2000.

For information about how to create C UDRs, see the DataBlade API Programmer's Manual.

For more information on the NODEFDAC environment variable and the relative system catalog tables (sysprocedures, sysprocplan, sysprocbody and sysprocauth), see the Informix Guide to SQL: Reference.