CREATE FUNCTION

The CREATE FUNCTION statement creates a scalar user-defined function (UDF) in the database. You can invoke the user-defined functions from a query.

Syntax

CREATE FUNCTION function-name ( [ [ IN ] 
{ :parameter_name scalar_parameter_data_type }  [...] ] )  
RETURNS scalar_return_data_type 
[AS] 
BEGIN 
body_of_function 
RETURN scalar_expression 

END; 

Arguments

function_name  

Name of the scalar UDF. UDF names must conform to the rules for identifiers and must be unique within the database and to its owner.

parameter_name  

A parameter in the scalar UDF. A maximum of 300 parameters is allowed. The value must be supplied when the function is invoked.

scalar_parameter_data_type 

The data type for the specified parameter name.

scalar_return_data_type 

The return value of a scalar UDF. Only scalar types are supported.

body_of_function 

The statements that compose the scalar function.

scalar_expression 

Specifies the scalar value that the scalar function returns.

Remarks

You must have the CREATE FUNCTION permission in the database in which you wish to create a scalar UDF.

Each fully qualified UDF name (database-name.function-name) must be unique within a database. The UDF name cannot be the same as that of a built-in function or any other UDF in the same database.

Restrictions

Only scalar input parameters are supported.

No OUTPUT and INOUT parameters are allowed. By default, all parameters are input. You need not specify the "IN" keyword.

Limits

Observe the following limitations when you create user-defined functions.

Supported Scalar Input Parameters and Returned Data Types

The following are the supported data types for input scalar parameters and returned values in Pervasive.SQL.

Examples

The following example creates a function that calculates the area of a rectangular box whose details are stored in the Box table:

CREATE FUNCTION CalculateBoxArea(:boxName char(20))  
RETURNS REAL  
AS  
BEGIN  
DECLARE :len REAL;  
DECLARE :breadth REAL;  
SELECT len, breadth INTO :len, :breadth FROM box 
WHERE name = :boxName;  
RETURN(:len * :breadth);  

END;  

The following example creates a function that compares two integers and returns the smaller of the two:

CREATE FUNCTION GetSmallest(:A integer, :B Integer)  
RETURNS Integer  
AS  
BEGIN  
DECLARE :smallest INTEGER  
IF (:A < :B ) THEN  
SET :smallest = :A;  
ELSE  
SET :smallest = :B;  
END IF;  
RETURN :smallest;  

END;  

The following example creates a function that calculates simple interest using the formula SI = PTR/100, where P is the Principle, T is the period, and R is the rate of interest.

CREATE FUNCTION CalculateInterest(IN :principle float, 
IN :period real, IN :rate double)  
RETURNS DOUBLE  
AS  
BEGIN  
DECLARE :interest DOUBLE;  
SET :interest = ((:principle * :period * :rate) / 
100);  
RETURN (:interest);  

END;  

Invoking a Scalar User-Defined Function

You can invoke a user-defined function wherever scalar expressions are supported, by specifying the function name followed by a comma-separated list of arguments. The list of arguments is enclosed in parentheses.

A UDF can be invoked with or without a database qualifier prefix. When a database qualifier is not prefixed, the UDF is executed from the current database context. If a database qualifier is prefixed, the UDF is executed in the context of the specified database. (In the examples below, some use a database qualifier prefix and some do not.)

Limits

Parameter names cannot be specified in the arguments, when invoking a function.

The argument values for all parameters (also known as actual parameters) must be in the same sequence in which the parameters are defined in the CREATE FUNCTION statement (also known as formal parameters).

Examples

UDF in Procedures

create procedure procTestUdfInvoke() as 
begin 
declare :a integer; 
set :a = 99 + (222 + Demodata.GetSmallest(10, 9)) + 
10; 
print :a; 

end; 
call procTestUdfInvoke() 

The following example is similar to the previous one, except that the database qualifier is omitted.

create procedure procTestUdfInvoke2() as 
begin 
declare :a integer; 
set :a = 99 + (222 + GetSmallest(10, 9)) +10; 
print :a; 

end; 
call procTestUdfInvoke2 

UDF in Select list

select GetSmallest(100,99) 

UDF in Where clause

Select name from class where id <= GetSmallest(10,20) 

UDF within UDF

create function funcTestUdfInvoke() returns integer as 
begin 
declare :a integer; 
set :a = 99  + (222 - Demodata.GetSmallest(10, 9)); 
return :a; 

end; 

UDF in INSERT statement

create table t1(col1 integer, col2 integer, col3 float) 
insert into t1 values (GetSmallest(10,20), 20 , 2.0) 
insert into t1 (select * from t1 where col1 = 
getSmallest(10,20)) 

UDF in UPDATE statement

update t1 set col2 = Demodata.GetSmallest(2,10) where 
col1 = 2 
update t1 set col1 = 3 where col2 = 
Demodata.GetSmallest(10, 5) 

UDF in GROUP BY statement

select col2 from t1 group by getSmallest(10,2), col2 

UDF in ORDER BY statement

select col2 from t1 order by Demodata.getSmallest(10,2), 
col2 

Recursive UDF

create function factorial(in :n integer) returns double 
as begin 
declare :fact double; 
if (:n <= 0) then 
set :fact = 1; 
else 
set :fact = (:n * Demodata.factorial(:n - 1));         
end if; 
return :fact; 

end; 

select Demodata.factorial(20) can be used to get the factorial value of 20.

UDF with default values

create function testUdfDefault(:z integer = 10) returns 
integer as  
begin 
return :z-1;  

end; 

select Demodata.testUdfDefault(). This function takes the default value specified in case the value of the parameter is not given.

IUDF with dynamic parameters

select name from class where id <= GetSmallest(?,?) 

IUDF as an expression

select 10 + Demodata.Getsmallest(10,20) + 15 

UDF used as parameters

select demodata.calculateinterest 
(10+demodata.getsmallest(3000, 2000), 
demodata.factorial(2), demodata.testUdfDefault(3)) 

See Also

DROP FUNCTION