CREATE FUNCTION

Advantage SQL Engine

Creates a new function in the database.

Syntax

CREATE FUNCTION package_name.]function_name( [parameter, Ö] )

RETURNS data-type

[DESCRIPTION function_description]

BEGIN sql-script END

 

package_name ::= identifier

function_name ::= identifier

parameter ::= identifier data-type

function_description ::= string-literal

sql-script ::= declare_statements;statement_block | statement_block

 

Remark

The CREATE FUNCTION statement can be used to create a User Defined Function (UDF) in the database. The function is stored entirely inside the Advantage Data Dictionary file.

The connected user must have the CREATE FUNCTION permission to create a function (see GRANT).

The optional package_name can be used to put the function in the specified package or function group. Packages provide independent name spaces for the functions, allowing functions with identical name to exist in different packages. To create the function in the specified package, the package must exist in the database (see CREATE PACKAGE) and the connected user must have the ALTER permission over the package (see GRANT). Functions in a package do not have individual permissions. A userís permission on the functions in the package is derived from the userís permission on the package.

The parameters can be any data type that is supported in an SQL script (see DECLARE) except the cursor type.

The RETURNS clause is used to specify the type of the returned data.

The optional DESCRIPTION clause can be used to store additional comments about the function in the data dictionary.

In the sql-script, the RETURN statement may be used to return the result to the caller.

A UDF, once defined, may be used in the SQL engine like any other native scalar functions supported by the Advantage SQL engine. The only difference in the usage between a UDF and a native function is that the user must have the execute permission on the UDF to evaluate it.

Example

  1. A simple function that returns a concatenated full name.

CREATE FUNCTION FullName( lastname char(15), firstname char(15))

RETURNS char(31)

DESCRIPTION 'This function constructs comma separated name with blanks trimmed'

BEGIN

RETURN Trim(lastname)+','+Trim(firstname);

END;

 

Sample usage of the function:

SELECT FullName( lastname, firstname ) FROM customers

 

  1. Creating functions in a package.

CREATE PACKAGE Local

DESCRIPTION 'Functions returning locally formatted string.';

 

// pad the string with zero or truncate it so it's length is
// equal to the specified length
CREATE FUNCTION local.ZeroPadI2Str( i Integer, len integer )
RETURNS String
BEGIN
DECLARE curr_len Integer;
DECLARE str String;

Str = Trim( Convert( i, SQL_CHAR ));
curr_len = length( str );

if curr_len < len then
return repeat( '0', len - curr_len ) + str;
elseif curr_len > len then
return left( str, len );
end;

return str;
end;

// This function returns a date in a string format that is
// traditionally used in the United State
CREATE FUNCTION Local.strDate( dDate Date )
RETURNS Char( 10 )
BEGIN
RETURN Local.ZeroPadI2Str( Month( dDate ), 2 ) + '/' +
Local.ZeroPadI2Str( DayOfMonth( dDate ), 2 ) + '/' +
Local.ZeroPadI2Str( Year( dDate ), 4 );
END

Sample usage of the function:

SELECT Local.StrDate( CURDATE() ) FROM system.iota

See Also

ALTER FUNCTION

CREATE PACKAGE

DROP FUNCTION

DROP PACKAGE

Effective Permissions