CREATE FUNCTION


Attribute	Limit
Number of parameters	300
Size of the UDF body	64 KB
Maximum length of a UDF name	See Table 1, Identifier Restrictions by Identifier Type, in Advanced Operations Guide
Maximum length of a UDF variable name	128 characters

Supported Scalar Input Parameters and Returned Data Types

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

Table 27

Supported Input Parameters and Returned Data Types

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

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

BEGIN

DECLARE :smallest INTEGER

============

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

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