FUNCTIONS System Table Description

Description

Lists information about SQL or Java user defined functions.

Fields

Field Name Type Description

SCHEMA

string

The schema in which the user defined function was created.

FUNCTIONNAME

string

The name of the user defined function.

PARAMETERCOUNT

integer

The number of INPUT parameters. Functions have only INPUT parameters. They do not have OUTPUT parameters. This is used to ensure uniqueness of function within a schema. More than one function can be defined in a schema with the same name providing the number of INPUT parameters is different.

CREATOR

string

The user who created the user defined function.

FUNCTIONTYPE

integer

Indicates the language of the user defined function, defined as:

0	SQL
1	Java

FUNCTIONTEXT

string

The actual text of the user defined function as entered at the time of CREATE FUNCTION.

VERSION

integer

This is reserved for future use.

Indexes

Primary Index: FUNCTIONS..PRIMARY_KEY on fields: SCHEMA , FUNCTIONNAME, PARAMETERCOUNT

Example

The following example shows the contents of both SYSTEM.FUNCTIONS and SYSTEM.FUNCTIONCOLUMNS for a user defined function. We use “SET OUTPUT VERTICAL” because it is more readable if displayed this way (see SET).

CREATE FUNCTION fnc_date(i_date DATE) RETURNS DATE AS RETURN i_date; END_FUNCTION;
SET OUTPUT VERTICAL;
SELECT * FROM system.functions;
==================================== Row #1 ====================================
SCHEMA: USER
FUNCTIONNAME: FNC_DATE
PARAMETERCOUNT: 1
CREATOR: DBA
FUNCTIONTYPE: 0
FUNCTIONTEXT: (i_date DATE) RETURNS DATE AS RETURN i_date; END_FUNCTION;
VERSION: 1

SELECT * FROM system.functioncolumns;
==================================== Row #1 ====================================
SCHEMA: USER
FUNCTIONNAME: FNC_DATE
PARAMETERCOUNT: 1
NAME: I_DATE
TYPE: 1
DATATYPE: 9
DATATYPENAME: date
PRECISION: 10
LENGTH: 8
SCALE: 0
OCTET_LENGTH: 8
ORDINAL: 1
==================================== Row #2 ====================================
SCHEMA: USER
FUNCTIONNAME: FNC_DATE
PARAMETERCOUNT: 1
NAME:
TYPE: 4
DATATYPE: 9
DATATYPENAME: date
PRECISION: 10
LENGTH: 8
SCALE: 0
OCTET_LENGTH: 8
ORDINAL: 0